Why Your CSV Opens in One Column in Excel (and How to Fix It)

A colleague in Berlin sends you an export, you double-click it, and Excel shows every row crammed into column A: name;city;price. Or it goes the other way: your comma-separated file lands in one column on their machine. Nothing is corrupted. Excel doesn’t split a CSV on commas. It splits on your operating system’s list separator, and in most locales that write decimals with a comma, that separator is a semicolon. The file is fine. The two machines just disagree about what separates a field. This post covers where that setting comes from, the workarounds (sep=, TSV, BOMs), and how delimiter auto-detection works, using real output from the parser behind our CSV delimiter converter and CSV viewer.

The separator comes from your locale, not the file

A CSV file has no header that declares its delimiter. The reader has to decide, and Excel decides with a system setting. Microsoft’s Import or export text (.txt or .csv) files page says it directly: “When Excel opens a .csv file, it uses the current default data format settings to interpret how to import each column of data.” To change the separator for all .csv files, the same page sends you to Windows Region settings, where you type a character into the List separator box. It also gives an Excel-only workaround: set the decimal separator to a comma, and that “forces Excel to use a semi-colon for the list separator.”

That last point is the whole story. If the comma is already your decimal mark, it can’t also separate fields, because 1,50 would split into two cells. Here’s how 1234.5 is formatted in a few locales, using the CLDR data built into Node’s Intl.NumberFormat:

Locale	1234.5 formatted	Decimal mark
en-US, en-GB, ja-JP	1,234.5	.
de-DE, nl-NL, pt-BR	1.234,5	,
fr-FR, ru-RU, sv-SE	1 234,5	,
es-ES, it-IT, pl-PL	1234,5	,
de-CH	1’234.5	.

Everyone in the comma-decimal rows is a candidate for semicolon CSV. Note de-CH: it follows the locale, not the language. Either way the symptom is the same: Excel doesn’t find its list separator and puts the whole line in column A.

What the spec says, and where Excel departs from it

RFC 4180 is the closest thing CSV has to a spec. It’s an Informational memo that “does not specify an Internet standard of any kind.” Its grammar hard-codes the comma (record = field *(COMMA field)) and CRLF line breaks. The core rules:

1. Each record is on its own line, delimited by CRLF. The last record may or may not end with a line break.
2. An optional header line has the same format as the records.
3. “Spaces are considered part of a field and should not be ignored.”
4. Fields containing line breaks, double quotes, or commas should be enclosed in double quotes.
5. A double quote inside a quoted field is escaped by doubling it: "said ""hi""".

Excel’s semicolon output is already outside this grammar, and the RFC itself names Excel as an outlier. Its own rule 5 in section 2 reads: “Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all).” In practice “CSV” is a family of dialects, and any reader has to work out which one it’s holding.

sep=: the Excel-only hint

A common workaround is to make the first line of the file declare the separator:

sep=;
name;city
Alice;Paris

Excel reads the first line as an instruction rather than data. Microsoft doesn’t document this on the import/export page above, so it’s folklore rather than a feature. Treat it as fragile. On Microsoft’s own Q&A forum, one user reports that adding sep=; to a UTF-8 file with a BOM made Excel stop displaying Chinese and Japanese characters correctly, and removing the line fixed it. Another user reports the same thing with German characters.

The bigger problem is that every non-Excel reader sees sep=; as a data row. Our parser does too, and it doesn’t treat the line specially. Here’s the file above in our viewer’s buildTable:

headers: ["sep=", "Column 2"]
rows:    [["name","city"], ["Alice","Paris"]]

The hint line became the header, and your real header became the first data row. If a file starts with sep=, delete that line before loading it anywhere other than Excel. You can also untick “First row is header” in the viewer, which at least keeps the real header visible as a data row. (A happy accident in the converter: the line parses as sep= plus an empty field, so converting to comma rewrites it as sep=,.)

The encoding half of the problem: BOMs

Getting the right number of columns is half the fix. The other half is when Käse shows up as Käse, which means the file was UTF-8 but was read as a legacy single-byte encoding. A plain text file has nowhere to say which encoding it uses. The Unicode BOM FAQ describes the only in-band signal: the byte-order mark U+FEFF, which in UTF-8 is the three bytes EF BB BF. It says a UTF-8 BOM “is only used as a signature — an indication that an otherwise unmarked text file is in UTF-8.”

That signature is a trade-off. The same FAQ warns that a BOM “will interfere with any protocol or file format that expects specific ASCII characters at the beginning.” In CSV terms, a script that doesn’t strip it reads your first column name as name instead of name, and every lookup by that name fails. So it’s worth adding a BOM for a human opening the file in a spreadsheet, and worth stripping it for a pipeline.

Our converter has a BOM setting with three modes: Keep if present, Strip, and Add. Uploads need one detail to get right: the browser’s File.text() runs the UTF-8 decode algorithm, and that algorithm silently removes a leading BOM. We checked this in Node: a Blob of EF BB BF 61 2C 62 comes back as the 3-character string a,b. So both CSV tools read the upload’s raw bytes and decode them with TextDecoder('utf-8', { ignoreBOM: true }), which keeps the mark. An uploaded file with a BOM shows “input had a BOM” in the converter’s status line, and Keep if present writes it back out. We still always read input as UTF-8, the same as Blob.text(), which “always uses UTF-8”. An export saved in a legacy Windows code page will show replacement characters where accented letters were, so re-save it as UTF-8 first.

Line endings: CRLF, LF, and mixed files

RFC 4180 specifies CRLF, and most modern readers accept LF too. The converter lets you pick either: LF (Mac/Linux) or CRLF (Windows/Excel). The subtle failure is a mixed file, where rows were appended by different tools. Papa Parse, the parser we use, picks one newline sequence for the whole input. With mixed endings, the minority breaks would end up glued inside field values. So our parseCsv checks for mixed endings outside quoted fields and normalises them. On a,b\r\n1,2\n3,4\r\n it reports:

The input mixes Windows (CRLF) and Unix (LF) line endings — all line breaks
were normalized, including any inside quoted fields.

Only breaks outside quotes count: CRLF rows with bare LF inside a quoted multi-line cell are not a mixed file.

How delimiter auto-detection actually works

Both tools default to auto-detect, and the method is simple enough to describe exactly. Our detectDelimiter (in src/lib/csv-delimiter.ts) strips any BOM, takes the first 64 KB (cut back to the last full line so a half row doesn’t skew the result), and hands it to Papa Parse’s sniffer. Papa’s docs say that leaving delimiter blank auto-detects from [',', '\t', '|', ';', Papa.RECORD_SEP, Papa.UNIT_SEP]. From the guessDelimiter source (papaparse 5.5.3), for each candidate in that order Papa:

1. Parses the first 10 rows with that delimiter.
2. Adds up how much the field count changes from one row to the next (delta) and works out the average field count.
3. Makes the candidate the new best only if its delta is less than or equal to the best delta so far, its average is strictly greater than the best average so far, and its average is above 1.99.

A few behaviours follow from rule 3, and they’re worth knowing when detection surprises you:

• Ties go to the comma. A candidate needs a strictly higher average to replace the current best. On a one-line input like a,b;c,d, comma gives 3 fields and semicolon gives 2, so comma wins. On a,b;c both give 2, and comma, tested first, keeps the win.
• One column means no detection. If no candidate averages two fields, Papa reports UndetectableDelimiter. We then fall back to comma and the converter warns: “Could not detect a delimiter — treated the input as comma-separated (or a single column). Pick the input delimiter manually if that is wrong.” The same happens if the first ten rows are single-column, even when delimited rows come later.
• A header row protects European files. On sku;price;tax / A1;12,50;2,38 / B2;3,99;0,76, the comma-split header has 1 field and the data rows have 3, so comma’s delta is high, and semicolon (3 fields every row, delta 0) wins. Result: {"delimiter":";","detected":true}.
• Headerless decimal-comma data fools it. On 12,50;2,38;14,88 / 3,99;0,76;4,75, the comma gives a perfectly consistent 4 fields per row and the semicolon gives 3. Both have delta 0, the comma average is higher, and the comma wins, which is wrong. Detection reports success anyway (detected: true), so no warning appears.

That’s why both tools show which delimiter they used and let you override it with Comma, Semicolon, Tab, Pipe, or a custom string, including multi-character separators like ::.

What our converter does with your file

The CSV delimiter converter fully parses the input and then re-serialises it, so quoting is recomputed for the new delimiter rather than swapped character by character. Real output from convertDelimiter, comma to semicolon:

in:  name,price,note
     "Smith, John",1.50,"said ""hi"""
out: name;price;note
     Smith, John;1.50;"said ""hi"""

Smith, John lost its quotes because it no longer contains the delimiter. The embedded quotes are still doubled. Going the other way, German Käse;1,50 to comma gives Käse,"1,50": the decimal comma now needs quoting. The converter doesn’t rewrite decimal marks. 1.50 stays 1.50 in semicolon output, so a comma-decimal Excel may not read it as the number you meant. It changes structure, not the data.

The output options:

Option	What it does
Output quote	" (default) or '. Embedded quote characters are doubled.
Line endings	LF or CRLF. A trailing newline is kept if the input had one.
BOM	Keep if present / Strip / Add (see the BOM section above).
Quote all fields	Wraps every value, e.g. "name";"price", for importers that expect it.

Without “Quote all”, a field is quoted only if it contains the delimiter, the quote character, a line break, or leading/trailing whitespace. Choosing Tab as the output saves the download as .tsv (export.csv becomes export.tsv). Files up to 50 MB are accepted, with a warning above 20 MB, and the on-screen preview shows the first 100,000 characters while Copy and Download give you everything. It all runs in your browser. Nothing is uploaded.

Malformed input gets flagged rather than silently accepted. An opening quote that never closes throws “Unterminated quoted field starting at row 2 — an opening quote is never closed.” Rows with the wrong number of fields are reported against the most common width: on a,b,c / 1,2,3 / 4,5 / 6,7,8,9 the message is “2 rows have a different number of fields than the usual 3 (row 3 has 2, row 4 has 4).”

TSV: the escape hatch

If you control the export, tab-separated values sidestep the locale fight: no locale uses a tab as a decimal mark, and tabs rarely appear in data, so almost nothing needs quoting. Converting a,b / "x, y",z to Tab output gives x, y<TAB>z with no quotes at all.

Just looking at the data: the viewer

To just inspect a file without Excel’s locale guessing, the CSV viewer uses the same parser and detection, pads short rows with empty cells (and lists the rows whose field count differs from the rest), and shows 50, 100, or 500 rows per page. The file never leaves your browser. Its sorting is worth understanding:

• A column counts as numeric only if every non-empty cell parses as a number. The pattern accepts -3, .5, 1e6, 007, and comma thousands groups like 1,234.5.
• Decimal commas aren’t numbers to the viewer. 1,5, 1.234,5, $5, and 12% all return null from parseNumber, so a European price column sorts as text. The natural collation still orders 1,5 before 10,25, but you won’t get min/max/mean stats for it.
• Blank cells sink to the bottom in both directions. Sorting 9, 10, (blank), 1e2 ascending gives 9, 10, 1e2, (blank).
• Text uses a natural, case-insensitive order: file1, File2, file10.

Checklist when a CSV lands in one column

1. Look at the raw text. Is the separator ,, ;, or a tab? Compare it to your system’s list separator.
2. If they differ, convert the delimiter with the delimiter converter, or import through Excel’s Get & Transform Data → From Text/CSV, where Microsoft’s docs say you can choose the delimiter and preview the effect immediately.
3. If accented characters are garbled, the problem is encoding, not delimiters. Make sure the file is UTF-8, and choose Add for the BOM if Excel is the reader.
4. If the file starts with sep=, remove that line before any non-Excel tool reads it.
5. Headerless numbers with decimal commas? Don’t trust auto-detect. Set the input delimiter by hand.

Once the columns are right, CSV ⇄ JSON and CSV to Markdown table both let you pick the delimiter explicitly. For the related Excel damage that happens after the columns split correctly (dates becoming 45292, leading zeros vanishing), see why Excel dates and IDs break on export. For the characters that break Markdown tables specifically, see CSV to Markdown table pitfalls.