data encoding_error ai_generated true

CSV file with UTF-8 encoded data displays as garbled text when opened in Excel or other tools expecting Latin-1

ID: data/csv-encoding-mismatch-latin1-vs-utf8

Also available as: JSON · Markdown · 中文
90%Fix Rate
85%Confidence
1Evidence
2023-03-15First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
pandas 1.5.0 active — — —
Python 3.10 active — — —
Excel 365 active — — —
LibreOffice 7.4 active — — —

Root Cause

CSV files are saved with UTF-8 encoding but the reader or application (e.g., Excel) defaults to Latin-1 (ISO 8859-1), causing extended characters to be misinterpreted.

generic

中文

CSV文件以UTF-8编码保存，但读取器或应用程序（如Excel）默认使用Latin-1（ISO 8859-1）编码，导致扩展字符被错误解释。

Workarounds

  1. 85% success Re-save the CSV with explicit UTF-8 encoding using Python: df.to_csv('file.csv', encoding='utf-8-sig') to add BOM for Excel compatibility. 
    Re-save the CSV with explicit UTF-8 encoding using Python: df.to_csv('file.csv', encoding='utf-8-sig') to add BOM for Excel compatibility.
  2. 90% success Open the CSV in a text editor, then use 'Save As' with UTF-8 encoding. In Excel, use Data > From Text/CSV and select '65001: Unicode (UTF-8)' as the file origin. 
    Open the CSV in a text editor, then use 'Save As' with UTF-8 encoding. In Excel, use Data > From Text/CSV and select '65001: Unicode (UTF-8)' as the file origin.

中文步骤

  1. 使用Python重新保存CSV文件并显式指定UTF-8编码：df.to_csv('file.csv', encoding='utf-8-sig')，添加BOM以兼容Excel。
  2. 在文本编辑器中打开CSV文件，然后使用'另存为'功能选择UTF-8编码。在Excel中，使用数据>从文本/CSV导入，并选择'65001: Unicode (UTF-8)'作为文件来源。

Dead Ends

Common approaches that don't work:

  1. Manually changing file extension to .txt and re-importing 90% fail

    Does not address the underlying encoding issue; the data remains corrupted during import.

  2. Using a hex editor to remove the UTF-8 BOM 70% fail

    Removing BOM only affects byte order mark, not the actual encoding; data still garbled if reader assumes Latin-1.