Why Excel Changes Your CSV Data (Leading Zeros, Long Numbers, Dates) and How to Stop It

By Talk & Tool4 min read

A CSV file is plain text. Excel is not a text editor: when it opens a CSV, it guesses a type for every cell and converts the value to fit that guess, silently and irreversibly once you save. Postcodes lose their leading zeros, order IDs turn into rounded scientific notation, and product codes become dates. None of this is a bug in your file.

The four conversions that do the most damage

Leading zeros disappear. 01234 looks like a number, so Excel stores 1234. US ZIP codes, UK phone numbers, employee IDs and bank sort codes are all affected.

Long numbers are rounded and rewritten. Excel stores numbers with 15 significant digits of precision. A 16-digit card-style reference or an 18-digit order ID has its trailing digits replaced with zeros and is displayed in scientific notation, such as 1.23457E+17. Saving the file writes the rounded value back, and the original digits are gone.

Codes become dates. 3-10, MAR1 and 1/2 are interpreted as dates in many locales. The problem was so widespread in genetics that the human gene naming committee renamed several genes — including SEPT1 and MARCH1 — in 2020 to stop spreadsheets converting them.

Text starting with symbols changes meaning. Cells beginning with =, +, - or @ can be treated as formulas. Beyond wrong values, this is a security issue known as CSV injection when a file built from user input is opened by staff.

The fix: import, do not open

Double-clicking a CSV lets Excel guess. Importing it lets you decide. In current versions of Excel on Windows and Mac, use Data → From Text/CSV, then choose Transform Data and set the vulnerable columns to Text before loading. On older versions, the legacy Text Import Wizard offers the same per-column choice.

Set these columns to Text every time: identifiers of any kind, postcodes, phone numbers, account and card-style references, and any code that mixes digits with hyphens or slashes. Leave genuine quantities as numbers.

Check the file is still intact first

If a CSV has already been opened and saved in Excel, the source may be damaged before it reaches you. Before importing anywhere, inspect a copy as plain text: look for E+ in ID columns, dates where codes should be, and values that are suspiciously shorter than their neighbours.

A fast way to see what a file really contains is to convert it with the CSV to JSON converter. JSON shows each field exactly as written, with quotes around strings, so a column of IDs that has already been mangled is obvious at a glance. It also exposes delimiter and quoting problems that a spreadsheet grid hides.

Producing CSVs that survive Excel

If you generate files that other people will open in Excel, you can prevent most of the damage on your side.

  • Add a UTF-8 byte order mark. Without it, Excel on Windows may read UTF-8 as a legacy encoding, turning accented characters into sequences like é.
  • Quote identifier fields. Quoting alone does not stop type conversion on open, but it keeps commas and line breaks inside fields from splitting columns.
  • Offer an .xlsx alongside the CSV for non-technical recipients. A proper workbook stores column types, so nothing is guessed.
  • Neutralise formula characters in user-supplied text by prefixing a single quote or escaping values that begin with =, +, - or @.

When the source is JSON, the JSON to CSV converter flattens nested objects into columns and quotes values consistently, which removes a whole category of broken exports.

Repairing data that has already changed

Some damage is recoverable and some is not.

  • Leading zeros can be restored when the correct length is known: pad each value back to five digits for ZIP codes, for example, with a TEXT(A2,"00000") formula.
  • Dates created from codes can sometimes be reversed if you know the original pattern, but ambiguous ones cannot.
  • Rounded long numbers cannot be recovered. The lost digits no longer exist anywhere in the file, so go back to the original export.

After a repair, check for rows that now collide. Two IDs that differed only in their final digits become identical after rounding, and remove duplicate lines in find-duplicates mode shows which ones need to be traced back to the source rather than silently merged.

A safe routine for CSV work

  1. Keep the original export untouched and work on a copy.
  2. Inspect the raw text before opening it in any spreadsheet.
  3. Import with explicit column types instead of double-clicking.
  4. Never save back to CSV from Excel unless you have checked the ID columns.
  5. Prefer JSON or .xlsx when the data moves between systems.

Related reading