How to fix CSV encoding problems

A CSV is bytes. An encoding is the agreement about what those bytes mean. When the writer and the reader disagree, you get one of three very distinct symptoms, and knowing which one you have tells you whether the file is repairable.

This matters more than it sounds. One of these three is recoverable with a single re-read. Another is recoverable with a small transformation. The third means the data is already gone and you need a fresh export. People routinely spend hours on the third one.

Symptom: é where é should be

Cause. The file is UTF-8 and something read it as a single-byte encoding, almost always Windows-1252. In UTF-8, é is the two-byte sequence C3 A9. Read one byte at a time as Windows-1252, C3 is à and A9 is ©, giving é. The Japanese word for this is mojibake, and there is no better one.

A small phrasebook, because recognizing the pattern saves time:

Fix. The good news is that the bytes are intact. Nothing was lost, because Windows-1252 maps every possible byte to some character, so the decode did not fail, it just produced nonsense. Re-read the file declaring UTF-8 and the accents come back.

If the mojibake has already been written into a new file, you can usually undo it by encoding the text back to Windows-1252 bytes and decoding those as UTF-8. That is a round trip, and it is exactly what a repair script does. It only works when the intermediate step preserved every character, which Windows-1252 does and ASCII does not.

Fix it: open the file and let the loader normalize the encoding →

Symptom: a black diamond with a question mark, or a bare ?

Cause. This is U+FFFD, the Unicode replacement character. A decoder hit a byte sequence it could not map and substituted this marker. Unlike mojibake, this is destructive. The original bytes are not in the file any more; the marker is.

The most common route is a pipeline step that read the file as UTF-8 in non-strict mode, hit Windows-1252 bytes, replaced them, and wrote the result back out as a "UTF-8" file. The file is now genuinely valid UTF-8. It just contains diamonds where the names used to be.

Fix. There is no fix inside that file. Go back one step in the chain and get the version that had the original bytes. If the chain is a script you control, make the decode strict so it fails loudly instead of replacing characters silently, and then handle the failure by choosing the right encoding.

That principle is worth generalizing. In our own loader, the UTF-8 probe runs with fatal: true precisely so that a bad decode throws instead of quietly producing replacement characters. The failure is then caught and the bytes are re-decoded as Windows-1252, which cannot fail. A decoder that never fails is not a feature; it is a way of losing data without a stack trace.

Fix it: check whether the damage is already in the file →

Symptom:  before the first column name

Cause. A UTF-8 byte order mark. Three bytes, EF BB BF, at the very start of the file. UTF-8 has no byte order to mark, so the sequence is purely a signal that says "this is UTF-8". Excel on Windows uses it, which is genuinely helpful. Many other parsers do not strip it, and then it becomes part of the first field.

The tell is subtle and infuriating: a column named order_id that does not match a join, a lookup that fails on the first column only, a WHERE clause that never matches. The column is really named order_id and it prints identically.

Fix. Decide who the file is for. Going to Excel on Windows and double-clicked? Keep the BOM, it is doing useful work. Going into a data pipeline, a database import or an API? Strip it. Do not try to have it both ways with a single artifact.

To detect one from the terminal:

head -c 3 yourfile.csv | od -An -tx1
# efbbbf  -> UTF-8 BOM present

Fix it: detect a BOM and other structural surprises →

Symptom: the file looks like it has a null between every letter

Cause. It is UTF-16, and something is reading it as a single-byte encoding. UTF-16 stores each ASCII character as two bytes, the character and a zero. Displayed by a byte-at-a-time reader you get o.r.d.e.r._.i.d with nulls in the gaps. Exports from older SQL Server tooling and some Windows PowerShell defaults land here.

If the file starts with FF FE it is UTF-16 little endian; FE FF is big endian. Some files have no mark at all, which is why our loader also checks null density in the first kilobyte: if more than a quarter of those bytes are zero, it treats the file as BOM-less UTF-16 little endian rather than guessing at Latin.

Fix. Convert to UTF-8 before anything else touches the file. DuckDB's CSV reader, which is what runs under our workbench, rejects anything that is not UTF-8 outright, so the conversion happens in the loader before the parse and you get a notice telling you it happened.

Fix it: load the file and export it as clean UTF-8 CSV →

Check what is really in the file

Encoding problems are the class where guessing costs the most, so establish the facts before you change anything.

1. Look at the first three bytes. head -c 3 file.csv | od -An -tx1. A BOM answers the encoding question immediately.
2. Ask the operating system. file -I file.csv gives a charset guess based on a sample.
3. Search for the marker. If the file contains the U+FFFD character anywhere, that data is already lost and the rest of the investigation is about the pipeline, not the file.
4. Open it and look at accented names. Nothing beats reading twenty rows of a customer table with your own eyes.

Then standardize. Pick UTF-8, with a BOM only if Excel is the destination, and enforce it at the point where files enter your process. Encoding problems are cheap to prevent at the boundary and expensive to chase three systems later.

Fix it: hand Excel a workbook instead, and skip the encoding question entirely →