How Excel silently mangles CSV files (and how to stop it)

Open a CSV in Excel, change nothing, hit save — and the file is no longer the file you received. Excel applies “helpful” conversions on open, before you touch a single cell, and bakes them in on save. None of it is announced; all of it is data loss. Here’s the complete damage list, why it happens, and what actually prevents it.

The damage list

Leading zeros disappear. 02134 (Boston), 00501 (an IRS processing ZIP), item code 00042 — anything that parses as a number becomes one, and numbers don’t have leading zeros. US East Coast ZIP codes are the classic casualty: every Massachusetts and New Jersey ZIP starts with 0.

Things that look like dates become dates. 1-2 turns into 2-Jan, MARCH1 and SEPT1 style codes get reinterpreted, and product/lot codes with slashes are fair game. The most famous victims are geneticists: gene symbols like SEPT1 and MARCH1 were converted to dates in supplementary spreadsheets so often — a systematic scan found errors in roughly a fifth of genomics papers with Excel gene lists — that the human gene-naming committee renamed 27 genes in 2020 (SEPT1 is now SEPTIN1, MARCH1 is MARCHF1) partly to stop Excel eating them. When a scientific nomenclature changes to accommodate a spreadsheet’s autocorrect, “mangling” is not too strong a word.

Long numbers get rounded. Excel stores numbers as 15-significant-digit floats. A 16-digit credit-card-style number or order ID — 4111111111111111 — displays as 4111111111111110, and a 20-digit ID becomes scientific notation (4.11111E+15). The original digits are gone, not hidden: saving writes the rounded value.

Dates get rewritten into your locale. An unambiguous 2024-03-31 can come back as 3/31/2024 — or 31/03/2024 on a UK system — after a save. Cross-locale roundtrips are how 03/04/2025 files become genuinely unanswerable (“March 4th or April 3rd?”).

Encoding can shift. Legacy “CSV (Comma delimited)” saves use a Windows codepage, not UTF-8 — accented and non-Latin characters that arrived intact leave corrupted. (Modern Excel offers “CSV UTF-8” — choose it if you must save from Excel at all.)

Delimiters follow your region. On comma-decimal locales (most of continental Europe), “CSV” saves with semicolons. The file may not parse at all on the comma-expecting system it’s headed to — the delimiter guide covers this one in depth.

Why Excel does this

It isn’t malice, and mostly it isn’t even a bug. Excel’s job, for fifty million users, is turning keyboard input into typed values: someone typing 02134 almost always means the number, and someone typing 3/4 usually means a date. The catch is that Excel applies these keyboard heuristics to files — where the bytes were produced deliberately by another program and every “correction” is corruption. CSV has no type annotations to defend itself with, so Excel guesses, and guessing is the bug.

What actually prevents it

Recent Excel: turn the conversions off. Microsoft 365 and Excel 2024 have File → Options → Advanced → Automatic Data Conversion, where you can disable removing leading zeros and the 15-digit truncation/scientific-notation rewrite, and tick “notify me of any automatic number conversions” when loading a .csv. If you live in Excel, set these once — it’s the single best fix on this page. (Date conversion of things like SEPT1 still has no complete off-switch there; hence everything below.)

Import, never open. Excel’s Data → From Text/CSV (Power Query) route lets you set each column’s type — force ID and ZIP columns to Text and nothing gets converted. The old-style Text Import Wizard does the same job. It’s the documented, supported path; the only problem is remembering to use it every single time, because double-clicking the file bypasses it.

Or don’t let Excel near the raw CSV at all. For a look, a filter, a dedupe or a format conversion, use a tool that doesn’t coerce types — a structure-preserving viewer/editor shows the bytes as they are and exports them unchanged. When the deliverable must be an Excel file, convert CSV → .xlsx directly: a proper converter declares each cell’s type in the workbook itself — leading-zero values and long IDs written as text cells, real numbers as numbers — so Excel opens it with nothing left to guess about.

The apostrophe trick, for one-off cells. Typing '02134 into Excel keeps the zero (the apostrophe forces text). Useless for a million rows, worth knowing for one.

How to tell if a file has already been mangled

• ZIP/postal-code column with values of different lengths (4 and 5 digits mixed): zeros are already gone. Recoverable for US ZIPs by left-padding to 5 — regex find & replace handles it.
• Random dates sprinkled in a code column (2-Sep, Mar-01): date conversion happened. Not reliably recoverable — the original strings are gone; re-export from the source.
• IDs ending in 0 suspiciously often, or scientific notation in a text column: the 15-digit rounding. Not recoverable; re-export.
• A row count of exactly 1,048,576: the file was truncated at Excel’s row limit somewhere upstream.

The pattern in all four: the mangling is invisible at the moment it happens and expensive to discover downstream. The fix is structural — keep raw CSVs away from type-guessing software, and do conversions with tools that treat your bytes as facts rather than suggestions.

Gene-renaming episode and the genomics error-rate study as reported by the genome-community press and the HGNC’s 2020 guideline update; Excel behaviors verified against Microsoft’s documentation of the Automatic Data Conversion settings, July 2026.