SEARCH

How-To Geek

How To Import a CSV File Containing a Column With a Leading 0 Into Excel

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

Comments (23)

  1. Mikey Mac

    Another way to do this – especially if the column is of a fixed umber of chars like “SpecialID” above – you simply highlight the column or cells you want to fix, right-click, Format Cells…, click Custom on the Number tab, and type four zeros (or however many chars the field should be) and click OK. Excel will automatically pad the field with leading zeros.

    The bonus to this method is that you can still do calculations with the number without having to use complex formulas to convert the text “number” into a real number.

  2. Jim Millard

    A third alternative, assuming you have control over the creation of the CSV, is to export the “SpecialID” field as a formula, e.g., instead of exporting (0001), export (=”0001″). That forces the value to be evaluated as text, as Excel evaluates the field as a text formula.

  3. Erin T

    Thank you! I was really struggling with this and appreciate the easy to follow instructions. My problem is solved!

  4. Paul F

    Thanks much. This page helped me a lot — exactly the info I was looking for.

  5. Prathap

    Thanks all. This helped to solve my problem.

  6. andrew marshall

    when importing part number from a csv file some number cotaining E
    eg 977012e000 when importing into excel 2007 the number end up looking like 9770+e can you please advise me how to fix this as every part number containing e is treated the same way.

  7. Anon

    Thanks

  8. Vivek

    Jim’s option worked for me. No need to manipulate the file later.

    Thanks

  9. husker

    Jim’s option is really simple when compared to other solutions out there and worked… thanks

  10. tapas

    i still can’t solve my problem,
    its same
    can,t open my .csv excel files in office 2007.

  11. Rafa

    Thanks. Jim’s option was exactly what I was looking for.

  12. another1

    Jim’s solution is very good. Thanks for it Jim.

  13. Jax Stephens

    Jim, you’re the man! I Googled all over for this, and everyone had horrible solutions that don’t address the root issue. But leave it to a lone man’s comment to an article that gets it wrong by showing the long, hard way. Bravo!

  14. Kavitha`

    I followed the procedure as Mickey and Jim suggested. It worked out. But if i try to open the CSV file again, zero’s are getting vanished. Suggest some other idea..

  15. Loreia

    Jim Millard’s the man.
    Thanks a lot for your great advice.

  16. Sequoia

    Way to go on this essay, helepd a ton.

  17. Leon Kehoe

    I haven’t tried the method in the article, but I will later. The custom field method does not work, neither does changing the cell’s contents to a formula because as soon as you save the file as a *.csv the zeros are stripped.

  18. Leon Kehoe

    I think what I’m trying to achieve is different from the rest of you. I want to be able to save a *.csv file that retains leading zeros in cells, that might just be impossible.

  19. Yayita

    I worked! I was having problems importing subtitles to google docs. Now the digits are showing as they suppose to. Thanks!

  20. Yeya

    Thanks, Jim Millard, for me works. in sql i export ‘=”‘ + ’0001′ + ‘”‘ as x ; in postgres ‘=”‘ || ’0001′ || ‘”‘ as x

  21. hendri

    Damn!! I use the wrong way, I replace “,” to “,”‘ in notepad, stupid of me..
    I think of that after you explain little trick above.. Thanks you so much

  22. Rob

    I have followed all the steps outline successfully, but when I “Save As” after the last step and save as a CSV file, then reopen, the leading zeros are striped out again. I need this in a CSV format so I can import into Salesforce. Any advice?

    To reiterate, I need to save a CSV file, then when I open it back up, see the leading zeros (which excel is currently removing).

    I’ve tried formatting as text, zipcode and custom formatting, no dice.

  23. Rob

    Nevermind, I figured it out.

    I just needed to format the cells as zipcode, then save as a CSV file, then update/import into Salesforce BEFORE opening the file.

    I was opening the final fileto confirm if the leading zeros were added (before importing) which reverted the file to remove the leading zeros.

    Just format as zipcode, save as CSV and import/update – and you’ll be fine.

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!