CSV Encoding and the BOM: Why Your Accents Broke and Which Fix Applies

Encoding damage in CSV is asymmetric: a wrong parse produces visible nonsense immediately (é), or nothing at all until a value with one accented character breaks a join six weeks later.

The two-byte question: is there a BOM?

A UTF-8 BOM is EF BB BF at byte zero. It is optional in UTF-8 and meaningless in the RFC, but two things behave differently depending on it:

So: produce a BOM when a human will double-click the file, and strip it on every read path. That is not a contradiction, it is the actual state of the ecosystem. Excel's "CSV UTF-8" export writes the BOM for exactly this reason; plain "CSV" writes the local code page.

The BOM also shows up as a join failure rather than a display failure, which is where it costs the most hours. Two files, same data, one exported from Excel ("CSV UTF-8") and one from a script: the header keys differ by an invisible character, so df1.merge(df2, on='id') fails with "column 'id' not found" on a DataFrame whose columns visibly print as id. Print the header with repr() or JSON.stringify before debugging the merge — '\ufeffid' vs 'id' is a one-line diagnosis.

Mojibake lookup table

The garbled text tells you what went wrong more precisely than the filename does.

The last row is the one that costs the most time: the text is fine and a hidden character is in your key. Print the header with repr() / JSON.stringify rather than eyeballing it.

The mojibake is reversible — the replacement character is not

When bytes were decoded as the wrong single-byte encoding, the damage is a lossless round-trip: re-encode the mangled text back to the wrong encoding and decode as UTF-8.

# "é" back to "é": the file was UTF-8 bytes decoded as Latin-1
fixed = broken.encode('latin-1').decode('utf-8')

# Windows-1252 variant (smart quotes, dashes): cp1252 has gaps latin-1 lacks,
# so use errors='strict' to confirm the guess before trusting the output.
fixed = broken.encode('cp1252', errors='strict').decode('utf-8')

If instead you see ` (U+FFFD) or ?, **stop** — those characters mean bytes were *discarded* at some earlier step, usually a decode(errors='replace') or a database column with the wrong charset. There is nothing to reverse; find the step that dropped the bytes and re-export. The distinction is the whole lesson: mojibake is a wrong lens, ` is a lost negative. One tells you to change the decoder, the other tells you to change the source.

Line endings

CRLF (\r\n) is what RFC 4180 specifies as a record separator; LF is what most libraries actually write. Practical consequences:

• A CRLF file parsed by a reader that splits on \n leaves \r glued to the last field of every row. Your last column silently never matches its expected value — trim() hides it, which is why the bug looks fixed and returns in a different column.
• Mixed endings in one file are legal and common when someone appended with a different tool. Count them: grep -c $'\r' file.csv.
• sed -i 's/\r$//' is the standard fix, but it is a content change: if a quoted field legitimately contains \r\n inside it, you have just altered data. Quote-aware newline normalisation needs a real parser.

A \r stuck to the last column is the encoding bug that never shows up in development, because macOS and Linux tools write LF and most test fixtures are LF. It appears the first time a Windows user exports. Put the \r count in your ingest checks (grep -c $'\r' or Python raw.count(b'\r')) and treat a nonzero count as "this file came from Windows, decode accordingly" rather than an error.

Detecting instead of guessing

file -i data.csv                              # first guess, wrong often
python3 - <<'PY'
import sys
raw = open('data.csv','rb').read(20000)
for enc in ('utf-8-sig','utf-8','utf-16','cp1252','latin-1'):
    try: raw.decode(enc); print('works:', enc)
    except UnicodeDecodeError: pass
PY

latin-1 never fails — it maps every byte — so it is the last thing to try, not the first. If both utf-8 and cp1252 succeed, the file has no non-ASCII characters in the sampled window and you have learned nothing; sample from the middle of the file where the data with accents actually lives.

UTF-16 CSVs exist (PowerShell Out-File and some SAP exports default to them) and arrive looking like a file with nulls between every character. head -c 32 file.csv | hexdump -C settles it in one line: ff fe at the start is UTF-16 LE.

A detection ladder that survives real files, in order:

1. BOM first. EF BB BF → UTF-8 with BOM. FF FE → UTF-16 LE. FE FF → UTF-16 BE. A BOM is a declaration; trust it over any heuristic.
2. Try strict UTF-8. If the whole file decodes with no errors, it's UTF-8 (or ASCII, a subset). This catches the overwhelming majority and has no false positives — invalid UTF-8 byte sequences simply fail.
3. Only if UTF-8 fails, try cp1252 (the Windows Western default). It's latin-1 with 32 printable characters in the 80–9F range that latin-1 leaves as control codes — the smart quotes and em-dashes that make Word exports fail latin-1.
4. latin-1 as the never-fails fallback, labelled as a guess. If you're here, record that you guessed; the next file will have non-ASCII that disambiguates.

The trap in step 2: a file can be valid UTF-8 and still be wrong. é is valid UTF-8 (it's two real characters), so strict decoding passes on already-mojibake'd input. Detection tells you the encoding of the bytes on disk, not whether an earlier step already mangled them — that's what the lookup table above is for.

What this tool does with it

The converter reads the text you paste or upload as UTF-8 and does not attempt encoding detection — detection on a 20-character sample is a coin flip. Concretely, verified against the shipped parser:

• A CR-only file (classic Mac, still produced by some mainframe jobs) does not end a record: \r is skipped as a line-ending tolerance, so the whole file arrives as one row. If your row counter says 1 and your file has 4,000 lines, that is why.
• A BOM is not stripped, so an uploaded file's first header key keeps the \ufeff character. It will not match id, and nothing warns you, because structurally the file is fine.
• Quoted fields keep their inner newlines, so parsed row count and line count are allowed to differ — see patterns and gotchas.

For anything beyond a one-off, decode explicitly at the boundary (utf-8-sig, cp1252) and hand the parser text, never bytes you hoped about. Best practices covers the type-inference half of the same class of failure.