Exporting query to text file with pre-defined header

  • Thread starter Thread starter James
  • Start date Start date
J

James

Can anyone assist with exporting a query to a txt flat
file with delimeters, including a pre-defined header in
the txt file. The header is two lines, but has
information necessary for it to work with the target
application - ie I am going to import the text file from
the target application.

The only other option I could think of was to somehow
incorporate the header information in the query - but I
don't see how I can do that. I also need a footer that
caluclates the number of records.

Cheers,

James
 
Hi James,

There are various ways of doing this. Perhaps the simplest is to create
three temporary files, for the header, the body and the footer, and then
use a Windows command to concatenate them to the output file before
deleting them e.g.

Dim strFolder As String
Dim strCmd As String

strFolder = "D:\Folder\Subfolder\"
strCmd = "Copy " & strFolder & "Header.txt + " & _
& strFolder & "Body.txt + " & strFolder & "Footer.txt " _
& strFolder & "ExportFile.txt"
Shell strCmd
Kill strFolder & "Header.txt"
Kill strFolder & "Body.txt"
Kill strFolder & "Footer.txt"


For the footer that calculates the number of records, you can probably
use a totals query, something like this:

SELECT "Total: " & COUNT(ID) & " Records." AS F1 FROM qryXXX;

For the header file, you can either put the necessary information in a
little two-row table and use a query to assemble and export it, or else
create the file under program control, as in this untested air code:

Dim lngF As Long
Dim strLine As String

lngFN = FreeFile()
Open strFolder & "Header.txt" For Output As #lngF

strLine = "This is the first line of the header"
Print #lngF, strLine

strLine = "Created " & Format(Date(), "yyyy.mm.dd")
Print #lngF, strLine

Close #lngF
 
John,

Thanks for the lead. I have resolved part of the issue by
using

DoCmd.TransferText acExportDelim, etc

However, I am having trouble getting your code to add the
header and footer to work. Even when I hard code the file
names in the code, I get a "File Not Found" Error. I have
tried tweaking your code numerous times, but it won't
work. So I have the body, but can't get VBA to copy header
and footer files - or any files?
strCmd = "Copy " & strFolder & "Header.txt + " & _
& strFolder & "Body.txt + " & strFolder & "Footer.txt " _
& strFolder & "ExportFile.txt"
Shell strCmd
Click to expand...

Maybe I need something other than "Copy"?

Cheers,

James
-----Original Message-----
Hi James,

There are various ways of doing this. Perhaps the simplest is to create
three temporary files, for the header, the body and the footer, and then
use a Windows command to concatenate them to the output file before
deleting them e.g.

Dim strFolder As String
Dim strCmd As String

strFolder = "D:\Folder\Subfolder\"
strCmd = "Copy " & strFolder & "Header.txt + " & _
& strFolder & "Body.txt + " & strFolder & "Footer.txt " _
& strFolder & "ExportFile.txt"
Shell strCmd
Kill strFolder & "Header.txt"
Kill strFolder & "Body.txt"
Kill strFolder & "Footer.txt"


For the footer that calculates the number of records, you can probably
use a totals query, something like this:

SELECT "Total: " & COUNT(ID) & " Records." AS F1 FROM qryXXX;

For the header file, you can either put the necessary information in a
little two-row table and use a query to assemble and export it, or else
create the file under program control, as in this untested air code:

Dim lngF As Long
Dim strLine As String

lngFN = FreeFile()
Open strFolder & "Header.txt" For Output As #lngF

strLine = "This is the first line of the header"
Print #lngF, strLine

strLine = "Created " & Format(Date(), "yyyy.mm.dd")
Print #lngF, strLine

Close #lngF






Can anyone assist with exporting a query to a txt flat
file with delimeters, including a pre-defined header in
the txt file. The header is two lines, but has
information necessary for it to work with the target
application - ie I am going to import the text file from
the target application.

The only other option I could think of was to somehow
incorporate the header information in the query - but I
don't see how I can do that. I also need a footer that
caluclates the number of records.

Cheers,

James
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi James,

Two things to try. First, if any of your file or folder names may
contain spaces, make sure the resulting filespecs are surrounded by
quotes:

strCmd = "Copy """ & strFolder & "Header.txt"" + """ & _
& strFolder & "Body.txt"" + """ & strFolder & "Footer.txt"" " _
& strFolder & "ExportFile.txt"

e.g. if the filenames are Header File.txt, Body File.txt etc., strCmd
should be like
Copy "D:\folder\Header File.txt" + "D:\folder\Body File.txt" +
"D:\Folder\Footer File.txt" "D:\Folder\Output File.txt"

Also, put
cmd.exe /c
or (in older versions of Windows)
command.com /c
at the beginning of strCmd, so it begins
cmd.exe /c copy ...

John,

Thanks for the lead. I have resolved part of the issue by
using

DoCmd.TransferText acExportDelim, etc

However, I am having trouble getting your code to add the
header and footer to work. Even when I hard code the file
names in the code, I get a "File Not Found" Error. I have
tried tweaking your code numerous times, but it won't
work. So I have the body, but can't get VBA to copy header
and footer files - or any files?
strCmd = "Copy " & strFolder & "Header.txt + " & _
& strFolder & "Body.txt + " & strFolder & "Footer.txt " _
& strFolder & "ExportFile.txt"
Shell strCmd
Click to expand...

Maybe I need something other than "Copy"?

Cheers,

James
-----Original Message-----
Hi James,

There are various ways of doing this. Perhaps the simplest is to create
three temporary files, for the header, the body and the footer, and then
use a Windows command to concatenate them to the output file before
deleting them e.g.

Dim strFolder As String
Dim strCmd As String

strFolder = "D:\Folder\Subfolder\"
strCmd = "Copy " & strFolder & "Header.txt + " & _
& strFolder & "Body.txt + " & strFolder & "Footer.txt " _
& strFolder & "ExportFile.txt"
Shell strCmd
Kill strFolder & "Header.txt"
Kill strFolder & "Body.txt"
Kill strFolder & "Footer.txt"


For the footer that calculates the number of records, you can probably
use a totals query, something like this:

SELECT "Total: " & COUNT(ID) & " Records." AS F1 FROM qryXXX;

For the header file, you can either put the necessary information in a
little two-row table and use a query to assemble and export it, or else
create the file under program control, as in this untested air code:

Dim lngF As Long
Dim strLine As String

lngFN = FreeFile()
Open strFolder & "Header.txt" For Output As #lngF

strLine = "This is the first line of the header"
Print #lngF, strLine

strLine = "Created " & Format(Date(), "yyyy.mm.dd")
Print #lngF, strLine

Close #lngF






Can anyone assist with exporting a query to a txt flat
file with delimeters, including a pre-defined header in
the txt file. The header is two lines, but has
information necessary for it to work with the target
application - ie I am going to import the text file from
the target application.

The only other option I could think of was to somehow
incorporate the header information in the query - but I
don't see how I can do that. I also need a footer that
caluclates the number of records.

Cheers,

James
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Back
Top