exported query to csv file loses leading zeros on strings because text is converted to number

[bashtonmcse]

I have created a custom database app that loads credit card data, reformats it some, adds a valid vendor number, and then exports it to a comma delimited file for upload into an accounting system. my last problem to fix is the fact that when the data is exported to a .csv file any text data that is actually numeric gets converted to a number automatically. I have tried several techniques for exporting the data including a macro and vba using the transfertext method, but all do the same thing. My current workaround is to export the data to a .xlsx spreadsheet, then manually open the spreadsheet and resave to a .csv. That keeps the text in proper format. I even tried the Function ConvertXls2CSV that I found online, but it did the same thing. I am wondering if newer file type of .xlsx to csv works properly but the convertxls2csv is using the older format and that is why that one does not work. Any alternative suggestions would be helpful.



Thanks.

[JoeM]

When you say that you are losing the leading zeroes, what program are you viewing the CSV file with?
The reason why I ask is because Microsoft has decided that the default program to view CSV files is Excel, and that is problematic, because even if you CSV has the leading zeroes, Excel will drop them upon opening the file. So don't trust what you see in Excel, if that is what you are using to view your CSV files.

If you REALLY want to see what is contained in the CSV file, view it in a Text Editor like NotePad, WordPad, or some third-party editor (UltraEdit, NoteTab, etc).

BTW, this field you are exporting out of Access, what is the data type?
Or, if you are exporting a calculated field, what is the calculation?
If there is truly a problem on that end, we may be able to help you fix it up.
Access WILL export to CSV with leading zeroes. I do it all the time with Social Security Numbers.

[bashtonmcse]

Sorry it took so long, I have been working on other parts of the system. I looked at the file with both excel and Notepad. The leading zeros have definitely been stripped off and it appears to be a numeric value. The field type in access that is being converted is short text. Interestingly enough we just started the next portion of testing where we import the csv file into an accounting program called Munis. We are having a problem in that one where the numeric values are being rounded to integers during the upload. Thankfully that is a problem for Munis to solve.

[June7]

What method are you using for export? The wizard is not saving as CSV.

I tried the wizard and TransferText. Both preserved the leading zeros when viewed in Notepad, only the wizard export showed leading zeros when viewed in Excel.