data encoding_error ai_generated true

带有 UTF-16 BOM 的 CSV 文件被误解为 UTF-8，产生乱码列标题

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

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

其他格式: JSON · Markdown 中文 · English
90%修复率
84%置信度
1证据数
2024-02-14首次发现

版本兼容性

版本状态引入弃用备注
Microsoft Excel 365 active — — —
Python csv module 3.10+ active — — —
Pandas 1.5.0+ active — — —
Apache Commons CSV 1.10.0+ active — — —

根因分析

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

English

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

官方文档

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

解决方案

  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` 转换文件

无效尝试

常见但无效的做法:

  1. 50% 失败

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

  2. 80% 失败

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