Exporting a table to a text file

[Peter Bellamy]

Can anyone solve this for me?

I am exporting a table into a text file and using a specification to do it.

"strSpec = "Promonth Import File Export Specification"
strFile = "Promonth Import Table dmy"

DoCmd.TransferText acExportDelim, strSpec, strFile, "H:\Userdata\Promonth Import File.csv", False "

One column includes integers, numbers with alphas and integers with leading zeros. Everything exports ok except the numbers with leading zeros, they get removed!

Unfortunately they are needed in the next process and have to be there.

Any suggestions?

Cheers

 

[DJkarl]

Make sure your export spec has that field set as text and not as a number, number formats will drop leading zeros as they are irrelevant to the value of the number.

 

[Peter Bellamy]

There does not seem to be a place where you can select the field type when exporting to a txt/csv file, it should all be text??

 

[Peter Bellamy]

No suggestions then?

 

[gemma-the-husky]

the point is that a number doesnt have leading zeroes - if you want to get this then your query will have to format the number into a specific style

im not sure of the command - maybe cstrg and/or format to coerce a specific number of characters - then it will be a string, not a number.

are you trying to get a fixed width text file, rather than csv - there is a fixed width option on the advanced/specs tab of the export wizard

 

[zzoemie]

What do you import, a txt file or a csv file?
If you import a csv file then don't open it in excel since excel wil recognize a number en excel will delete the leading zero's.

 

[Peter Bellamy]

zzoemie, you have given me the answer!
The user of the routine was opening the csv it created with Excel to check its contents, and then saving it.
Excel was strippng out the leading zeros!
They are now using Notepad !

Thanks