Comma, semicolon, tab: the CSV delimiter mess, explained

“Comma-separated values” sounds like a definition. In practice it’s a suggestion. Files called .csv arrive separated by commas, semicolons, tabs and pipes — and the mismatches produce the two classic failures: a file that opens as one giant column, and a file that shatters into too many columns halfway down. Both are delimiter problems, and both are fixable in seconds once you understand why they happen.

The one standard CSV has (and what it actually says)

CSV grew up wild in the 1970s–90s; the closest thing to a rulebook, RFC 4180, wasn’t written until 2005, and it describes rather than commands. Its rules are worth knowing because virtually every parser follows them:

Fields are separated by commas; rows end with a line break.
A field containing commas, quotes or line breaks must be wrapped in double quotes.
A literal double quote inside a quoted field is doubled: she said ""hi"".

Two consequences surprise people. First, a single CSV row can span multiple lines — addresses and comments with embedded newlines are legal, which is why line-counting a CSV lies and why naive split-by-newline scripts corrupt files. Second, quoting is conditional — most fields aren’t quoted, so a parser can’t just split on every comma it sees. (“Smith, John” taught everyone that lesson at least once.)

Why Europe’s CSVs use semicolons

Here’s the part that looks insane until you see the reason. In most of continental Europe, the decimal separator is a comma: π is 3,14 and a price is 1.234,56. A comma-separated file of comma-decimal numbers would be unparseable — so Windows regional settings in those locales define the list separator as a semicolon, and Excel (which delegates its CSV behavior to that setting) both writes and expects semicolon-“CSV”.

Nobody is wrong. A German colleague’s Excel produces name;price with 1,99 inside — fully consistent with their locale — and your US-locale Excel, expecting commas, opens it as one column per row. The reverse trip fails symmetrically. The file didn’t break; two regional conventions met in an under-specified format.

The fixes, most-practical first:

Re-delimit the file itself — parse with the delimiter it has, re-serialize with the one you need, quoting recalculated. The delimiter changer does exactly this locally; it’s the fix that doesn’t touch anyone’s system settings.
Import instead of double-clicking — Excel’s Data → From Text/CSV asks about the delimiter and previews the result.
Change the Windows list separator — works, but it also changes the argument separator in every Excel formula on that machine (=SUM(A1;B1) style), which surprises people for months. Last resort.

Tabs: the delimiter that dodges the problem

Tab-separated values (TSV) sidestep both the decimal-comma clash and most quoting, because real-world data almost never contains tabs. That’s why databases (Postgres’ COPY), bioinformatics pipelines, and spreadsheet clipboards — copied cells paste as TSV — favor tabs. The cost is that tabs are invisible: in an editor, a broken TSV looks fine, and a “TSV” that someone’s editor silently converted to spaces is a file that no longer has a delimiter at all. If you work with TSVs, look at them in a table view, not a text editor.

Pipes (|) are the pragmatic fourth option — ugly, rare in data, and effective when a file is full of both commas and tabs. You’ll meet them in exports from older enterprise systems.

Diagnosing a delimiter problem in 10 seconds

Open the file in any table viewer with delimiter detection and look at the shape:

One wide column, every row: the parser used the wrong delimiter entirely. Semicolon file read as comma, usually.
Correct until one row explodes into extra columns: unquoted delimiter inside data — someone concatenated “Acme, Inc.” into a comma file without quoting. The row number tells you where; the fix belongs at the source that wrote the file.
Columns shift right partway through a row: an unescaped quote opened a quoted section that swallowed the following commas. Also a producer-side bug — a tolerant parser can usually still show you the damage so you can find it.
Numbers look like 1,99 and there are semicolons everywhere: European export, perfectly healthy — parse with semicolons, and convert decimal commas only if the destination needs dot-decimals (find & replace with a column scope does it safely).

Auto-detection — picking the candidate delimiter that yields the most consistent column count across sample rows, ignoring characters inside quotes — gets this right nearly always, which is why the viewer here sniffs commas, semicolons, tabs and pipes and then shows you the grid as proof. When it’s unsure (single-column files are genuinely ambiguous) the toolbar override is one click.

Producing files that don’t cause this

If you control the export, three habits end delimiter pain for everyone downstream:

Quote defensively: quote any field containing the delimiter, quotes or newlines (that’s just RFC 4180), and double embedded quotes.
Say what you wrote: UTF-8, comma-separated, header row — one line in the docs or the email. The filename helps too: orders-2026-07.semicolon.csv has saved real hours.
For machine-to-machine data, prefer tabs (or better, a typed format) when fields are full of prose commas.

The delimiter mess is fifty years of pragmatic local decisions meeting each other in attachments. It isn’t going away — but with a parser that detects honestly and a re-delimiter within reach, it stops costing you more than a minute.