Microsoft Excel will automatically convert data columns into the format that it thinks is best when opening comma-separated data files. For those of us that don’t want our data changed, we can change that behavior.

You have a CSV file probably exported from other application or given to you by other people. You would like to open it up in Excel since you know it very well. The CSV file will look like this (if you open the file in notepad.exe):

The file contains columns: ProductModelID, Name, SpecialID, and ModifiedDate. The column SpeicalID containing data with leading 0 and it is specialized code so you would like to keep the leading 0s in Excel. Since it is a CSV file it is, by default, shown in your Windows Explorer as an Excel file:

So you double-click the file and open the file in Excel as such:

You can see that by default, Excel stripped out the leading 0s in column 3 “SpecialID�?. You brought up Excel application and tried to use File/Open to open the file manually and that doesn’t work either. How can I fix this?

The trick is to rename the file with .TXT extension then bring the file into Excel. SO first bring up “My Computer�? and navigate the folder to where the file resides.

Now, if you don’t see the file extension .CSV (Windows has this option disabled by default) then follow this step to enable the file extension – Select the menu “Tools�?/�?Folder Options�? then select the “View�? tab, uncheck the item “Hide extensions for known file types�? as the picture below:

Then you should see the file extension as such:

Right click the file and select rename to rename the file to become “ProductInfo.txt�?

Notice the icon changes to regular text file icon as well. No open up Excel and use “File�?/�?Open�? command to bring the file into Excel. When you do this, a dialog will pop up like:

Make sure the “Delimited�? radio button is selected and click on “Next�? button to continue. On the next screen, check the box “Comma�? and uncheck all other selections.

Click on “Next�? to advance to the next screen. In this screen, you need to click on the “SpecialID�? column and select the “Text�? radiobutton from the “Column data format�? section.

Click on “Finish�? then you are done.

You can see now the column “SpecialID�? has been brought in as a Text column and all leading 0 are preserved.

This method can be applied to any special circumstance for bringing in a CSV file into Excel including other delimited files. With the ability of specifying the data type for each column during the data importing process you are in much better control on your final result.