Many programs export data to CSV files (Comma Separated Values) because they are almost universally recognized. Excel can easily open a CSV file and automatically split into columns but there are some common problems you’ll run into when doing that. One of the most common problems is leading zeros.
Zip codes (postal codes), invoice numbers, and part numbers are just a few instances in which leading zeros are common. The problem is that true numbers don’t begin with a zero. So when Excel sees a string of digits with no letters or other characters, it assumes that string must be a number and, logically, eliminates any zeros from the beginning of the string. 01234 would get converted to 1234. Fortunately there’s an easy way around this.
Start by renaming the CSV file to a TXT file. Simply right click on the file, choose Rename and replace the .CSV at the end of the file name with .TXT. If you don’t see the “CSV” follow these steps to turn off the option to hide file extensions.
- Open the Start menu
- Type Folder Options and hit Enter
- When the Folder Options window opens select the View tab
- Uncheck the box next to Hide extensions for known file types .
- Click OK
Once you have the file renamed, open Excel. To open your file, go to File > Open. At the bottom of the “Open” window, change the file type from “Excel Files” to “Text Files (*.prn; *.txt; *.csv)”. Select the file you just renamed and open it.
When you open the file, the Text Import Wizard will appear.
- On step 1 leave it set to Delimited.
- At step 2 change the delimiter to Comma
- On step 3 locate the column that contains the leading zeros, click to select it, and set the “Column data format” to Text.
- Click Finish
Excel will open the file but now it will leave the leading zeros in place because it thinks they are text instead of numbers. If you want to save the files as a CSV again after working with it, choose Save As and change the file type from “Text (Tab delimited) (*.txt)” to “CSV (Comma delimited) (*.csv).
