Why is Excel removing leading zeros when displaying CSV data?

Just ran into this issue. If you control the creation of the CSV file you can make the data look like the following:

"Col1","Col2"
="01",A
="2",B
="10", C

When opened up as Excel it will preserve the 01.

When you open the csv, you'll have the option to specify the delimiter and data type for each column. The text import wizard has 3 steps. Note that my screen shots are from Excel 2010, but this will work in exactly the same manner in Excel 2003.

Step 1: select delimited (instead of fixed width)

Step 2: select comma as your delimiter

Step 3: select each column and change the data format to "text". (You will see the word Text displayed above each column as in the screen shot below.)

The leading zeros will no longer be removed:

UPDATE: If you don't want to navigate through the process to get Excel to cooperate, you can use CSVEd, an editor designed specifically for editing delimited text files. A good text editor like Notepad++ might also work, but you won't be able to see your data arranged in columns.