Navigate
or
Contact Us
Subscribe Download PDF

Excel and CSV files: leading zeroes trimmed, numbers appear in exponential notation

Scott Leonard
2020-04-02
in FAQs

Problem

When opening a CSV (Comma Separated Value) file, Excel automatically trims leading 0s from data (even if the value should contain one or more leading zeroes) and/or displays large numbers in exponential notation (e.g. 7.03E+11). When resaved, the original values are lost, and when the CSV file is imported or linked with Wasp software, the values are incorrect.

Cause

Excel assumes that cells containing numeric digits should be treated as numbers, and converts the data. When saving changes back into the CSV file, the changes become permanent. These behaviors are characteristics of Excel, a Microsoft product.

Workaround

  1. Rename the file so the extension is .txt instead of .csv (If you don't see the extension, you will need to enable viewing them. If you're not sure how, do a web search for "view file extensions" and your version of Windows, for example "Windows 7".)

  2. In Excel, begin the process of opening a file.

  3. Find the File Type popdown menu. There may be a notation such as "Files of Type", or you may just see a popdown menu that starts "All Excel Files..."

  4. Change the File Type to "All Files (*.*)"

  5. Double-click your text file (or select it and click OK button). The Text Import Wizard window appears.

  6. Click Next (don't make any changes; file type is delimited).

  7. Uncheck the Tab delimiter, and check Comma. Click Next.

  8. Select the column(s) that you need to remain unchanged, and change the Column Data Format to Text. If you're not sure, change all the columns to Text. When done, click Finish.

  9. The data now displays in Excel with its original format.

Notes

  1. This workaround needs to be performed each time the CSV file is opened in Excel, since text-formatted files do not contain any data-formatting metadata.

  2. After importing the CSV file, we recommend doing a Save As in Excel format. This allows the file to opened, saved, closed, and reopened while retaining the data formatting.

    If you will be converting the file back to CSV format (e.g. in order to import), make that conversion your last step after working with the data. If the data still needs tweaking, go back to the Excel file (not the CSV) and edit, save, then convert to CSV again. Repeat as necessary.

  3. This Excel workaround also works for Spreadsheets from Kingsoft (a freeware spreadsheet program).