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
There are two options for opening the CSV file in Excel to preserve the data: Either rename the file to change the extension from .csv to .txt, or enable the Legacy Data Wizard in Excel and use that menu option to open the .csv file. Either option will end up using the same Text Import Wizard interface to specify the column types.
Changing the file extension:
- 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".)
- In Excel, begin the process of opening a file.
- 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..."
- Change the File Type to "All Files (*.*)"
- Double-click your text file (or select it and click OK button). The Text Import Wizard window appears.
Using the Legacy Data option:
- In Excel, go to File > Options Data. Under Show legacy data import wizards, check the box for From Text (Legacy), then hit OK.
- On the Data tab, click the Get Data drop down menu, then select Legacy Wizards > From Text (Legacy)
- Select your file in the browser window and click Import. The Text Import Wizard window appears.
Using the Tex Import Wizard:
- Make sure the radio selector for Delimited is selected, and check the box for My data has headers, then click Next.
- In the Delimiters selection, uncheck Tab and check Comma, then click Next.
- All of the columns will default to General, which allows Excel to change data automatically, often incorrectly. Selecting each column in the Data Preview window sequentially, select Text for the numbers columns that need to retain the data exactly as is. For Date columns, select Date and then your preferred order (MDY, YMD, etc) from the sub-menu. You can also choose Do not import column (skip) for any columns you know are not needed.
- Once all columns have been designated, click Finish.
Notes
- 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.
- 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. - This Excel workaround also works for Spreadsheets from Kingsoft (a freeware spreadsheet program).