issue exporting access table into txt file with correct format

HOTWATT
HOTWATT used Ask the Experts™
on
Hey
I have an access table that I need to export to a txt file so I can use it to import into our database. I am exporting it with spaces and a text qualifier with quotes. In the preview screen it looks perfect. The text has quotes around it and the numbers do not but as soon as I export and open it everything has quotes around it including numbers which wont work when I try to import. I need just the text to have quotes. Any idea why it looks fine in the preview but then exports with everything in quotes? I added a couple attachments so you can see what I am talking about.
text_document.PNG
preview.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
I just tried a simple test in Access 2016 here and it worked as you wanted it to.  Below is the output simple sample text file, with two text columns, and two number columns.  What version are you using?

How are the number fields defined in the table, and what formatting etc do they have?

"ID" "t1" "n1" "t2" "n2"
1 "AAA" 111 "BBB" 222
2 "C" 3 "D" 4

Open in new window


»bp

Author

Commented:
I am using access 216
I have one field that is short text and all the rest are number. The number columns are formatted as decimals. I added an attachment of the format for the number columns.
format.PNG
Bill PrewTest your restores, not your backups...
Top Expert 2016
Commented:
Ah, that seems to make a difference.  You might play around with the field formatting and see if you can get a format that is still useful for the data, but doesn't force in the double quotes.


»bp
Distinguished Expert 2017
Commented:
Is there some reason that you are using the decimal data type?  If you don't need more than four decimal digits, Currency would be the best choice.  Otherwise, Double or Single would work.  It looks like the export is treating the decimal data type as text rather than numeric.  That would be a bug but you would still have to deal with it.  I created a text and verified that the Decimal data type is the problem.  You'll need to change it or create your own export using VBA.

"PK","DateData","LngInt","dbl","sngl","Dec"
1,11/7/2017 0:00:00,1234567890,123456789.12,12345.12,"12345678901.123"

Author

Commented:
That was the issue. The decimal format was being treated as text for some reason. I switched that and everything exported correctly. Thanks for all the help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial