Why Excel ruins your CSV

The damage happens when you open it

This is the part that makes the problem so hard to diagnose. A CSV is a text file: every value in it is characters, and nothing in the file says what any of them mean. When Excel opens one it guesses a type for every column, converts the values to match, and from that moment holds the converted version. Save the file and the conversions are written back.

So the file was fine, the file you saved is not, and at no point did anything warn you. People conclude the export was broken, re-export it, and get the same result — because the corruption is happening on their own machine, after the download.

The four conversions that cause the damage

Leading zeros are stripped. A postcode of 01234, a French department code, an account number, a phone number with a leading zero — all look numeric, so the zero is discarded as meaningless. It is not meaningless; it is part of the identifier.

Long numbers are rounded. A spreadsheet holds numbers as floating point with about fifteen significant digits, so an order reference, a credit card number, an IMEI or a 64-bit identifier is silently rounded — the last digits become zeros. The cell looks like a number and is the wrong number.

Anything resembling a date is reformatted, using the local convention. 03/05 means March in one country and May in another, so the same file opened in two offices produces two different datasets. Worse, values that were never dates get converted: a gene name like SEPT1 becomes a date, a problem so persistent that geneticists eventually renamed the genes rather than keep fighting it.

Accented characters turn to mojibake. A CSV carries no record of its character encoding, so a file saved as UTF-8 and read as a legacy code page — or the reverse — mangles exactly the rows containing names with accents in them.

The fix: import, do not open

Never double-click a CSV you care about. Double-clicking gives Excel permission to guess, and it guesses confidently.

Instead use Data → From Text/CSV. The import dialogue lets you set each column's type before anything is parsed — mark the postcode column and the identifier column as Text, and they arrive exactly as they are in the file. It also lets you state the encoding and the delimiter rather than having them inferred. It takes twenty seconds and it is the whole solution.

The same applies in Google Sheets: File → Import, and turn off "Convert text to numbers, dates and formulas". LibreOffice Calc shows the column-type dialogue by default, which is one of the few areas where it is simply better behaved.

Inspect before you import

When something has already gone wrong, the useful first step is to look at the file without a spreadsheet touching it. Viewing the CSV shows the values exactly as the bytes say they are — leading zeros present, long numbers complete, dates as the text they were written as. That tells you immediately whether the export was wrong or the import was.

It also shows you the two things a CSV never states about itself: the delimiter — a comma, a semicolon or a tab, and European locales export semicolons because the comma is their decimal separator — and the encoding. Knowing both before you import removes most of the remaining guesswork.

If you are producing the CSV

A few decisions at the export end prevent all of this downstream.

Write UTF-8 with a byte order mark if the file is destined for Excel on Windows. Excel uses the mark to detect UTF-8 and mangles accented characters without it — one of the rare cases where adding a BOM is the right call rather than a nuisance.

Quote every field that could be misread, and quote identifier columns always. Quoting does not stop Excel converting on open, but it makes the intent unambiguous for every other consumer.

Consider not using CSV at all. If the recipient can take JSON or a real spreadsheet, both carry types explicitly and neither has any of these failure modes. CSV's virtue is that everything reads it; its weakness is that nothing agrees on what it says.