We help IT Professionals succeed at work.

Exporting access table to text file -

Jim Smulders
Jim Smulders used Ask the Experts™
on
MS Access 2010 has changed.
We had an access file that used to be able to export a table to a text file and retain the fixed width of the file, with no formatting MS Acccess 2010 puports to help you, such that if you use the exportwith formatting command, you get a text file, with special headers and field separators, defeating the purpose of a simple export.

What is the best way to export to text from Access 2010 using a Macro...or VB.

In this case, I have a table called header.
I want to create a text file called header.txt with no formatting, but just use the definitiions within the table to provide the output (ie column x is 2 digits wide, etc)....

Thanks all.

JCS
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
to customize your export, and use vba codes you need to create an export specification

1.right click on the table
2.select export > Text file
   click on Browse and locate the destination folder
3. (you can accept the proposed name or change it)
click Save, then click OK
4. In the export text wizard select the type (Delim Fixed) width
5. Follow the wizard, before clicking on Finish
     5a .Click Advanced
6. In the Export Specification dialog box Field Information List, correct any descrepancies

7. click save as, give the specification a name <-- this is the specification name that you will use in the command line below


DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myHeader.txt", True
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
To help more, you can post a basic sample of the DB and include a sample of the *exact* output you are looking for...

Author

Commented:
Capricorn, your assistance has moved me ahead....one additional problem.  The table exports with the first line being the record header...ie the names of the fields.  This is not good, and I dont see how to turn this off in the export selection.

Attached is the example file I was able to create, and you will see the header comes with it.

All I want is the table's contents.  The header is pollution.
header.txt

Author

Commented:
Ah, Ha! Figured it out.....should be:

DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myHeader.txt", False

Instead of:

DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myHeader.txt", True

Thanks Capricorn
Top Expert 2016

Commented:
coorstek,

since you already got the solution, please close this thread.