Management Systems

22 Apr 2013

How can I keep leading Zero in CSV files?

When I used export data as a CSV files, all of leading are cut off any number when I open file in EXCEL. How can I keep the zeros?

This is actually Microsoft Excel issue. The program automatically truncates all leading zeros from numbers in CSV files. The key is to change at least the columns where the leading zeros occur (i.e. student’s IC number, phone and handphone) to “text.”

There are several options to do this. Start with the report open:

Step: (on Microsoft Excel XP)
1: Open a new worksheet in Excel
2. Open the menu path for Data -> Import External Data -> Import Data

3. Click on the From text button in the Get External Data section

4. Select your CSV file to import

5. Select the “Delimited” radio button — Text Import Wizard, Step i determines that your data is delimited

Step i
Click next.

Step ii
Make sure delimiters for Tab and comma is tick. Click next.

Step iii
Mark those columns format as “text” by clicking the radio button in the Column Data Format section.

You will need to do this for each column where the data contains leading zeros.


Click Finish


6. Click Ok. The leading zeros will still be there in the new worksheet with the imported data.

RSS feed for comments on this post