RSS

How-To Geek

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.

Comments (23)

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.

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.

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.

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!

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.

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).

GEEK TRIVIA

DID YOU KNOW?

Popeye the Sailor Man, the iconic early 20th century spinach-eating comic strip character, was originally intended to be a one-off cameo in a comic strip focused on Olive Oyl (Thimble Theater), but he proved to be so popular the roles were reversed and Olive Oyl became a part-time player in a dedicated Popeye comic strip.