Solved

Export Query To Tab Delimited File

Posted on 2008-11-01
24
1,272 Views
Last Modified: 2012-08-14
I am trying to export query to tab delimited file.  I have the export specification defined, but when I run this in VBA it gives me error 3625 file specification does not exist.  I have checked the name of the spec and it is correct.  This is the code:  DoCmd.TransferText acExportDelim, "EXPORT-file1", "EXPORT_TARGET_TO_TEXT_brokendown", exportto  ' EXPORT_TARGET_TO_TEXT_brokendown is qry name

When I run the specification through Access and not vba, it does the export... BUT it takes my memo field with formatting codes in it and changes the format.    Here is a cut and paste of the first two records.  It should be just 2 lines and not breaking out the code to another line.
I should mention that if I export to excel, open in excel and then save as a tab delimited file through excel - its perfect.
How do I properly export to tab delimeted in vba code?
TIA
Diana
"v_products_model"      "v_products_image"      "v_products_image_med"      "v_products_image_lrg"      "v_products_image_sm_1"      "v_products_image_xl_1"      "v_products_image_sm_2"      "v_products_image_xl_2"      "v_products_image_sm_3"      "v_products_image_xl_3"      "v_products_image_sm_4"      "v_products_image_xl_4"      "v_products_image_sm_5"      "v_products_image_xl_5"      "v_products_image_sm_6"      "v_products_image_xl_6"      "v_products_name_1"      "v_products_description_1"      "v_products_url_1"      "v_products_head_title_tag_1"      "v_products_head_desc_tag_1"      "v_products_head_keywords_tag_1"      "v_products_price"      "v_products_weight"      "v_date_avail"      "v_date_added"      "v_products_quantity"      "v_manufacturers_name"      "v_categories_name_1"      "v_categories_name_2"      "v_categories_name_3"      "v_categories_name_4"      "v_tax_class_title"      "v_status"      "EOREOR"
"001-1254"      "Images\small\001-1254.jpg"      "001-1254.jpg"      "Images\Large\001-1254.jpg"                                                                              "Banshee Talkbox"      "<b><p>
<font color=""#0060B0"">Quick Info and Policies:</font>
</b> <font color=""#0060B0""> <br>
- Same day shipping on most orders. <br>
- 30 day defective item return policy. <br>
- Low flat rate shipping charges. <br>
- 45 day refund return policy. <br>
- 7% sales tax applies to NJ residents only.<br>
- Security encryption to protect customer data. <br>
- All merchandise is brand new and factory sealed. <br>
- Guaranteed warranty with all products. <br>
- No gimmicks, no headaches, no BS! <br>
-"      "www.rocktron.com/frameset1.html"      "Banshee Talkbox"      "ROCKTRON BANSHEE TALK BOX"      "<li>5-watt driver for easy set up<li>Amplified talk box<li>Run right along with other effects<li>Microphone required"      "141.2387"      "6.2"      "10/31/2008 13:33"      "10/31/2008 13:33"      "8"      "Rocktron"      "DJ Equipment"      "Microphones"      ""      ""      "Taxable Goods"      "Active"      "EOREOR"
"001-1302"      "Images\small\001-1302.jpg"      "001-1302.jpg"      "Images\Large\001-1302.jpg"                                                                              "Hush Super C Rack With Noise Reduction"      "<b><p>
<font color=""#0060B0"">Quick Info and Policies:</font>
</b> <font color=""#0060B0""> <br>
0
Comment
Question by:dleads
  • 15
  • 9
24 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 22858582
I'm not making any sense of your command line.  Here's what Help says:

TransferText Method
See AlsoApplies ToExampleSpecificsThe TransferText method carries out the TransferText action in Visual Basic.

expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
expression    Required. An expression that returns one of the objects in the Applies To list.

TransferType   Optional AcTextTransferType.

AcTextTransferType can be one of these AcTextTransferType constants.
acExportDelim
acExportFixed
acExportHTML
acExportMerge
acImportDelim default
acImportFixed
acImportHTML
acLinkDelim
acLinkFixed
acLinkHTML
If you leave this argument blank, the default constant (acImportDelim) is assumed.

Notes

Only acImportDelim, acImportFixed, acExportDelim, acExportFixed, or acExportMerge transfer types are supported in a Microsoft Access project (.adp).
 

SpecificationName   Optional Variant. A string expression that's the name of an import or export specification you've created and saved in the current database. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.

TableName   Optional Variant. A string expression that's the name of the Microsoft Access table you want to import text data to, export text data from, or link text data to, or the Microsoft Access query whose results you want to export to a text file.

FileName   Optional Variant. A string expression that's the full name, including the path, of the text file you want to import from, export to, or link to.

HasFieldNames   Optional Variant. Use True (-1) to use the first row of the text file as field names when importing, exporting, or linking. Use False (0) to treat the first row of the text file as normal data. If you leave this argument blank, the default (False) is assumed. This argument is ignored for Microsoft Word mail merge data files, which must always contain the field names in the first row.

HTMLTableName   Optional Variant. A string expression that's the name of the table or list in the HTML file that you want to import or link. This argument is ignored unless the transfertype    argument is set to acImportHTML or acLinkHTML. If you leave this argument blank, the first table or list in the HTML file is imported or linked. The name of the table or list in the HTML file is determined by the text specified by the <CAPTION> tag, if there's a <CAPTION> tag. If there's no <CAPTION> tag, the name is determined by the text specified by the <TITLE> tag. If more than one table or list has the same name, Microsoft Access distinguishes them by adding a number to the end of each table or list name; for example, Employees1 and Employees2.

CodePage   Optional Variant. A Long value indicating the character set of the code page.

Remarks


Type Alt+F11 and enter TransferText in the Help window.  This is from Method.  The Action return is more detailed.
0
 

Author Comment

by:dleads
ID: 22858595
Sorry - this is the command line:
DoCmd.TransferText acExportDelim, specname, qryname, filetoexportto

1.  It does not find the specname, although it is clearly there
2.  If I leave out the specname, it exports but not properly - it does NOT use the proper tab delimited
3.  If I export to Excel, then save as tab text, it works fine.

Help!  I'm going nuts trying to get this to work.
Thanks,
Diana
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22858732
Firstly, I Exported my small table by creating an Export Spec named tabxpspec, by clicking File, Export To..., selecting txt as Type, and providing a file name, in my case, TabDoctors.txt.  I was taken to the wizard, where I clicked field delimited, clicked Advanced, selected tab delimited, made sure all the other choices were correct clicked Save As and entered tabxpspec as the specification name, Then I went back to the wizard, completed the inputs and clicked Finish.  The table exported correctly.  Note you should fully qualify the export file name with the drive and path.

Then I went to the Immediate Pane of the VBA Editor (Ctl+G) and typed the command line:

docmd.TransferText acExportDelim,"tabxpspec","Doctors","f:\data\acdata\doctorsTab1.txt"

and Enter.  The table exported correctly.  Did you follow this process?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22858756
Note the export specification is save in one of the hidden tables MSysIMEXSpecs.  If you use the Schema.ini method, can you post the contents of your Schema.ini file.  Remember it has to be in the same directory as the mdb from which you are doing the export.
0
 

Author Comment

by:dleads
ID: 22858775
Did you have a field that had the following?  I wonder if this is throwing it off.

<b><p>
<font color=""#0060B0"">Quick Info and Policies:</font>
</b> <font color=""#0060B0""> <br>
- Same day shipping on most orders. <br>
- 30 day defective item return policy. <br>
- Low flat rate shipping charges. <br>
- 45 day refund return policy. <br>
- 7% sales tax applies to NJ residents only.<br>
- Security encryption to protect customer data. <br>
- All merchandise is brand new and factory sealed. <br>
- Guaranteed warranty with all products. <br>
- No gimmicks, no headaches, no BS! <br>
-"  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22858813
Try changing the delimiter for text field from double quotes ( the default) to some other character that is not contained in any of the text fields such as the single quote (')  or tilde (~) perhaps??
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22858820
BTW where does the second record start?
0
 

Author Comment

by:dleads
ID: 22858828
the last field is "EOREOR"
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22858867
It appears that the first record is a list of field names.  If so you have to modify the command line as per HasFieldNames from Help.
0
 

Author Comment

by:dleads
ID: 22862598
No matter how I change the export options, the field with the descriptions is throwing it off.
The only way I am able to get a good txt file is to export it to Excel, then save it as a tab delimited file.
Any ideas why?
Suggestions?
Thanks,
Diana
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22862744
Been around this one for a while and I now think when you create the export specification using the export wizard, Advanced - you should not wrap the fields in any character.  Select {none} and re-save the spec.  Now try the command line.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22862847
Can you confirm how you created your export specification - via the wizard - or did you create a schema.ini file?  Perhaps you could pare the mdb down the tables required for the query (just a few records) and the query.  Do a compact and repair and then upload it here using Attach File below.  
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:dleads
ID: 22862944
I created it using the wizard.  I have tried every scenario in the wizard.  The problem is the field with the special characters creates a line feed on conversion.  

Try pasting this record into one field on your test.  this is where it bombs out

<b><p>
<font color="#0060B0">Quick Info and Policies:</font>
</b> <font color="#0060B0"> <br>
- Same day shipping on most orders. <br>
- 30 day defective item return policy. <br>
- Low flat rate shipping charges. <br>
- 45 day refund return policy. <br>
- 7% sales tax applies to NJ residents only.<br>
- Security encryption to protect customer data. <br>
- All merchandise is brand new and factory sealed. <br>
- Guaranteed warranty with all products. <br>
- No gimmicks, no headaches, no BS! <br>
- Canadian customers welcome. <br>
<p>Please <a href="http://bsless.com/info.html" target="_blank">click here</a> to view all policies.
<p><b>
Payment Options:</b> Visa, MasterCard, Discover, Amex, PayPal, Money Order, Money Wire, and Certified Check.</font><p>Ensure to protect your purchase beyond the manufacturers warranty: <a href="http://bsless.com/warranty.html" target="_blank"> Click Here </a> to view our low priced extended warranty contracts.<p>  We are a <b>Top Service Yahoo Shopping Store</b>  and also have a <b>Spotless and Satisfactory Record</b> with the <b><a target="_blank" href="http://www.trenton.bbb.org/nis/newsearch2.asp?ID=1&ComID=022100002
6000611">BBB</a></b>. We are committed to maintaining this reputation and will do everything possible to make your purchase from us easy and enjoyable. Our prices are some of the lowest around and we have thousands of products listed. We also take pride in offering outstanding service and very reasonable policies.<p><a href="http://bsless.com/warranty.html" target="_blank"> Click Here for Important Warranty and Extended Warranty Information</a>

<p><a href="http://bsless.com/accessories.html" target="_blank"> Click Here to Save on Related Accessories</a>




0
 

Author Comment

by:dleads
ID: 22862966
Forgot to mention - it breaks to the next line right away, right after the <b><p>
it must be the <p> that causes the new line
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22863244
OK, I copied that block of text to an empty table into the last field, a memo field.  I then clicked File, Export..., selected txt, tab, etc. as Type, gave it a file name, and clicked Export.  The wizard opened, I made sure Delimited was selected, Next, selected Tab, Text Qualifier {none},Next, there was my path and File Name, Finish, and the table exported without any problem and without selecting an export specification.  

I am starting to think you export specification is 'busted'.  Why not delete it and start over with a fresh one.  Don't forget to make the selection that the first row contains the field names.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22863345
As an alternative, you could click Tools, Options, and check System files, then look at the table MSysIMEXSpecs, find your spec name and make sure that record has all the right stuff.  Similarly MsysIMEXColumns should have all the fields listed correctly.  It might also be prudent to shut down and reboot.  I think it would be easiest, once you are in the wizard, to delete the specification and start over.
0
 

Author Comment

by:dleads
ID: 22865969
I've checked the msysimexspecs file and it appears as it should.  I deleted and tried again. I rebooted and tried again.

The problem is the <b><p><font> etc.
Because of these characters, the text file puts a paragraph mark into the txt file.
How do I avoid this?
Thanks,
Diana
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 22868643
Just so we're on the same page, in your post at http:#a22862944 , I copied the text from <b><p> to </a> inclusive, including the blank line and pasted it into the memo field of my table and exported the table.  I've copied the results of the export in the code snippet.  3 is an autonumber ID field, there are 5 empty fields, and then the memo field.  Your example copied perfectly - I'm running A 2003 with Service Pack 3.  From my point of view, there is no problem.  
3						<b><p>

<font color="#0060B0">Quick Info and Policies:</font>

</b> <font color="#0060B0"> <br>

- Same day shipping on most orders. <br>

- 30 day defective item return policy. <br>

- Low flat rate shipping charges. <br>

- 45 day refund return policy. <br>

- 7% sales tax applies to NJ residents only.<br>

- Security encryption to protect customer data. <br>

- All merchandise is brand new and factory sealed. <br>

- Guaranteed warranty with all products. <br>

- No gimmicks, no headaches, no BS! <br>

- Canadian customers welcome. <br>

<p>Please <a href="http://bsless.com/info.html" target="_blank">click here</a> to view all policies.

<p><b>

Payment Options:</b> Visa, MasterCard, Discover, Amex, PayPal, Money Order, Money Wire, and Certified Check.</font><p>Ensure to protect your purchase beyond the manufacturers warranty: <a href="http://bsless.com/warranty.html" target="_blank"> Click Here </a> to view our low priced extended warranty contracts.<p>  We are a <b>Top Service Yahoo Shopping Store</b>  and also have a <b>Spotless and Satisfactory Record</b> with the <b><a target="_blank" href="http://www.trenton.bbb.org/nis/newsearch2.asp?ID=1&ComID=022100002

6000611">BBB</a></b>. We are committed to maintaining this reputation and will do everything possible to make your purchase from us easy and enjoyable. Our prices are some of the lowest around and we have thousands of products listed. We also take pride in offering outstanding service and very reasonable policies.<p><a href="http://bsless.com/warranty.html" target="_blank"> Click Here for Important Warranty and Extended Warranty Information</a>
 

<p><a href="http://bsless.com/accessories.html" target="_blank"> Click Here to Save on Related Accessories</a>

Open in new window

0
 

Author Comment

by:dleads
ID: 22868973
When you open this txt export in excel or in notepad, do you see the 'problem field' wrapped when it should not be?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22870753
What do you mean 'wrapped'?  I have shown you exactly what I see in NotePad
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22870767
Perhaps it's time to upload the mdb as I suggested earlier.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22870768
Perhaps it's time to upload the mdb as I suggested earlier.
0
 

Author Closing Comment

by:dleads
ID: 31512333
It would not work for me (I ran out of time and will)  but GrayL was so helpful that he deserves the points.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22871344
Well thanks, I'm sorry we did not get to the end of it.  I am still not certain what the problem is.  You showed me the memo field and I showed you the result of my export - nothing had changed as far as I could tell.  I still have no idea what happened to your export attempt as you never posted the result.  Oh well....
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now