CSV Files And Leading Zeroes

Home/Data Handling/ CSV Files And Leading Zeroes

CSV Files and leading zeroes

I use Excel to create a csv file. When I reopen the csv file in Excel I lose all the leading zeroes. Is there a way to stop this?

By Neale Blackwood
You have to trick Excel into using its built-in Text Import Wizard. A csv (Comma-Separated Values) file is a special type of text file that is commonly used to transfer table data between different systems. Because Excel knows the structure of a csv file, it automatically opens it and drops all leading zeroes. Excel will drop off leading zeroes unless you define the data as text. You need to use the Text Import Wizard to do this.

First make a copy of the .csv file and rename it with an extension of .txt. If the file was data.csv rename it to data.txt. Because .txt files are a general text file Excel will have to get directions on how to read it.

Now open the .txt file from Excel. You may need to amend the File of Type option at the bottom of the Open dialog to All Files so you can see the .txt file. Step 1 of the Wizard asks if it is delimited. A .csv file is delimited by a comma, so click the Next option and in step 2 select comma from the delimiters options.
The data should then be split correctly into columns. Click Next and now you can define columns as Text by clicking on the column heading and clicking the Text option in the Column data format section. Do this for each column that has leading zeroes. Then Click the Finish button to complete the process.

2017-03-08T03:54:02+00:00 Data Handling|