issue exporting access table into txt file with correct format

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
HOTWATTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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
0
HOTWATTAuthor 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
0
Bill PrewIT / Software Engineering ConsultantCommented:
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
0
PatHartmanCommented:
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"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HOTWATTAuthor 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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.