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.
- Published 04/20/07