Why Excel ruins your CSV files

The gene names Excel ate

Open a CSV in Excel and it starts guessing. Every cell runs through a type detector before it reaches the grid, and that detector was built for someone typing into an empty spreadsheet, not for a file that already contains correct values. The most expensive casualty of that design is genetics.

Human gene symbols include SEPT2, MARCH1, DEC1 and OCT4. Excel reads SEPT2 as the second of September, stores a date serial number and shows you a date. MARCH1 becomes 1-Mar. The original string is gone, not hidden: the cell now holds a number with a date format on top, and saving the file writes that number back out.

How often does that actually happen in published science? A 2016 study in Genome Biology by Mark Ziemann, Yotam Eren and Assam El-Osta went and counted. They scanned 35,175 supplementary Excel files from 18 journals published between 2005 and 2015, found 7,467 gene lists attached to 3,597 papers, and confirmed mangled gene names in 987 files belonging to 704 of them. That is 19.6 percent of the papers with an Excel gene list, roughly one in five.

The follow-up is the part people usually miss. In July 2021 Ziemann and colleagues published a second pass in PLOS Computational Biology, covering 2014 to 2020, and found errors in 3,436 of 11,117 papers. 30.9 percent, with no downward trend after the 2016 paper made the problem famous. Five years of awareness campaigns moved nothing.

So in 2020 the HUGO Gene Nomenclature Committee gave up and renamed the genes instead. 27 human gene symbols were changed to survive spreadsheets: SEPT2 became SEPTIN2, MARCH1 became MARCHF1, and the revised naming guidelines published in Nature Genetics explicitly allow renaming symbols that "affect data handling and retrieval". Read that again. The naming authority for the human genome changed the vocabulary of the human genome because a spreadsheet would not stop parsing it. We have never seen a better argument against letting a UI guess types.

Leading zeros, scientific notation and the 15-digit wall

Dates are the famous one. The number handling is worse, because it fails quietly and the result still looks plausible.

The 15-digit line is the one worth memorising. Excel stores numbers as IEEE 754 doubles but deliberately limits itself to 15 significant decimal digits, so a 16-digit card number loses its last digit to a zero and an 18-digit Twitter-style ID collapses into scientific notation with nothing left to recover. Any identifier longer than 15 digits is text, full stop. If you never do arithmetic on a value, it was never a number in the first place.

Dates that do survive have their own quirk. Excel’s serial 60 is 29 February 1900, a day that did not exist, deliberately kept for compatibility with Lotus 1-2-3, which means dates before 1 March 1900 are off by one day compared to every other system. It rarely bites, but when it does you will not find it by staring at the grid. Dates in exchange files get their own write-up in why you should store dates in UTC; the short version is ISO 8601 strings, UTC, and no locale formats anywhere near a CSV.

The comma that isn’t a comma

CSV feels like a standard and mostly isn’t. RFC 4180 was published in October 2005, describes commas, CRLF line endings and double quotes escaped by doubling, and carries the Informational label: it documents what people were already doing rather than defining a protocol. The format was two decades old by then. Every parser difference you have ever fought comes from that gap.

Excel adds its own layer on top. It does not split on the comma, it splits on the list separator from the operating system’s regional settings. On German, Austrian, French, Italian and Spanish systems the decimal separator is a comma, so the list separator is a semicolon. Two consequences, both annoying:

• A perfectly valid comma-separated export from your API opens on a German laptop with every row crammed into column A. Nothing is broken, the user just sees garbage and files a bug against your export.
• The same Excel writes semicolon-separated files and still calls them .csv. Your importer, tuned to commas, sees a single column. Both sides think the other one is wrong.

The escape hatch is the sep= line: make the very first line of the file sep=, or sep=; and Excel uses that delimiter regardless of locale, on Windows and on macOS. It is genuinely useful for files you hand to colleagues. It is also completely non-standard, and pandas, Python’s csv module and most database import tools will happily read it as your first data row. We cleaned that mess up once and have used sep= only in files meant for a human with Excel ever since, never in an API export or anything a script picks up.

One more delimiter detail that costs afternoons: fields containing a newline are legal in RFC 4180 as long as they are quoted. Plenty of hand-rolled parsers split on \n first and quote-parse second, which shreds exactly those rows. If your row count is off by a few and the broken records all contain free-text comments, that is your bug.

UTF-8, the BOM and the broken umlauts

Double-click a .csv on Windows and Excel reads it with the system ANSI code page, Windows-1252 in Western Europe, not UTF-8. So Müller arrives as Müller, Straße as Straße, and a French export turns into confetti. The mechanism behind those exact character pairs is the same one we picked apart in mojibake explained: UTF-8 bytes interpreted one at a time as single-byte characters.

There are two ways out. Import the file through Data > From Text/CSV and set File Origin to "65001: Unicode (UTF-8)", which is correct but requires the recipient to do something. Or start the file with a UTF-8 BOM, the three bytes EF BB BF, which makes Excel detect UTF-8 on a plain double-click.

The BOM works, and it is a trade rather than a fix. That same BOM shows up in every other consumer of the file: strict JSON parsers throw on it, a header row read by pandas gets a first column literally named id instead of id, and shell scripts with a BOM in front of the shebang fail with "bad interpreter". It is an invisible character with real consequences, which is a whole genre of bug in itself, covered in invisible Unicode characters. The workable split: machine-readable exports without BOM, a separate "for Excel" download with BOM if humans need one.

What Excel finally fixed in 2023

After roughly three decades of this, Microsoft shipped actual switches. Automatic Data Conversion reached general availability in October 2023 with Excel for Windows version 2309 (build 16808.10000) and Excel for Mac 16.77. It lives under File > Options > Data > Automatic Data Conversion and lets you turn off, individually:

• removing leading zeros from numerical text and converting it to a number
• truncating numerical data to 15 digits of precision and showing it in scientific notation
• converting numerical data around the letter "E" to scientific notation
• converting a continuous string of letters and numbers to a date

Opening a CSV also gets you a banner offering to open it without any conversions at all. Good change, and it does not solve the problem people think it solves. The settings are per user, per machine, stored in the Excel profile rather than in the file, and they are off by default. They also do nothing about the delimiter or the encoding guessing. You can protect your own Excel; you cannot protect the accountant in another department who opens your export and saves it.

Opening a CSV without damaging it

The short version: never double-click a CSV you care about.

• Import, don’t open. Data > From Text/CSV opens the Power Query preview, where you set the encoding, pick the delimiter and mark every risky column as Text before a single value is parsed. It costs fifteen seconds and it is the only reliable path in Excel.
• Rename to .txt if you are on an old build without Power Query. Excel then shows the legacy text import wizard instead of silently guessing, which gets you the same per-column type control.
• Ship .xlsx when the recipient is a human. An xlsx file carries its own types, so a column declared as text stays text no matter whose regional settings it lands on. Keep the CSV for machines.
• Treat any Excel round trip as a rewrite. Saving writes back what is in the grid, including every conversion done on import, plus Excel’s own quoting and line endings. Diff the before and after once and you will stop trusting "I only opened it".

Two workarounds you will find everywhere and should use sparingly: prefixing values with an apostrophe, and writing ="01234" into the CSV. Both force text in Excel, both put junk into the file for every other reader, and the formula variant is a small CSV injection risk when the file is opened somewhere you did not expect. If you need a spreadsheet with types, produce a spreadsheet, not a CSV wearing a costume.

Worth knowing if you have the choice: LibreOffice Calc shows its text import dialog every single time you open a CSV, with encoding, separator and per-column types on one screen. It cannot silently guess wrong because it never guesses silently.

A pipeline with no Excel in it

The reliable fix is structural: keep Excel out of the part of the process where data is created or converted, and let it be a viewer at the very end.

Most CSVs in a dev workflow start life as JSON, an API response, a database dump, a log export. Converting that to CSV in a browser tab instead of pasting it into a sheet removes the entire failure mode, because nothing along the way is trying to detect types. Our JSON to CSV converter runs completely client-side, so the payload never leaves the machine, and you choose the delimiter yourself instead of inheriting whatever the recipient’s regional settings decided. For the reverse direction, keeping the canonical data in JSON and generating CSV on demand beats maintaining a spreadsheet that three people have opened.

The other half is your export code. Quote every field, escape embedded quotes by doubling them, write ISO 8601 dates, emit UTF-8, and decide once whether that export is for machines (no BOM, comma) or for Excel users (BOM, and a sep= line if your audience is in Europe). Publishing both variants from the same endpoint with a query parameter takes an hour and ends the support tickets permanently. We have shipped that twice now, and both times the "Excel breaks your export" reports went to zero.

Excel and CSV, the recurring questions