Export a table to a delimited text file

[FuzMic]

Hi guys:

Back again asking for help

We know the following will export to a delimited text.
DoCmd.TransferText acExportDelim, , "A-Table", "textDelimit"

However needs help to changed separator to say |, the standard is comma, how do do it?

Another question:
Once the text file is created, how to add another text line at the top of the file or append at the bottom.

 

[FuzMic]

PS Solved the append after searching the web; essentially using FileSystemObject > ReadAll() & Write with vCrLf.

Questions that remains, how to control the output to the text file especially to items
1. Date export only as short date
2. All strings without extra ""
3. Use different delimiter
Please help, i have no experience with schema issue. Thanks

 

[Ranman256]

you skipped the specName parameter....
DoCmd.TransferText acExportDelim,specName, "A-Table", "textDelimit"

the specification name is where you setup your delimiters, and the fixed len or comma delim.
Make one by:
external data tab, export text
select query and target filename
click advanced button at bottom
set fixed width settings, and delimiter character
save these settings as a Spec Name.

then set up your export import command using this spec name.
click Import Text file

 

[FuzMic]

Thanks Raman

From the Text export dialog, all these you mentioned can be set up and it can be saved as SpecName. I do this in Access.02 Text Export Wizard (same in 03 & 07). But in VBA using DoCmd.TransferText, this SpecName can't be used. Moreover the SpecName does not appear as a file that i can found with the PC.

I just found that the system tables is keeping the SpecName that i saved with all the settings.
Thus using DoCmd.TransferText acExportDelim, "SpecName", "sourceTable", "TargetText" nothing is exported because it just did not recognize the saved "SpecName"

Any other way to tackle this. All the write up in the web about missing SpecName is going round & round, too many missed directions.