Why does my CSV show strange characters?
Accents turning into é and ’ in a CSV is an encoding mismatch, not corruption. What causes it, how to open the file correctly and how to stop it happening.
Names like José arrive as José, apostrophes turn into ’ and a column of city names looks like it went through a shredder. It looks like corruption, but the data is intact. Two programs disagree about how the text is encoded.
What is going on
Text is stored as bytes and an encoding is the rule for turning bytes into characters. Modern systems use UTF-8, where an accented letter takes two bytes. Older Windows software uses a different encoding where each byte is one character. When a UTF-8 file is read with the older rule, each accented letter becomes two wrong characters: é becomes é, a curly apostrophe becomes ’.
The most common place this happens is Excel on Windows opening a CSV exported by a web application: the file is UTF-8 and Excel reads it with the old Windows encoding.
The byte order mark
UTF-8 files can start with three invisible bytes, the byte order mark, announcing the encoding. Excel uses it to read the file correctly. Other programs sometimes don’t expect it and show odd characters at the start of the first heading or fail to match that column by name. So it helps with Excel and hurts elsewhere.
Fixing it
To open the file in Excel, use Data, From Text/CSV and choose UTF-8 as the file origin or convert it with CSV to Excel, which reads UTF-8 and writes a workbook Excel opens correctly, while also keeping leading zeros, as explained in why Excel removes leading zeros.
To feed the file to another system, remove the byte order mark and normalise line endings with the CSV cleaner, which lists every change it makes.
When exporting from Excel, Excel to CSV writes UTF-8 and offers the byte order mark as an option, on for files going back into Excel, off for everything else.
Don’t save the garbled version
If Excel shows garbled characters and you save the file, the garbled text is what gets saved and the original characters are gone for good. Close without saving and reopen correctly. More on how CSV and Excel files differ in CSV vs Excel.
Common questions
Is my data corrupted?
Almost never. The bytes are fine; the program reading them is using the wrong encoding. Open the file with the right one and the characters come back, as long as nobody has saved the garbled version over the original.
What is a byte order mark?
Three invisible bytes at the start of a UTF-8 file that announce its encoding. Excel relies on it to read UTF-8 CSVs correctly, while some other programs choke on it and show odd characters at the start of the first heading.
Why does it look fine in one program and wrong in Excel?
Most modern programs assume UTF-8. Excel on Windows assumes the older Windows encoding for CSV files unless the file starts with a byte order mark or you choose the encoding while importing.
How do I open a UTF-8 CSV in Excel correctly?
Use Data, From Text/CSV and choose 65001: Unicode (UTF-8) as the file origin. Or convert the CSV to a real Excel file first, which carries the text correctly.