HowToCodePage

CSV in depth: quoting, delimiters and Excel traps

RFC 4180 in practice — quoted fields, doubled quotes, embedded newlines — plus the BOM, delimiter and locale quirks that break real spreadsheet exports.

What the format actually allows

CSV looks trivial until a value contains a comma. The informal standard is RFC 4180, and almost every bug comes from ignoring one of its four rules.

  • Records are separated by CRLF (\r\n), though most parsers also accept a bare LF.
  • Fields are separated by a comma, but any field may be enclosed in double quotes.
  • Inside a quoted field, a literal double quote is written twice: "".
  • A quoted field may contain commas, newlines and quotes — so a record is not the same thing as a line.
name,note,price
Widget,"ships in 2-3 days",9.99
Gadget,"he said ""hello""",19.99
Cable,"multi-line
description",4.50

That third record spans two physical lines. Any reader that splits on \n first and parses second will corrupt the file.

The Excel traps

SymptomCauseFix
First header cell shows as \ufeffidExcel writes a UTF-8 BOMStrip the BOM on read, or open with encoding='utf-8-sig'
Leading zeros lost (007 becomes 7)Spreadsheet infers a numberQuote the field in the file; treat all input as text on import
Semicolons instead of commasLocale list separator (de, fr, es)Sniff the delimiter, or force sep=; on the first line
Long numbers become 1.23E+15Double precision inferenceStore IDs as strings, never as CSV numbers
Dates reorder (03/04 ambiguity)Locale date parsingUse ISO 8601 YYYY-MM-DD in exports

A leading sep=, line is a Microsoft extension understood by Excel but rejected by strict parsers, so it is a one-way door: use it only for human-facing exports.

Parsing and writing safely

import csv

with open("data.csv", newline="", encoding="utf-8-sig") as f:
    reader = csv.reader(f, dialect="excel")
    rows = [r for r in reader]

# writing: the writer quotes only when it must
with open("out.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.writer(f, quoting=csv.QUOTE_MINIMAL, lineterminator="\r\n")
    w.writerow(["id", "note"])
    w.writerow(["1", 'he said "hi", loudly'])

Two arguments are easy to forget: newline="" stops Python from translating line endings and producing \r\r\n, and encoding="utf-8-sig" transparently eats the BOM that Excel writes.

⚠️
Never build a CSV line with string concatenation. A single unescaped comma or newline silently shifts every later column, and the corruption is invisible until someone sums the wrong field.

FAQ

Should I quote every field?
It is valid and removes ambiguity, but it increases size and makes diffs noisy. Quote minimally on write and accept both forms on read.
How do I detect the delimiter?
Count candidate delimiters outside quotes on the first few lines and pick the most consistent one. Python's csv.Sniffer or a small heuristic both work; never assume a comma.

Choosing a format: size, speed, readability and tooling Converting between formats with jq, yq and csvkit

Last refreshed 2026-09-18.