Why your CSV opens wrong in Excel

Excel does not look inside a .csv file to work out how it is separated. When you double click one, it uses the list separator from your operating system's regional settings, and if that does not match the file, every row lands in a single column.

In the UK and the US the list separator is a comma. Across most of continental Europe and much of Latin America, where the decimal point is written as a comma, the list separator is a semicolon. So a valid comma separated file mailed from London opens as one squashed column in Amsterdam, Berlin or Madrid, and a semicolon file does the same in reverse. Nothing is corrupted; the file is being read with the wrong assumption.

Three ways to open it correctly

1. Import instead of opening. In Excel, Data, then Get Data or From Text/CSV, and pick the delimiter and encoding in the dialogue. This is the only route that gives you full control, and the only one that survives being sent to another country.
2. Rename the file to .txt. Excel refuses to guess for a .txt file and shows the import wizard instead, which asks you what the separator is.
3. Convert the file to the separator your reader expects before you send it. If you know the recipient is on a semicolon locale, send them a semicolon file.

The sep= line

Excel and LibreOffice both understand a special first line that overrides the locale setting:

sep=;
name;email;country
Ada;ada@example.com;UK

It works, and it is a common fix for "the client cannot open my export". Be aware that it is a spreadsheet convention, not part of any CSV specification. Feed the same file to a script or a database import and sep=; is read as an ordinary first row, usually becoming a bogus column header. Use it for files destined for a human with a spreadsheet, not for machine-to-machine exchange.

Quoting and escaping

The informal reference for CSV is RFC 4180, which describes common practice rather than dictating it. Its rules are short:

• A field must be wrapped in double quotes if it contains the separator, a double quote, or a line break.
• A double quote inside a quoted field is written twice.

id,name,note
1,"Smith, Ada","She said ""hello"" twice"
2,"Multi
line note",fine

Row 1 has three fields, not four, because the comma sits inside quotes. The doubled quotes collapse to single ones when parsed. Row 2 contains a real line break inside a quoted field, which is legal and is why you cannot reliably split a CSV by counting lines.

Backslash escaping (\, or \") is a different convention used by some database tools. It is not RFC 4180 and a strict parser will not understand it, so if your file uses backslashes, say so explicitly to whatever reads it.

The most common breakage in the wild is a file written by string concatenation, where a value containing a comma or a stray quote was never quoted at all. That produces rows with more fields than the header, which is exactly the sort of thing a validator will point at.

The BOM, and why accented characters turn into mojibake

A byte order mark (BOM) is three invisible bytes at the very start of a file that mark it as UTF-8. Excel on Windows has historically relied on it: without a BOM, it may fall back to a legacy regional encoding, so Müller shows up as Müller and café as café.

The trade-off is that other parsers do not always strip it. If your script reports a first column named \ufeffid rather than id, or a lookup on "id" mysteriously fails on the first column only, a BOM is the culprit. Read the file with an encoding of utf-8-sig and it goes away.

Rule of thumb: include the BOM in files meant to be opened in Excel, leave it out of files meant to be parsed by software.

Line endings

Windows tools write \r\n at the end of each row, Unix tools write \n. Most parsers cope with both, but a naive split on \n leaves a trailing carriage return glued to the last field of every row, which is why a value that looks like UK refuses to equal "UK" in comparisons.

How to tell which separator a file actually uses

Open the file in a plain text editor, never in a spreadsheet, because the spreadsheet has already made its guess. Then:

1. Look at the first two or three lines. Which candidate character (comma, semicolon, tab, pipe) appears between what are clearly field values?
2. Count each candidate on several lines. The real separator gives the same count on every line, one less than the number of columns. A character that appears seven times on one line and two on the next is data, not structure.
3. Do that counting outside quoted sections. In "Smith, Ada" the comma is content, and counting it naively is what makes commas look inconsistent in a file that is genuinely comma separated.
4. Watch for decimal commas. A European file often has both semicolon separators and commas inside numbers, as in Ada;1.234,56;UK. If you see commas only ever between digits, they are decimal points, not separators.
5. Check the first bytes for a sep= line or a BOM before you conclude anything.

Tabs are worth a special mention. A tab separated file is generally the safest thing to hand a spreadsheet, because tabs almost never appear inside real values and Excel handles them well through the import route. The catch is that tabs are invisible, so a file where someone has aligned columns with spaces looks identical and parses to nothing useful.

Once you know the separator, converting the file is mechanical: read it with the correct delimiter and quoting, then write it out with the one your destination expects, or convert it to JSON and skip the delimiter question entirely.