Stop Excel from stripping out leading zeros when saving as CSV

[Guest]

Hi, I am getting strange behaviour from Excel when working with CSV files.

It's quite easy to reproduce the problem:
1) In a text editor, such as Notepad, create the file "test.csv", containing
this data:
001,hello
2) Close the file
3) Open the same file up in Excel and change the value in B1 to "goodbye"
4) Save, clicking "Yes" when Excel asks if I want to keep CSV format, and
close Excel
6) Open the file in the text editor. It now contains:
1,goodbye

In other words, Excel has decided that "001" should be saved as "1". Is
there any way to stop this?
Regards
Matthew

 

[Guest]

It's not the saving, it's the re-opening. After you save the file as .csv,
open the csv with notepad, the leading zero will be there.

If you rename the file to .txt and then open it with Excel, Excel will bring
up the Import Wizard and allow you to specify text.

 

[Guest]

Many thanks for the help, Gary's Student.

However, I'm not sure what you're saying is correct. Have another look at my
original post. After saving the file in Excel, I then do what you've
suggested (see my step 6 - which should be step 5! sorry): open the CSV file
in a text editor. And the zeros are gone.

In other words, it's not a presentation issue - Excel really has stripped
out the leading zeros. Despite the fact that I didn't go anywhere near the
relevant part of the file (namely, cell A1), during my editing.

All the best
MattM

 

[David Biddulph]

Many thanks for the help, Gary's Student.

However, I'm not sure what you're saying is correct. Have another look at
my
original post. After saving the file in Excel, I then do what you've
suggested (see my step 6 - which should be step 5! sorry): open the CSV
file
in a text editor. And the zeros are gone.

In other words, it's not a presentation issue - Excel really has stripped
out the leading zeros. Despite the fact that I didn't go anywhere near the
relevant part of the file (namely, cell A1), during my editing.

What he was saying was that the problem was when you read the original .CSV
file into Excel. If you rename the original file to .TXT then you can
specify the cell formats as text when you read the file in, but if you
merely read a .CSV file into Excel, it will make its own mind up on the cell
format and you'll get the problem you reported.

 

[Guest]

Both - many thanks for the information. I'm now finding that using the import
wizard followed by "Save As CSV" causes Excel to convert the comma-separated
text file into a tab-separated CSV file (!) which is equally frustrating.
Still, you're right - it doesn't strip out the leading zeros and I should be
able to find some workarounds with a bit more experimentation.

I'm also toying with the idea of writing a macro which adds leading zeros
preceeded by an apostrophe to all the cells in a given column, which might
prove fruitful.

Thanks again,
MattM