leading zeroes

[ajm]

help: am losing my leading zeroes when exporting a table for access to csv file. whats happening and how do i fix it?

 

[Andrew Fergus]

What is the format of the field that is losing the leading zeroes? Have you tried converting that field to a text format? Lastly, how are you exporting the file?
Andrew

 

[ajm]

andrew,

(cool name BTW). am exporting using the file menu option Save as/export - external db or file, and then saving it as a csv file. the column is a summed column that is formatted as text in the query and the resulting table. it is only when you do the export that it reverts to number format.

weird huh!

 

[Andrew Fergus]

I presume you opened the file with something like Excel and I expect Excel will be imposing it's own formats (hence the leading zeroes are dropped). Have you checked the file (before opening it with Excel) by viewing it with Notepad? You might find the zeroes are there but Excel is dropping them. BTW how exactly are you exporting to a csv file? I have Access 2K and cannot see csv as an export option - are you trying to export while viewing the data in a table or from a query or somethign else?

 

[Joe4]

We have had this problem happen numerous. The problem isn't saving it to a CSV file. Rather, it is opening it in Excel (which, I think was what Andrew was alluding to).

Opening a CSV in Excel doesn't invoke the Data Import Wizard; Excel tries to figure out the formats itself, and chooses a numebr format, which of course, drops the leading zeroes.

There are a few ways around this:
1. Export the file from Access to another text file option (i.e., .txt, .prn)
or
2. Export to a CSV file, but then rename the .csv extension to .txt before opening/importing into Excel. This will now cause the Data Import Wizard to be invoked, where you can control the format of the incoming data.

 

[Andrew Fergus]

Aha! At least someone knew what I was talking about - even if I didn't explain it very well....