Fixing Mojibake in a CSV Without Losing Any More Data

You open a CSV and the names read José Müller. The data is not lost, it is wearing the wrong clothes: someone read UTF-8 bytes as Windows-1252, and the letters you see are the right bytes shown through the wrong table. Reversing that is exact, with one condition about what has happened to the text since. This article shows the two ways I repair it, the extra change ftfy makes that you probably do not want, and the garbling that cannot be undone. If you have not confirmed that your problem is mojibake, start with why a CSV shows strange characters instead of accents.

The most common cause and its fix

The cause is almost always UTF-8 bytes decoded as Windows-1252 and then saved. The fix is the same two steps run backwards: turn the garbled text back into the bytes it was decoded from, using Windows-1252, then decode those bytes as UTF-8.

python - <<'PY'
bad = "José Müller, São Paulo"
fixed = bad.encode("cp1252").decode("utf-8")
print(bad, "->", fixed)
twice = "José".encode("utf-8").decode("cp1252").encode("utf-8").decode("cp1252")
print("double encoded:", twice, "->", twice.encode("cp1252").decode("utf-8").encode("cp1252").decode("utf-8"))
PY

José Müller, São Paulo -> José Müller, São Paulo
double encoded: José -> José

The first line is the repair of José Müller, São Paulo, which comes back as José Müller, São Paulo with no loss. The second shows the version that has been through the mistake twice. A file that was saved as garbled UTF-8 and then misread again turns José into José, and it needs the same reversal twice. Repeat the two steps once per layer and stop when the text stops containing Ã.

The reversal raises an error when the garbled text contains a character that Windows-1252 has no byte for. That is useful: it means the text was not damaged in the way you assumed, and you should look again rather than force it.

Using ftfy

ftfy is a Python library that detects and fixes this class of problem automatically, including mixed and repeated cases. Its main function is fix_text, and it does a little more than you might expect. I ran it on a garbled line and on a line that was already correct:

python - <<'PY'
import ftfy
bad = "José Müller, São Paulo, price €4.50, “smartâ€\x9d"
print("broken          :", bad)
print("fix_text        :", ftfy.fix_text(bad))
print("fix_encoding    :", ftfy.fix_encoding(bad))
fine = "José said “hello” and left… – ok"
print("clean text      :", fine)
print("fix_text(clean) :", ftfy.fix_text(fine))
print("fix_encoding    :", ftfy.fix_encoding(fine))
PY

broken          : José Müller, São Paulo, price €4.50, “smart”
fix_text        : José Müller, São Paulo, price €4.50, "smart"
fix_encoding    : José Müller, São Paulo, price €4.50, “smart”
clean text      : José said “hello” and left… – ok
fix_text(clean) : José said "hello" and left… – ok
fix_encoding    : José said “hello” and left… – ok

On the garbled line, both functions repaired the accents and the euro sign. The difference is in the quotes. fix_text returned straight quotes where the original had curly ones, and on the clean sentence it also replaced “hello” with "hello" when nothing was wrong. That is a default (uncurl_quotes) that normalizes typography, and it changes your data. fix_encoding performs only the encoding repair and left the clean text identical. For a data file, use fix_encoding, or pass uncurl_quotes=False to fix_text.

How many layers

Text that has been garbled more than once needs more than one reversal, and doing it by hand means guessing. A short function removes the guesswork: keep undoing the mistake while it succeeds and stop at the first failure, with a cap so it cannot loop. Because the reversal raises an error on text that is not mojibake, a clean string is left alone.

"""Undo UTF-8 read as Windows 1252, as many times as the text needs and no more."""
import sys


def undo_once(s: str) -> str:
    return s.encode("cp1252").decode("utf-8")


def unmojibake(s: str, max_layers: int = 3):
    layers = 0
    while layers < max_layers:
        try:
            fixed = undo_once(s)
        except (UnicodeEncodeError, UnicodeDecodeError):
            break          # not mojibake of this kind, or already clean
        if fixed == s:
            break
        s, layers = fixed, layers + 1
    return s, layers


if __name__ == "__main__":
    for line in sys.stdin.read().splitlines():
        fixed, n = unmojibake(line)
        print(f"{n} layer(s): {line!r} -> {fixed!r}")

Run on five lines, a clean one, a single layer, a double layer, a clean line with curly quotes, and one with a question mark:

printf '%s\n' 'José Müller' 'José Müller' 'José Müller' 'Zoë “quoted”' 'caf?' | python ../../unmojibake.py

0 layer(s): 'José Müller' -> 'José Müller'
1 layer(s): 'José Müller' -> 'José Müller'
2 layer(s): 'José Müller' -> 'José Müller'
0 layer(s): 'Zoë “quoted”' -> 'Zoë “quoted”'
0 layer(s): 'caf?' -> 'caf?'

The clean lines came back unchanged with zero layers. José took one pass and José took two. The line with the question mark also came back unchanged, because there is nothing to reverse. That behavior is the point: a repair that touches only text it can prove was damaged is safe to run over a whole column. A repair that guesses is not. The standard library's codec reference lists the encodings you may need if the wrong table was not Windows 1252.

The same caution applies to ftfy, which makes the decision with heuristics. Its documentation explains the heuristics and the options, and I would still spot check a sample of what it changes, comparing the set of non-ASCII characters before and after.

Applying it to a CSV

Repair the cells, not the file. Running a regular expression or a conversion over the raw bytes risks touching delimiters and quotes. Parse the file, repair each cell, and write it back:

cd enc && python - <<'PY'
import csv, ftfy
raw = open("utf8.csv", "rb").read().decode("utf-8").encode("utf-8").decode("cp1252", errors="replace")
rows = list(csv.reader(raw.splitlines()))
fixed = [[ftfy.fix_text(c) for c in r] for r in rows]
print(rows[1]); print(fixed[1])
PY

['1', 'José Müller', 'São Paulo', 'price €4.50']
['1', 'José Müller', 'São Paulo', 'price €4.50']

The first list is the garbled record, the second is the same record after repair. In a real script you would read the garbled file with encoding="utf-8", since the garbled text is valid UTF-8 text, map fix_encoding over each cell, and write with encoding="utf-8". Spot check a handful of rows containing the characters that matter, and diff the count of non-ASCII characters before and after: a repair should not change how many cells have them.

Garbling that cannot be undone

The reversal needs every original byte to survive. Two things destroy bytes. A decoder that replaces what it cannot read with U+FFFD, the black diamond, throws the original byte away. And an encoder that replaces what it cannot represent with ? throws away the character. I made both kinds of damage from the same line, Zoë “quoted” €4:

python - <<'PY'
import ftfy
original = "Zoë “quoted” €4"
lost = original.encode("utf-8").decode("cp1252", errors="replace")
print("garbled with U+FFFD :", lost)
print("ftfy on it          :", ftfy.fix_text(lost))
qm = original.encode("utf-8").decode("cp1252", errors="replace").encode("ascii", errors="replace").decode()
print("with ? substitutions:", qm)
print("ftfy on that        :", ftfy.fix_text(qm))
PY

garbled with U+FFFD : Zoë “quoted� €4
ftfy on it          : Zoë "quoted� €4
with ? substitutions: Zo?? ???quoted??? ???4
ftfy on that        : Zo?? ???quoted??? ???4

With the replacement characters, ftfy restored Zoë and €4 and left a damaged tail, because one byte of the closing quote was gone. With question marks, it restored nothing: Zo?? ???quoted??? ???4 carries no information about what the characters were. If the original export still exists, re-export it with the right encoding. If it does not, the question marks are permanent, and the article on question marks explains how to recognize and prevent them.

Boundary: when the damage is not mojibake

Repair is for text where every character is a plausible Latin letter or symbol and the sequence is wrong. If you see Cyrillic or Greek letters where Latin accents belong, the file was read with a different legacy table, and the reversal needs that table's name instead of Windows-1252. If the garbling appears only in your terminal, the file is fine, as the strange characters article says. And if the text is correct in the source database but wrong in the export, fix the export's encoding setting, not the file.

To prevent the next occurrence, convert legacy files once at the boundary, as in converting Latin 1 or Windows 1252 to UTF-8, and keep the original so a wrong guess can be undone.