[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Export Access Table to Tab Delimited TXT file or fixed

Posted on 2009-04-29
13
Medium Priority
?
735 Views
Last Modified: 2013-11-27
I have done exports to Excel, Dbase etc, but I am having problems with Text files.

I need to export a table to a TAB delimited or fixed-width TXT file.

I have no problem creating the regular delimited (") export, which is the default for Export delimited.

But how do I either do the Export Tab Delimited  or Fixed-Width ? I cannot find anything on what the specificationname should be. I need an example
0
Comment
Question by:proffittware
  • 6
  • 4
  • 3
13 Comments
 
LVL 11

Expert Comment

by:SeanStrickland
ID: 24263235
This is Tab Delimited:

DoCmd.TransferText acExportDelim, "", "tableName", "C:\My Documents\TableName.txt", True, ""



A good method to find this out is to create a macro using the TransferText command.  Get that to work and save the macro.  Then right click the macro and use Save As / Export and select Save As Visual Basic Module and it will transfer the macro you created to VBA.

HTH,
Sean
0
 
LVL 11

Expert Comment

by:SeanStrickland
ID: 24263241
BTW,

Replace tableName with your Table's name and replace the file location with your own.
0
 

Author Comment

by:proffittware
ID: 24263279
Thanks I will try these and get back to you.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1800 total points
ID: 24263286
you have to create the ExportSpecification first

to create the export specification  (export spec fixed width)
1.select the table
2.File>export
3. in the Save As Type list, click Text Files (*.txt, *.csv, *.tab, *.asc), and then click Export
4. In the export text wizard select Fixed width
5 .Click Advanced
6. In the Customers 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 acExportFixed, "ExportSpecName", "TableOrqueryName", "C:\mytext.txt", True




0
 

Author Comment

by:proffittware
ID: 24263345
That still gave me quotes (") around each field I am exporting.
0
 

Author Comment

by:proffittware
ID: 24263371
Capricorn1...once I create this specification name file...if I do this export numerous times can I use it repeatedly ?
0
 
LVL 11

Expert Comment

by:SeanStrickland
ID: 24263383
Sorry, I missed that part.  Capricorn has described clearly how to create specificiations.  That's what you need.

My apologies,
Sean
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24263390
proffittware,

in number 4 of my post., if you selected Delimited

you have to use

DoCmd.TransferText acExportDelim, "ExportSpecName", "TableOrqueryName", "C:\mytext.txt", True

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24263397
<once I create this specification name file...if I do this export numerous times can I use it repeatedly ?>  YES
0
 

Author Comment

by:proffittware
ID: 24263604
Capricorn1: Ok...I did what you suggested for Fixed and it worked.  I tried to apply your instructions to the TAB delimited...it gave me tabs between fields, but it also used (") to delimit the fields.

Here is the DoCmd.

  DoCmd.TransferText acExportDelim, "FTPExportTab Export Specification", "FTPExport", "C:\Canada\exporttab.txt", True, ""
0
 
LVL 11

Assisted Solution

by:SeanStrickland
SeanStrickland earned 200 total points
ID: 24263641
"but it also used (") to delimit the fields."

Set the Text Qualifier in the wizard to {none}
0
 

Author Comment

by:proffittware
ID: 24263647
Sorry about the last comment, did not see your comment of 2:39 ...I will try and get back
0
 

Author Comment

by:proffittware
ID: 24263723
Thanks to both for the information.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question