Can’t find a format? Request it

Guide

Fixing CSV encoding problems: BOM, UTF-16, Latin-1 and more

Why CSV imports show garbled characters or a strange first column, and how to detect and handle each encoding Excel and legacy systems produce.

CSV has no way to declare its own encoding, so every importer has to guess. When it guesses wrong you see mojibake: José instead of José, “ instead of a curly quote, or a first column called id instead of id.

The usual suspects

UTF-8 with a BOM. Excel’s “CSV UTF-8” export starts the file with the bytes EF BB BF. Decoders that don’t strip the byte-order mark include it in the first header, so row["id"] suddenly returns nothing. Sample

UTF-16. Excel’s “Unicode Text” export is UTF-16 LE with a BOM (FF FE), and tab-separated despite the name. Read as UTF-8, it looks like every character is followed by a NUL byte. Sample

Latin-1 and Windows-1252. Older Windows software and many ERPs export single-byte encodings. They are almost identical, except that Windows-1252 uses bytes 0x80–0x9F for characters like €, “ and ”. Latin-1 sample · Windows-1252 sample

Shift_JIS and GB18030. Files from Japanese and Chinese systems often use national encodings rather than UTF-8. Shift_JIS sample · GB18030 sample

A detection strategy that works

  1. Check for a BOM first. EF BB BF means UTF-8, FF FE means UTF-16 LE and FE FF means UTF-16 BE. Strip the BOM before parsing.
  2. Try strict UTF-8. If the whole file decodes without errors, it is almost certainly UTF-8. Random Latin-1 text is very unlikely to be valid UTF-8 by accident.
  3. Fall back to Windows-1252, not Latin-1. It is a superset for printable characters and is what “ANSI” means on Western Windows systems.
  4. Let users override the guess. Show a preview of the first rows with an encoding picker.

In Python, open(path, encoding='utf-8-sig') handles UTF-8 with or without a BOM. In JavaScript, new TextDecoder('utf-8', { fatal: true }) throws on invalid input, which makes step 2 easy.

Beyond encoding

Once the bytes decode correctly, the format itself still varies:

  • Line endings: CRLF from Windows, CR only from classic Mac OS, or a mix of both.
  • Delimiters: Excel in much of Europe uses semicolons, because the comma is the decimal separator.
  • Quoting: fields can contain commas, quotes and even newlines. Use a real CSV parser, not split(','). Tricky quoting sample
  • CSV injection: a value like =1+1 becomes a live formula when the exported file is opened in a spreadsheet. Prefix such values with ' when you export user data.

All of these files are collected in the CSV import use case.

Sample files for this guide