Semicolon CSV Explained: Why European Excel Uses ;

A colleague in Berlin sends you export.csv. You open it and every row sits in column A, glued together with semicolons. Nothing is corrupted. A semicolon CSV is what Excel produces when the operating system's list separator isn't a comma — which is the default across most of Europe. This post explains the mechanism, and how to read those files reliably in Excel, Python, and JavaScript.

#TL;DR

• Excel writes CSV using the OS list separator, not a hard-coded comma.
• Locales with a decimal comma (de, fr, es, it, pl) get a semicolon list separator.
• RFC 4180 only defines the comma, so semicolon files are convention, not standard.
• In Excel use Data → From Text/CSV and pick the delimiter; don't double-click.
• Parsers sniff delimiters: Python's csv.Sniffer, Papa Parse's delimitersToGuess.

#Why does Excel save CSV with semicolons instead of commas

The short answer: Excel never decided to use a comma in the first place. It asks the operating system.

#The list separator is a Windows regional setting

On Windows there is a setting called List separator, found under Region settings → Additional settings → Numbers. Excel reads that value when it saves a workbook as CSV (Comma delimited) and when it opens a .csv by double-click. Microsoft's own guidance on importing and exporting text files documents this and warns that changing the value is "a global change on your computer, affecting all applications."

So the menu item still says "Comma delimited" while the file on disk contains semicolons. That mismatch is the single biggest source of confusion here — the UI label describes the format family, not the byte that actually separates fields.

#Why the decimal comma forces the change

You cannot use the comma for two jobs at once. In de-DE, fr-FR, es-ES, it-IT, pl-PL and dozens of other locales, the decimal separator is a comma: one and a half euros is 1,50 €. If the field separator were also a comma, this row would be ambiguous:

Widget,1,50,Berlin

Is that three fields or four? A parser has no way to know. Promoting the semicolon to field separator resolves the ambiguity cleanly, and the group separator convention (1.234,56 in German per CLDR locale data) stays intact.

#Why RFC 4180 does not help here

RFC 4180, the 2005 informational memo that registered the text/csv media type, is unambiguous: its ABNF grammar defines COMMA = %x2C and nothing else. There is no delimiter parameter in the media type registration — only charset and header.

But RFC 4180 is Informational, not a Standard, and it says so about itself: it documents "the format that seems to be followed by most implementations." It also notes that implementations should "be conservative in what you do, be liberal in what you accept from others." Semicolon files are the practical expression of that second half. Every serious parser accepts a configurable delimiter; none of them are wrong for doing so.

If you want the deeper tour of where the spec and reality part ways, we covered that in RFC 4180 Explained.

#How to open a semicolon CSV in Excel without breaking columns

There are three fixes, in ascending order of collateral damage.

#Use Data → From Text/CSV and pick the delimiter

This is the correct answer and it takes eight seconds:

1. Data tab → Get & Transform Data → From Text/CSV
2. Select the file, click Import
3. In the preview dialog, set Delimiter to Semicolon
4. Load

The preview redraws immediately, so you see the columns split before committing. Under the hood this is Power Query, whose Csv.Document function takes an explicit delimiter argument that defaults to "," — the delimiter is data, not a guess.

LibreOffice Calc behaves better by default: its Text Import dialog shows Tab / Semicolon / Comma / Space checkboxes every time you open a CSV, with a live preview. You can tick more than one.

#The sep=; first-line trick and what it costs you

Excel honours a magic first line that names the delimiter:

sep=;
name;price;city
Widget;1,50;Berlin

Excel consumes that line and hides it from the sheet. Double-clicking now works. The cost is that sep=; is an Excel convention with no basis in RFC 4180, so a strict parser sees a bogus one-column header row. Use it only for files whose sole destination is Excel — never for an API payload or a file another system ingests. If a partner requires it, generate the file without it and prepend the line as a separate, clearly-named export.

#Change Excel's separators without touching Windows settings

If you must make Excel write semicolons on a US-configured machine, Microsoft documents a locale-free workaround: clear Excel Options → Advanced → Editing options → Use system separators, set the decimal separator to , and the thousands separator to .. Excel then falls back to ; for the list separator. It is scoped to Excel rather than the whole OS — but it also changes how every number in every workbook displays, which is rarely worth it.

#How to detect the delimiter in code

Never hard-code a comma when the file comes from a spreadsheet you don't control. Sniff it.

#Python: csv.Sniffer and the preferred tie-break list

The standard library ships a detector. Per the Python csv documentation, Sniffer.sniff() analyses a sample and returns a Dialect; when several delimiters split every row equally well, it breaks the tie using the preferred attribute, whose initial value is [',', '\t', ';', ' ', ':'].

import csv

with open("export.csv", newline="", encoding="utf-8-sig") as f:
    sample = f.read(4096)
    dialect = csv.Sniffer().sniff(sample, delimiters=",;\t|")
    f.seek(0)
    for row in csv.reader(f, dialect):
        print(row)

Two details worth copying. Passing an explicit delimiters string stops the sniffer wandering off into spaces and colons. And encoding="utf-8-sig" strips a UTF-8 byte order mark if Excel wrote one — a separate failure mode we unpack in Why Your CSV Is Garbled in Excel.

Note that the comma outranks the semicolon in preferred. On a German file where both characters happen to split rows consistently, the sniffer will pick the comma and hand you nonsense. Constrain the candidate set.

#JavaScript: Papa Parse delimitersToGuess

Papa Parse auto-detects when you leave delimiter empty. Its documentation lists the default candidates as [',', '\t', '|', ';', Papa.RECORD_SEP, Papa.UNIT_SEP], and the delimiter it settled on comes back in results.meta.delimiter:

Papa.parse(file, {
  header: true,
  skipEmptyLines: true,
  delimitersToGuess: [";", ",", "\t"],
  complete: (res) => {
    console.log(res.meta.delimiter); // ";"
    console.log(res.data[0]);
  },
});

Always log meta.delimiter in an ingest pipeline. A silent wrong guess produces one wide column that looks like valid data until it reaches a database.

#A quick heuristic you can write yourself

If you'd rather not add a dependency, count candidates on the header line and pick the one with the most consistent count across the first few rows:

function detectDelimiter(text, candidates = [",", ";", "\t", "|"]) {
  const lines = text.split(/\r?\n/).filter(Boolean).slice(0, 5);
  let best = ",", bestScore = -1;
  for (const d of candidates) {
    const counts = lines.map((l) => l.split(d).length - 1);
    if (counts[0] === 0) continue;
    const consistent = counts.every((c) => c === counts[0]);
    const score = consistent ? counts[0] : 0;
    if (score > bestScore) { bestScore = score; best = d; }
  }
  return best;
}

This is deliberately naive — it ignores quoting, so a semicolon inside "Berlin; Mitte" will skew the count. Good enough to route a file, not good enough to parse it.

#Semicolon CSV in pipelines: what actually breaks

Getting the delimiter right is step one. Two more things follow it downstream.

#Decimal commas turn numbers into strings

A semicolon file almost always carries decimal commas, because they arrive from the same locale. Type coercion then silently fails:

name;price;qty
Widget;1,50;3
Gadget;12,00;1

Parsed with default settings, price is the string "1,50". Papa Parse's own docs flag this: with dynamicTyping enabled, "European-formatted numbers must have commas and dots swapped." Normalise explicitly at parse time rather than hoping parseFloat does something sensible — it returns 1 for "1,50", which is worse than an error because it looks fine.

#Quoting rules do not change, only the delimiter

This part is reassuring. RFC 4180's quoting rules are orthogonal to the separator: fields containing the delimiter, a double quote, or a line break get wrapped in double quotes, and an embedded quote is escaped by doubling it. A semicolon file follows the same rules with ; substituted for ,:

name;note
Widget;"Berlin; Mitte"
Gadget;"He said ""ja"""

So you do not need a different parser. You need the same parser with one option changed.

#Convert once at the boundary

The cleanest architecture is to stop caring about delimiters after ingestion. Convert to JSON at the edge, validate the shape, and let every downstream service consume a single format. The CSV ↔ JSON Converter does the delimiter detection and the conversion in the browser — the file never leaves your machine, which matters when the CSV is a customer list. From there, JSON Decoder will validate and pretty-print the result, and if you're loading the data into a database, SQL Converter handles the other direction.

One last practical note: when you send a CSV to someone in a different locale, say which delimiter you used. One line in the email prevents the whole round trip. Better still, send JSON.

#References

• RFC 4180 — Common Format and MIME Type for Comma-Separated Values (CSV) Files — ABNF grammar, the COMMA = %x2C definition, quoting rules, and the "be liberal in what you accept" interoperability note.
• Import or export text (.txt or .csv) files | Microsoft Support — list separator behaviour, the From Text/CSV delimiter picker, and the Excel-scoped separator workaround.
• Csv.Document - PowerQuery M — confirms Power Query's explicit delimiter parameter and its "," default.
• csv — CSV File Reading and Writing (Python 3 documentation) — Sniffer.sniff(), the preferred tie-break list, and dialect parameters.
• Documentation - Papa Parse — delimitersToGuess defaults, meta.delimiter, and the European-number caveat on dynamicTyping.
• Text Import — LibreOffice Help — Calc's separator checkboxes and locale-driven number detection.

#Related on iKit

• RFC 4180 Explained: The CSV Edge Cases Tools Get Wrong — the spec this article leans on, with the quoting and line-break cases that bite after you've fixed the delimiter.
• Why Your CSV Has Garbled Chinese or Japanese in Excel — and How BOM Fixes It — the other locale bug that hits the same files, this time in the encoding layer.
• CSV to JSON: The Complete 2026 Guide for Developers — the full conversion workflow once you know which delimiter you're dealing with.
• JSON to CSV: How to Flatten Nested Objects Without Breaking the Data — the return trip, including how to choose an output delimiter your recipient can open.
• Excel to JSON in the Browser: No Server Needed — skip CSV entirely by reading the .xlsx directly, which sidesteps the list-separator problem at the source.