CSV pitfalls: quoting, delimiters and encodings

CSV is a convention, not a standard

RFC 4180 documents CSV and registers text/csv, but it is an informational RFC written in 2005 to describe existing practice, not to bind anyone. PostgreSQL's own COPY documentation says it plainly: "Many programs produce strange and occasionally perverse CSV files, so the file format is more a convention than a standard."

What RFC 4180 actually specifies is short: records separated by CRLF, an optional header row, the same number of fields on every row, fields containing commas, double quotes or line breaks enclosed in double quotes, and a literal double quote inside a quoted field written as two double quotes. It says nothing about other delimiters, nothing about types, and next to nothing about encodings. Almost every real-world CSV bug lives in one of those gaps.

Quoting and embedded newlines

Here is a file that follows the RFC, with a doubled quote, an embedded comma and an embedded newline, parsed with Python's csv module (all output in this guide came from Python 3, pandas 3.0.3 and Node 24):

import csv, io
raw = 'id,note\r\n1,"said ""hi"", then left"\r\n2,"line one\nline two"\r\n'
for row in csv.reader(io.StringIO(raw, newline='')):
    print(row)

['id', 'note']
['1', 'said "hi", then left']
['2', 'line one\nline two']

Two physical lines became one record, which is correct, and it is exactly what breaks grep, wc -l, head and any "split on \n" parser. If your pipeline counts records with wc -l, it is wrong for any file that has multiline fields.

Writing is the mirror image. csv.writer quotes only when it has to:

buf = io.StringIO()
csv.writer(buf).writerow(['a,b', 'say "x"', 'multi\nline', 'plain', ''])
repr(buf.getvalue())
# '"a,b","say ""x""","multi\nline",plain,\r\n'

Note the \r\n terminator: Python follows the RFC here and writes it even on Linux. When you open a file for the csv module, pass newline=''; otherwise text-mode newline translation can corrupt embedded newlines.

Parsers differ most on malformed input. An unclosed quote is silently swallowed by default and only an error in strict mode:

list(csv.reader(io.StringIO('a,"b\n1,2\n')))
# [['a', 'b\n1,2\n']]          <- the rest of the file became one field
list(csv.reader(io.StringIO('a,"b\n1,2\n'), strict=True))
# _csv.Error: unexpected end of data

A stray quote in the middle of an unquoted field is another divergence. Python keeps it literally (a,b"c,d gives ['a', 'b"c', 'd']), while RFC 4180 calls it invalid and stricter parsers reject it. Never assume your parser's leniency matches the consumer's.

NULL, empty and ragged rows

CSV has no null. PostgreSQL COPY ... CSV adds one by convention: the default NULL string is an unquoted empty string, and an empty string value is written as "". So a,,b is NULL in the middle and a,"",b is an empty string. Python's csv module cannot tell them apart; both come back as ''. pandas goes the other way and converts a long default list of strings to NaN:

pd.read_csv(io.StringIO('n,v\nNA,null\nNone,\nnan,x\n')).to_dict('list')
# {'n': [nan, nan, nan], 'v': [nan, nan, 'x']}
pd.read_csv(io.StringIO('n,v\nNA,null\nNone,\nnan,x\n'), keep_default_na=False).to_dict('list')
# {'n': ['NA', 'None', 'nan'], 'v': ['null', '', 'x']}

The ISO country code for Namibia is NA. A column of country codes read with pandas defaults silently loses Namibia.

Ragged rows are handled three different ways. Python's csv returns whatever it finds ([['a','b'],['1'],['1','2','3']]) and a blank line becomes []. pandas raises ParserError: Expected 2 fields in line 3, saw 4 when a row is longer than the header, but when every data row has the same extra field it quietly treats the leading column as an index:

df = pd.read_csv(io.StringIO('a,b\n1,2,3\n4,5,6\n'))
df.to_dict('list')   # {'a': [2, 5], 'b': [3, 6]}   <- columns shifted left
df.index.tolist()    # [1, 4]

A trailing comma on every data row but not on the header produces exactly this.

Delimiters: the semicolon locales

Many locales (much of continental Europe, Brazil and others) use the comma as the decimal separator, so 1,50 is a price. Spreadsheet software there exports with a semicolon as the list separator, and Excel reads the delimiter it expects from the operating system's regional "list separator" setting rather than from the file. The same bytes parse differently depending on whose machine opens them:

semi = 'name;price\nWidget;1,50\n'
list(csv.reader(io.StringIO(semi)))
# [['name;price'], ['Widget;1', '50']]          <- comma assumed
list(csv.reader(io.StringIO(semi), delimiter=';'))
# [['name', 'price'], ['Widget', '1,50']]
csv.Sniffer().sniff(semi).delimiter   # ';'

csv.Sniffer guesses well on clean data and badly on short or irregular files, so treat it as a fallback, not a spec. In pandas, pass sep=';', decimal=',' and you get price: [1.5]. For files you produce, pick one convention and document it; if your users open them in Excel, either expect regional settings to interfere or offer a tab-separated or XLSX export.

Encodings and the BOM

RFC 4180 defines no encoding for the file contents beyond the registered text/csv type's optional charset parameter, and in practice you will meet UTF-8, UTF-8 with a BOM, Windows-1252 and, from some Excel exports, UTF-16. Guessing wrong produces classic mojibake:

s = 'Zoë Müller'
ascii(s.encode('utf-8').decode('cp1252'))   # 'Zo\xc3\xab M\xc3\xbcller'  i.e. "Zoë Müller"
s.encode('cp1252').decode('utf-8')
# UnicodeDecodeError: 'utf-8' codec can't decode byte 0xeb in position 2: invalid continuation byte

The first direction fails silently (nearly every byte is valid in cp1252); the second fails loudly. That asymmetry is why "UTF-8 read as Windows-1252" is the mojibake you see in the wild.

The BOM is three bytes, EF BB BF, that Microsoft tools use to mark a file as UTF-8. Excel on Windows generally needs it to open a double-clicked UTF-8 CSV as UTF-8 rather than the legacy code page. Most other tools do not expect it, and Python's plain utf-8 codec keeps it as data:

data = 'id,name\n1,Zoë\n'.encode('utf-8-sig')
data[:5]                                                # b'\xef\xbb\xbfid'
next(csv.reader(io.StringIO(data.decode('utf-8'))))     # ['id', 'name']
next(csv.reader(io.StringIO(data.decode('utf-8-sig')))) # ['id', 'name']

That 'id' header is why row['id'] raises KeyError on a file that visibly has an id column. pandas handled the same file correctly in my test (read_csv with encoding='utf-8' returned ['id', 'name']). PostgreSQL's COPY documentation says nothing about BOMs, so strip one before loading. Set the encoding explicitly, with ENCODING in COPY or encoding= in pandas, instead of relying on defaults.

Numbers that lose their leading zeros

CSV stores text; the type is inferred by the reader. Python's csv module never converts anything, so '01234' stays '01234'. Inference is where the damage happens:

raw = 'zip,qty,id\n01234,007,12345678901234567890\n90210,1,2\n'
pd.read_csv(io.StringIO(raw)).zip.tolist()             # [1234, 90210]
pd.read_csv(io.StringIO(raw), dtype=str).zip.tolist()  # ['01234', '90210']

US ZIP codes, phone numbers, product codes and account numbers are identifiers, not numbers, so read them as strings (dtype=str, or a per-column mapping). Excel does the same trimming when it opens a CSV, displays long integers in scientific notation and, for integers over 15 digits, zeroes the digits past the 15th because it stores numbers as doubles. It also converts date-like values, which has famously mangled gene names such as SEPT1 and MARCH1. The file was fine; the damage happens on open and again on save. Importing through Data > From Text/CSV and setting the column type to Text avoids it. That is Excel's commonly documented behavior; it is not something Python can test, so I did not run it here.

Formula injection

If a CSV cell begins with =, +, - or @, a spreadsheet may treat it as a formula. OWASP's CSV Injection page lists those four plus tab, carriage return and line feed, and notes full-width variants in some locales. Anyone who can put text into your app (a name field, a support ticket) can get it into an export that an employee opens in Excel. Quoting does not help, because the CSV parser strips the quotes before the spreadsheet evaluates the cell, and Python's writer does not sanitize anything:

csv.writer(buf).writerow(['=HYPERLINK("http://x","c")', '+1', '-2', '@SUM(A1)', '\tx'])
# '"=HYPERLINK(""http://x"",""c"")",+1,-2,@SUM(A1),\tx\r\n'

The common mitigation is to prefix risky cells with a single quote:

def safe(v):
    return "'" + v if v and v[0] in '=+-@\t\r' else v
[safe(v) for v in ['=1+1', '-5', 'ok', '@x']]   # ["'=1+1", "'-5", 'ok', "'@x"]

It has a cost. A legitimate negative number such as -5 gets mangled, and OWASP is blunt that "there is no universal CSV sanitization strategy that is safe for all spreadsheet applications and all downstream consumers." Apply it to free-text columns in exports aimed at humans, and never to files meant for machine consumers.

A practical checklist

1. Parse with a real CSV parser, never split(',') or line splitting; fields can contain commas and newlines.
2. Decide and record the delimiter, encoding and null convention out of band; the file will not tell you.
3. Strip or decode a BOM (utf-8-sig) when reading, and write one only if Excel users are the audience.
4. Read identifier columns as strings, and turn off default NA conversion when NA, None or null are real values.
5. Use strict parsing in validation pipelines so an unclosed quote fails instead of swallowing the file.
6. Neutralize formula-leading text in any export that opens in a spreadsheet.

To check a conversion, the hub's CSV to JSON and JSON to CSV tools show how quoting and delimiters round-trip, and CSV diff compares two exports. For header and nesting behavior, see our companion guide on converting CSV and JSON without losing data. A CSV-to-Markdown-table tool is also on the way.