Why Excel Changes Your CSV Data (and How to Stop It)
Updated 3 min read
You open a CSV file in Excel, save it, and suddenly zip codes have lost their leading zeros, order IDs end in 000 and part numbers have turned into dates. Excel did not mean to corrupt the file: it guessed what each value was. Here is what it guesses wrong and how to keep your data intact.
Leading zeros disappear
When Excel sees 00501 it decides it is the number 501 and drops the zeros. Zip codes, phone numbers, employee IDs and product codes are all affected. Once you save the file as CSV again, the zeros are gone for good.
Long numbers become 1.23457E+15 and lose digits
Excel stores numbers with 15 significant digits of precision. A 16-digit card number or a 19-digit order ID is converted to a number, displayed in scientific notation, and every digit after the 15th is replaced with a zero. Unlike leading zeros, those digits cannot be recovered from the saved file.
Text becomes dates
Values like 1-2, 3/4 or MAR1 are converted to dates because Excel tries to recognize dates in many formats. This is common enough that scientists renamed several human genes, such as SEPT1 and MARCH1, so that Excel would stop turning them into dates. The result also depends on regional settings, so the same file can change differently on different computers.
Accents turn into é and columns are wrong
Excel assumes a CSV uses your computer's legacy encoding unless the file starts with a byte order mark, so UTF-8 accents and symbols appear garbled. It also uses your regional list separator: in many European countries it expects semicolons, so a comma-separated file opens with everything in column A. See garbled characters in CSV files and the delimiter converter.
How to open a CSV without changing it
Do not double-click the file. Import it instead, so you can tell Excel which columns are text:
- In a blank workbook, go to Data > From Text/CSV and choose the file.
- Check that File origin is 65001: Unicode (UTF-8) and the delimiter is right, then click Transform Data.
- In Power Query, select the columns that must stay exactly as written (IDs, zip codes, phone numbers), set Data Type to Text and click Close & Load.
Recent versions of Microsoft 365 for Windows also let you switch off some of these conversions under File > Options > Data > Automatic data conversion, for example to keep leading zeros and long numbers.
Or skip the problem: convert the CSV to .xlsx first
An .xlsx file stores each cell's type explicitly, so Excel has nothing to guess. Convert the CSV to Excel with CleanCSV and values with leading zeros, long IDs and date-like text stay as text, while plain numbers and ISO dates become real numbers and dates. Open the .xlsx and everything looks the way it should.
Saving back to CSV safely
- Use File > Save As with the CSV UTF-8 (Comma delimited) type so accents survive.
- Check that the columns you imported as text are still text before saving.
- If the CSV will be imported somewhere else, open the saved file in a CSV viewer to confirm the values are exactly what you expect.