Blog Archive

Import a CSV File into EXCEL containing numeric data with Leading ZEROS as Text

The issue with numeric data is as soon as you open the CSV file in Excel, the Excel will try to out smart you and make the fields numeric and drop the leading zeros. You can even try making the column format Text and try to paste the column again but to no avail. Here is a sample CSV file that is opened using Notepad++ and as you can see the Item number is within double quotes and it's a string(text) and not a numeric data.


Excel Import CSV with Leading Zeros as Text data

Now when I try to open the same file in Excel it makes the Item Number numeric and drops the leading zeros.


Excel Import CSV with Leading Zeros as Text data

To solve this issue you have to import the CSV file into excel rather than just opening the CSV file using EXCEL. Here are the steps ...

Step 1: Open Excel with an empty sheet



Excel Import CSV with Leading Zeros as Text data

Step 2: Click on Data > Import External Data > Import Data



Excel Import CSV with Leading Zeros as Text data

Step 3: Change "Files of type:" to All files and Select your CSV file



Excel Import CSV with Leading Zeros as Text data

Click Open ...

Step 4: In the Text Import Wizard Click the delimited Radio button



Excel Import CSV with Leading Zeros as Text data

Click Next ...

Step 5: In the Delimiters section select Comma



Excel Import CSV with Leading Zeros as Text data

Click Next...

Step 6: Change Column data format Radio button to Text for Item Number



Excel Import CSV with Leading Zeros as Text data

Click on all numeric columns that you want as Text and do the same, then click Finish ...

Step 7: Just click OK for Existing Worksheet or change if you want



Excel Import CSV with Leading Zeros as Text data

Step 8: All done !



Excel Import CSV with Leading Zeros as Text data

3 comments :

  1. If you make your CSV file contain "text",="001","more text" then opening it as a CSV will correctly read the 001 as text and keep the leading zeros. So you dont have to use the manual Text Wizard.

    ReplyDelete