Fix CSV Encoding

Worked example: a broken export

This is what the example button loads:

id,name,city,note
1,Café Müller,Zürich,"“best espressoâ€[U+009D]"
2,José García,Málaga,visited in May
3,Björn Östberg,Malmö,"– pending –"
4,Ada Lovelace,London,fine already

Drop it in and, with no settings touched:

id,name,city,note
1,Café Müller,Zürich,“best espresso”
2,José García,Málaga,visited in May
3,Björn Östberg,Malmö,– pending –
4,Ada Lovelace,London,fine already

4 rows · no BOM · LF line endings in the source · written with LF
· 8 cells repaired

Eight cells, and the note column is the half that matters. The closing curly quote breaks into three characters and the third of them, written above as [U+009D], is an invisible control character with no printable form at all. The accented names would come back from any repair tool; the curly quotes and the en dashes in the notes are the CP1252 block described below, and a repair built on a plain character-code map loses exactly those. Row 4 is the control. It was never broken, so the detection never looks at it and it comes through untouched. That matters more than it sounds: a repair pass that runs over clean text can break it, and the reason the automatic mode only touches cells carrying the tell-tale pairs is to make that impossible.

A repair that cannot be verified is not applied

Running the mistake backwards produces a sequence of bytes. Those bytes are either valid UTF-8 or they are not, and that is a question with a definite answer rather than a judgement call.

The decode is done with TextDecoder("utf-8", { fatal: true }). Without the fatal flag, a decoder given invalid bytes silently substitutes U+FFFD, the replacement character, and hands back a string full of question-mark diamonds that looks like a successful repair. With it, the decode throws, the cell keeps its original text, and the count of cells that looked broken but did not repair cleanly appears in the report.

Two more guards sit alongside it. A repair whose output is identical to its input is not a repair and is not counted as one. And a repair that produces a replacement character anywhere in the result is rejected outright, because that means the bytes were not what the theory said they were.

The practical upshot: the number of repaired cells in the report is a number you can trust. Every one of those cells went through a round trip that came back as valid UTF-8.

When this page cannot help

The repair here undoes one specific mistake: UTF-8 read as Windows-1252 and re-saved as UTF-8. That covers the large majority of broken CSVs, and it does not cover everything.

• A file that is genuinely single-byte encoded. If the bytes on disk really are Latin-1 or CP1252, nothing was double-encoded and there is nothing to run backwards. The characters will look wrong when the browser reads the file as UTF-8, and the fix is to re-export as UTF-8 from the system that produced it. The page says so when it finds no mojibake, rather than leaving you to wonder.
• Double or triple mangling. A file that has been through the same mistake twice needs the repair applied twice. Run the output through the page again; the second pass finds the second layer.
• Text that already contains replacement characters. Those diamonds mean a decoder gave up before you got the file, and the original bytes are gone. Nothing can bring them back.
• Legacy Asian encodings. Shift-JIS, Big5, GB18030 and their relatives are multi-byte and are not the mistake this page reverses. Convert at the source.

Try every cell exists for the borderline case where the detection is too conservative: a file where the only broken characters happen to fall outside the tell-tale pairs. It is safe, because the verification still applies to every attempt, and it is slower on a large file for no benefit when the automatic mode already found everything.

Related