Fix garbled characters (é, ’, �) in CSV files

Café – résumé ’ (CSV shows é and ’ instead of é and ’)

Common causes

• Excel opening a UTF-8 CSV without a byte order mark and assuming the system's ANSI code page
• A file exported as Windows-1252 or ISO-8859-1 being read by a tool that expects UTF-8
• Data that was already double-encoded (UTF-8 converted to UTF-8 again)
• A database connection or export using latin1 while the data is UTF-8
• Saving in Excel as plain 'CSV (Comma delimited)', which uses the ANSI code page, not UTF-8
• Characters that do not exist in the target encoding being replaced with ?

How to fix it

1. Identify the real encoding. On Linux or macOS run file -i data.csv (file -I on macOS). é patterns point to UTF-8 shown as Windows-1252, while � points to Windows-1252 or Latin-1 shown as UTF-8.
2. Import with the right encoding in Excel. Use Data > From Text/CSV, set File Origin to 65001: Unicode (UTF-8) or 1252: Western European, check the preview, then Load. Double-clicking the file skips this choice.
3. Convert the file to UTF-8. Run iconv -f WINDOWS-1252 -t UTF-8 data.csv > data-utf8.csv, or in a text editor reopen with the correct encoding and save as UTF-8.
4. Save from Excel as CSV UTF-8. Use File > Save As > 'CSV UTF-8 (Comma delimited) (*.csv)'. It writes a BOM, so Excel and most tools read it back correctly.
5. Write a BOM when exporting for Excel. In PHP write "\xEF\xBB\xBF" before the first fputcsv() call; in Python use encoding='utf-8-sig'. This tells Excel the file is UTF-8.
6. Read with explicit encoding in code. In Python use pd.read_csv('data.csv', encoding='utf-8-sig') or encoding='cp1252' for Windows exports. Never rely on the platform default.

Terminal / Python / PHP

file -i data.csv
iconv -f WINDOWS-1252 -t UTF-8 data.csv > data-utf8.csv

# Python: write a CSV Excel opens correctly
df.to_csv('out.csv', index=False, encoding='utf-8-sig')

// PHP: add a UTF-8 BOM before fputcsv()
fwrite($fh, "\xEF\xBB\xBF");

How to stop it happening again

• Standardize on UTF-8 for every export, import and database connection
• Add a UTF-8 BOM to CSV files meant to be opened in Excel
• Import CSVs through Excel's From Text/CSV instead of double-clicking
• Document the encoding and delimiter of any CSV you exchange with others