data encoding_error ai_generated true

CSV file with UTF-16 BOM is misinterpreted as UTF-8, producing garbled column headers

ID: data/csv-encoding-utf16-vs-utf8-bom

Also available as: JSON · Markdown · 中文
90%Fix Rate
84%Confidence
1Evidence
2024-02-14First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
Microsoft Excel 365 active — — —
Python csv module 3.10+ active — — —
Pandas 1.5.0+ active — — —
Apache Commons CSV 1.10.0+ active — — —

Root Cause

CSV files saved with UTF-16 encoding (often by Excel on Windows) start with a UTF-16 BOM (0xFFFE) that UTF-8 parsers interpret as two characters, corrupting the first column name.

generic

中文

以 UTF-16 编码保存的 CSV 文件（通常由 Windows 上的 Excel 生成）以 UTF-16 BOM（0xFFFE）开头，UTF-8 解析器将其解释为两个字符，从而损坏第一个列名。

Official Documentation

https://docs.python.org/3/library/csv.html

Workarounds

  1. 95% success Detect and decode UTF-16 BOM explicitly: `with open('file.csv', 'rb') as f: raw = f.read(); if raw[:2] == b'\xff\xfe': text = raw.decode('utf-16'); else: text = raw.decode('utf-8')` 
    Detect and decode UTF-16 BOM explicitly: `with open('file.csv', 'rb') as f: raw = f.read(); if raw[:2] == b'\xff\xfe': text = raw.decode('utf-16'); else: text = raw.decode('utf-8')`
  2. 90% success Use pandas with explicit encoding detection: `import chardet; with open('file.csv', 'rb') as f: enc = chardet.detect(f.read(10000)); df = pd.read_csv('file.csv', encoding=enc['encoding'])` 
    Use pandas with explicit encoding detection: `import chardet; with open('file.csv', 'rb') as f: enc = chardet.detect(f.read(10000)); df = pd.read_csv('file.csv', encoding=enc['encoding'])`
  3. 85% success Convert the file using `iconv -f UTF-16 -t UTF-8 file.csv > fixed.csv` 
    Convert the file using `iconv -f UTF-16 -t UTF-8 file.csv > fixed.csv`

中文步骤

  1. 显式检测并解码 UTF-16 BOM：`with open('file.csv', 'rb') as f: raw = f.read(); if raw[:2] == b'\xff\xfe': text = raw.decode('utf-16'); else: text = raw.decode('utf-8')`
  2. 使用 pandas 和显式编码检测：`import chardet; with open('file.csv', 'rb') as f: enc = chardet.detect(f.read(10000)); df = pd.read_csv('file.csv', encoding=enc['encoding'])`
  3. 使用 `iconv -f UTF-16 -t UTF-8 file.csv > fixed.csv` 转换文件

Dead Ends

Common approaches that don't work:

  1. 50% fail

    Notepad may silently change line endings or add extra characters, causing further corruption.

  2. 80% fail

    utf-8-sig only handles UTF-8 BOM (0xEFBBBF), not UTF-16 BOM (0xFFFE).