Why your CSV opens wrong, and how to stop it

CSV is not really a format

Here is the uncomfortable thing at the root of every CSV problem: there is no CSV standard that everyone follows. There is a specification, RFC 4180, written in 2005, years after the format was already everywhere. It documents one common dialect. It does not oblige anyone.

So when you write a CSV, you are not producing a file with defined meaning. You are producing a pile of text and hoping the program that opens it makes the same guesses your program made. Most of the time the guesses match. When they do not, you get an afternoon of confusion and a spreadsheet full of wrong dates.

Everything below is a specific case of that one problem: two programs, two sets of assumptions, no referee.

Excel changes your data on open

The most famous casualty is genetics. The gene SEPT1 opens as 1-Sep. MARCH1 becomes 1-Mar. This got bad enough that a 2016 study found errors in a fifth of published genomics papers, and in 2020 the naming committee gave up and renamed the genes to SEPTIN1 and MARCHF1 rather than keep fighting the spreadsheet. That is how immovable the default behaviour was.

The same logic eats ordinary business data. A product code like 3-10 becomes 3 October. A ratio written 1/4 becomes a date in January. Anything that looks vaguely like a date is treated as one, because the software assumes you meant a date and would rather be helpful than literal.

Then there are numbers. A 16-digit order reference or a long credit card number becomes 1.23457E+15 in scientific notation, and worse, the trailing digits are gone for good, because a spreadsheet cell holds about 15 significant digits. Save the file and the real value no longer exists anywhere.

And leading zeros. UK postcodes, French departments, phone numbers with a country prefix, employee IDs padded to six digits: all of them lose their leading zeros the moment the column is read as a number. 007643 becomes 7643 and no warning appears anywhere.

Modern Excel has softened this. Recent versions ship a setting to stop automatic conversion of dates and long numbers, and they surface a notification when a conversion happens. But it is not on by default for most people, it does not travel with the file, and it does nothing for the colleague on an older build. Assume the mangling is still the default in the wild.

One colleague sees ten columns, another sees one

This is the single most reported CSV bug and it has nothing to do with your file being broken. In much of continental Europe the comma is the decimal separator: three and a half is written 3,5. A comma cannot then also separate fields without chaos, so the regional convention is a semicolon.

Spreadsheets follow the operating system locale, not the file. So a file written with commas in London opens perfectly in London and as a single unsplit column in Berlin, and the file itself is identical in both places. Nobody is doing anything wrong.

There is a legacy escape hatch that genuinely works: a first line reading sep=; tells Excel which character to expect. It is not part of any standard and other tools will read it as a stray row of data, so use it only for files you know are going straight into a spreadsheet.

The more robust move is to sidestep the ambiguity. Tabs almost never appear inside real field values, so a tab-separated file splits reliably regardless of locale. Running the file through CSV to TSV is often the fastest fix for a file that will not open properly for one particular person.

Why é becomes é

A text file is bytes. Nothing in it records which alphabet those bytes represent, so the reader has to guess, and when it guesses wrong you get mojibake: café rendered as café, or a bag of question marks where an emoji used to be.

The near universal answer is UTF-8, which covers every script and every emoji. The problem is that a spreadsheet opening a file by double click will often assume the old regional encoding instead, and UTF-8 accented characters happen to decode into plausible looking nonsense rather than an obvious error.

The pragmatic fix is a byte order mark: three invisible bytes at the very start of the file that say, unmistakably, this is UTF-8. Purists dislike it, because a byte order mark is meaningless for UTF-8 in principle and can confuse tools that read the first line raw. It is still the right trade, because it is the one thing that makes Excel get accents right on a plain double click.

If the accents are already gone, no encoding setting will bring them back. Either regenerate the file, or if the goal is only ASCII-safe output, flatten the characters deliberately with remove accents rather than leaving corrupted bytes behind.

Commas, quotes and line breaks inside fields

A field containing a comma must be wrapped in double quotes, so Smith, John becomes "Smith, John". Miss that and the row gains a column and every field after it shifts one place left, which is exactly the sort of error that survives all the way into a report.

A quote mark inside a quoted field is escaped by doubling it, not with a backslash. A 6" pipe is written "A 6"" pipe". This surprises people constantly, because almost every other format in a developer's life uses backslash escapes.

Line breaks are legal inside a quoted field, which is why a customer comment written across three lines is valid CSV and still breaks naive parsers that split on newlines first. If a file looks like it has more rows than records, this is usually why. A quick text diff against a known good export will show you where the row count went astray.

Line endings are the last quiet one. Windows ends lines with a carriage return and a line feed, everything else uses a line feed alone. A stray carriage return can end up as an invisible character glued to the last field of every row, so an exact match on a value fails for no visible reason. A pass through remove empty lines tidies the blank trailing rows that come with the same problem.

Treat CSV as transport, not storage

CSV is excellent at one job: moving a flat rectangle of values between two systems that both understand it. It is genuinely poor at being the place your data lives, because it records no types, no encoding, no nesting and no schema. Every one of those has to be reconstructed by guesswork at the other end.

The moment your data has real structure, a customer with several addresses, an order with line items, a field that is sometimes empty and sometimes a list, CSV starts lying about it. You end up with columns named item_1, item_2, item_3 and a hard ceiling at whatever number you chose.

That is the point to convert. CSV to JSON gives you actual types, real nesting and an unambiguous encoding, and JSON formatter will make the result readable enough to check by eye. When a person needs to read it rather than a machine, CSV to Markdown table produces something you can paste straight into a document.

And keep the conversion two way. Plenty of systems still only accept CSV, so generate it from your structured source with JSON to CSV when you need to hand a file over, and treat that CSV as a disposable export rather than the master copy.

Before you send the file

Save as UTF-8 with a byte order mark. Confirm the delimiter with whoever is receiving it, especially across borders. Quote any field containing a comma, a quote or a newline, and double the internal quotes. Keep identifiers, postcodes and phone numbers as text, and say so in the accompanying message.

Then open your own file the way the recipient will open it, by double clicking rather than through a careful import wizard. Almost every CSV disaster is discovered by the second person rather than the first, purely because the first one never looked at it the way the second one did.