Export to text with special header line

  • Thread starter Thread starter bruce
  • Start date Start date
B

bruce

I have several queries which I want to export to a single text
file in a particular order, plus output a special header line
such as "---- file #1 ----".

Also, each query generates 4 fields. I only want to export
one particular field (which is named the same in each query).

Thanks...
 
Hi Bruce,

One way to do this is with a Union query based on your existing queries.

Assuming the queries are called qry1, qry2, qry3 and you want them in
that order, and the field you want is called F1, you'd basically have
something like this:

SELECT F1, 1 AS QOrder FROM qry1
UNION
SELECT F1, 2 AS QOrder FROM qry2
UNION
SELECT F1, 1 AS QOrder FROM qry3
ORDER BY QOrder;

One can often use a "fake" query to generate the header line, e.g.
something like this: it doesn't matter what table you use provided the
WHERE criterion makes the query return just one record.

SELECT "---- file #1 ----" AS F1, 0 AS QOrder
FROM SomeTable WHERE ID=1

Just include this in the Union query above. Then export the result to a
text file, using the Advanced... button in the text export wizard to set
up a specification that exports only the field F1, skipping QOrder.

Other ways of doing this sort of thing include;
- use the VBA file I/O commands to create a text file, and write the
header line. Then, for each query in turn, open a recordset and iterate
through the recordset writing the field to the text file.
- use the VBA file I/O commands to create a text file containing the
header line. Then use DoCmd.TransferText to export each query to a
separate file. Finally, use the Shell() function to execute a Windows
command to concatenate all the files in the desired order, e.g.
COPY Header.txt + Qry1.txt + Qry2.txt [+...] Finished.txt
 
Hi John...

Good suggestion with the Union...
argh... Tried to do the Union but got an error...
System Resource Exceeded

I'm not a frequent user of Access so excuse my ignorance...
So if I wanted to use VBA, (checking "help"), I see the
definition for "File" Object, and then an "OpenAsTextStream"
method. Would this be the methodology?

Thanks...


John said:
Hi Bruce,

One way to do this is with a Union query based on your existing queries.

Assuming the queries are called qry1, qry2, qry3 and you want them in
that order, and the field you want is called F1, you'd basically have
something like this:

SELECT F1, 1 AS QOrder FROM qry1
UNION
SELECT F1, 2 AS QOrder FROM qry2
UNION
SELECT F1, 1 AS QOrder FROM qry3
ORDER BY QOrder;

One can often use a "fake" query to generate the header line, e.g.
something like this: it doesn't matter what table you use provided the
WHERE criterion makes the query return just one record.

SELECT "---- file #1 ----" AS F1, 0 AS QOrder
FROM SomeTable WHERE ID=1

Just include this in the Union query above. Then export the result to a
text file, using the Advanced... button in the text export wizard to set
up a specification that exports only the field F1, skipping QOrder.

Other ways of doing this sort of thing include;
- use the VBA file I/O commands to create a text file, and write the
header line. Then, for each query in turn, open a recordset and iterate
through the recordset writing the field to the text file.
- use the VBA file I/O commands to create a text file containing the
header line. Then use DoCmd.TransferText to export each query to a
separate file. Finally, use the Shell() function to execute a Windows
command to concatenate all the files in the desired order, e.g.
COPY Header.txt + Qry1.txt + Qry2.txt [+...] Finished.txt

I have several queries which I want to export to a single text
file in a particular order, plus output a special header line
such as "---- file #1 ----".

Also, each query generates 4 fields. I only want to export
one particular field (which is named the same in each query).

Thanks...
Click to expand...
Click to expand...
 
Here is a sample of some general export code:

Public Sub ExportDelim(strTable As String, strExportFile As String,
strDelimiter As String, Optional blnHeader As Boolean)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiter is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or ??

Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & fld.Name & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
rs.Close
Set rs = Nothing
End Sub

Modify the above code to meet your needs.
You may want to open 4 recordsets and grab 1 field from each.
--
Joe Fallon
Access MVP



bruce said:
Hi John...

Good suggestion with the Union...
argh... Tried to do the Union but got an error...
System Resource Exceeded

I'm not a frequent user of Access so excuse my ignorance...
So if I wanted to use VBA, (checking "help"), I see the
definition for "File" Object, and then an "OpenAsTextStream"
method. Would this be the methodology?

Thanks...


John said:
Hi Bruce,

One way to do this is with a Union query based on your existing queries.

Assuming the queries are called qry1, qry2, qry3 and you want them in
that order, and the field you want is called F1, you'd basically have
something like this:

SELECT F1, 1 AS QOrder FROM qry1
UNION
SELECT F1, 2 AS QOrder FROM qry2
UNION
SELECT F1, 1 AS QOrder FROM qry3
ORDER BY QOrder;

One can often use a "fake" query to generate the header line, e.g.
something like this: it doesn't matter what table you use provided the
WHERE criterion makes the query return just one record.

SELECT "---- file #1 ----" AS F1, 0 AS QOrder
FROM SomeTable WHERE ID=1

Just include this in the Union query above. Then export the result to a
text file, using the Advanced... button in the text export wizard to set
up a specification that exports only the field F1, skipping QOrder.

Other ways of doing this sort of thing include;
- use the VBA file I/O commands to create a text file, and write the
header line. Then, for each query in turn, open a recordset and iterate
through the recordset writing the field to the text file.
- use the VBA file I/O commands to create a text file containing the
header line. Then use DoCmd.TransferText to export each query to a
separate file. Finally, use the Shell() function to execute a Windows
command to concatenate all the files in the desired order, e.g.
COPY Header.txt + Qry1.txt + Qry2.txt [+...] Finished.txt

I have several queries which I want to export to a single text
file in a particular order, plus output a special header line
such as "---- file #1 ----".

Also, each query generates 4 fields. I only want to export
one particular field (which is named the same in each query).

Thanks...
Click to expand...
Click to expand...
Click to expand...
 
Joe ---

Worked like a charm! Many Thanks!!!

bruce

Joe said:
Here is a sample of some general export code:

Public Sub ExportDelim(strTable As String, strExportFile As String,
strDelimiter As String, Optional blnHeader As Boolean)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiter is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or ??

Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & fld.Name & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
rs.Close
Set rs = Nothing
End Sub

Modify the above code to meet your needs.
You may want to open 4 recordsets and grab 1 field from each.
--
Joe Fallon
Access MVP

bruce said:
Hi John...

Good suggestion with the Union...
argh... Tried to do the Union but got an error...
System Resource Exceeded

I'm not a frequent user of Access so excuse my ignorance...
So if I wanted to use VBA, (checking "help"), I see the
definition for "File" Object, and then an "OpenAsTextStream"
method. Would this be the methodology?

Thanks...


John said:
Hi Bruce,

One way to do this is with a Union query based on your existing queries.

Assuming the queries are called qry1, qry2, qry3 and you want them in
that order, and the field you want is called F1, you'd basically have
something like this:

SELECT F1, 1 AS QOrder FROM qry1
UNION
SELECT F1, 2 AS QOrder FROM qry2
UNION
SELECT F1, 1 AS QOrder FROM qry3
ORDER BY QOrder;

One can often use a "fake" query to generate the header line, e.g.
something like this: it doesn't matter what table you use provided the
WHERE criterion makes the query return just one record.

SELECT "---- file #1 ----" AS F1, 0 AS QOrder
FROM SomeTable WHERE ID=1

Just include this in the Union query above. Then export the result to a
text file, using the Advanced... button in the text export wizard to set
up a specification that exports only the field F1, skipping QOrder.

Other ways of doing this sort of thing include;
- use the VBA file I/O commands to create a text file, and write the
header line. Then, for each query in turn, open a recordset and iterate
through the recordset writing the field to the text file.
- use the VBA file I/O commands to create a text file containing the
header line. Then use DoCmd.TransferText to export each query to a
separate file. Finally, use the Shell() function to execute a Windows
command to concatenate all the files in the desired order, e.g.
COPY Header.txt + Qry1.txt + Qry2.txt [+...] Finished.txt


I have several queries which I want to export to a single text
file in a particular order, plus output a special header line
such as "---- file #1 ----".

Also, each query generates 4 fields. I only want to export
one particular field (which is named the same in each query).

Thanks...
Click to expand...
Click to expand...
Click to expand...
 
Holy cow! What a pleasant surprise!

Any chance you can post your actual routine?
Or at least a "sanitized" version of it.
Just curious.
--
Joe Fallon
Access MVP



bruce said:
Joe ---

Worked like a charm! Many Thanks!!!

bruce

Joe said:
Here is a sample of some general export code:

Public Sub ExportDelim(strTable As String, strExportFile As String,
strDelimiter As String, Optional blnHeader As Boolean)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiter is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or ??

Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & fld.Name & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
rs.Close
Set rs = Nothing
End Sub

Modify the above code to meet your needs.
You may want to open 4 recordsets and grab 1 field from each.
--
Joe Fallon
Access MVP

bruce said:
Hi John...

Good suggestion with the Union...
argh... Tried to do the Union but got an error...
System Resource Exceeded

I'm not a frequent user of Access so excuse my ignorance...
So if I wanted to use VBA, (checking "help"), I see the
definition for "File" Object, and then an "OpenAsTextStream"
method. Would this be the methodology?

Thanks...


John Nurick wrote:

Hi Bruce,

One way to do this is with a Union query based on your existing
queries.

Assuming the queries are called qry1, qry2, qry3 and you want them in
that order, and the field you want is called F1, you'd basically have
something like this:

SELECT F1, 1 AS QOrder FROM qry1
UNION
SELECT F1, 2 AS QOrder FROM qry2
UNION
SELECT F1, 1 AS QOrder FROM qry3
ORDER BY QOrder;

One can often use a "fake" query to generate the header line, e.g.
something like this: it doesn't matter what table you use provided the
WHERE criterion makes the query return just one record.

SELECT "---- file #1 ----" AS F1, 0 AS QOrder
FROM SomeTable WHERE ID=1

Just include this in the Union query above. Then export the result to
a
text file, using the Advanced... button in the text export wizard to
set
up a specification that exports only the field F1, skipping QOrder.

Other ways of doing this sort of thing include;
- use the VBA file I/O commands to create a text file, and write the
header line. Then, for each query in turn, open a recordset and
iterate
through the recordset writing the field to the text file.
- use the VBA file I/O commands to create a text file containing the
header line. Then use DoCmd.TransferText to export each query to a
separate file. Finally, use the Shell() function to execute a Windows
command to concatenate all the files in the desired order, e.g.
COPY Header.txt + Qry1.txt + Qry2.txt [+...] Finished.txt


I have several queries which I want to export to a single text
file in a particular order, plus output a special header line
such as "---- file #1 ----".

Also, each query generates 4 fields. I only want to export
one particular field (which is named the same in each query).

Thanks...
Click to expand...
Click to expand...
Click to expand...
 
Could this work too?
Before you export to a text file:

1)Enter text header in field #1 (Even with just 1 character)
2)Put all your detail data in fields 2,3,4...
3)Sort your table by Field #1 in Desc Order.
 
Back
Top