Fixing Garbled Text in CSV Files and Keeping It Fixed

"The accents are wrong" is the most common problem report about CSV files, and it has at least six different causes that look alike for the first thirty seconds. A guess at the fix, usually re-saving the file in a spreadsheet, can turn a recoverable problem into a permanent one. This guide is the route I follow, in order: read the symptom, look at the bytes, name the encoding, convert once, load with the encoding declared, and check the result. Every step links to the article that goes deeper, and every claim below was run on a bench with PostgreSQL 18, MySQL 8.4, DuckDB 2.0, SQLite 3.54 and Python 3.13.

Why the problem exists at all

A computer stores numbers, so text needs a table that maps numbers to letters. ASCII, from the 1960s, used 7 bits and covered English. When the rest of the world needed accents and currency signs, every region used the eighth bit differently, and the result was dozens of "code pages": ISO 8859 1 for Western Europe, ISO 8859 2 for Central Europe, Windows 1252 and Windows 1250 as Microsoft's variants, Shift_JIS and GBK for East Asian scripts that need more than 256 characters. A byte like E9 meant é in one table, é shifted into a different letter in another and half of a Japanese character in a third.

Unicode ended the argument by giving every character a number, and UTF-8, specified in RFC 3629, became the way to write those numbers as bytes: ASCII stays one byte, everything else takes two to four. The web, Linux, macOS and every modern database default to it. The trouble is the long tail. Windows applications still write the legacy code page of the user's locale, mainframe exports use EBCDIC derivatives, and a CSV, which has no header to say what it is, travels between the two worlds with nothing to tell the receiver which one wrote it. The garbling in this guide is that gap made visible.

For CSV this has a sharp consequence. JSON and XML solve the problem by contract: JSON is UTF-8 by specification, and XML declares its encoding on the first line. CSV never made either choice, and the RFC mentions a charset parameter on the media type that no file on disk carries. Everything in this guide is what you do because of that omission.

Start with the symptom, not the fix

The first thing to establish is which kind of broken the text is, because the repair for each is different and the wrong one does damage. The characters themselves tell you.

Notice what is missing: any way to fix the file without knowing what happened to it. The rest of the guide is the work of finding out.

One table, six encodings, five readers

To see how much depends on the reader, I generated the same small table in six encodings: UTF-8, UTF-8 with a BOM, Windows 1252, ISO 8859 1, UTF-16 and Shift_JIS. The table has accents, a euro sign, curly quotes, and, in some files, Japanese and Polish text. The script writes all of them from one source so any difference in outcome is down to the encoding.

"""Write the same small table in the encodings people actually send, so every encoding article starts from the same bytes."""
import codecs
import pathlib

out = pathlib.Path("enc")
out.mkdir(exist_ok=True)

WEST = "id,name,city,note\n1,José Müller,São Paulo,price €4.50\n2,Zoë Dupré,Zürich,“smart quotes”\n3,Åsa Öberg,Malmö,café\n"
LATIN = "id,name,city\n1,José Müller,São Paulo\n2,Zoë Dupré,Zürich\n3,Åsa Öberg,Malmö\n"
MIXED = WEST + "4,田中 太郎,東京,日本語\n5,Łukasz Nowak,Kraków,zażółć\n"
JA = "id,name,city\n1,田中 太郎,東京\n2,山田 花子,大阪\n3,佐藤 次郎,京都\n"

files = {
    "utf8.csv": MIXED.encode("utf-8"),
    "utf8-bom.csv": codecs.BOM_UTF8 + MIXED.encode("utf-8"),
    "cp1252.csv": WEST.encode("cp1252"),
    "latin1.csv": LATIN.encode("latin-1"),
    "utf16.csv": MIXED.encode("utf-16"),
    "shift_jis.csv": JA.encode("shift_jis"),
}
for name, data in files.items():
    (out / name).write_bytes(data)
    print(f"{name:14} {len(data):4} bytes")

Then I read each file with five readers, using no options at all, and checked whether the second cell of the first record came out as the right text. ok means the right text. WRONG means the reader returned something without complaint that was not the right text. ERROR means it refused.

python enc_matrix.py

file              python    pandas    duckdb    sqlite  postgres
cp1252.csv         ERROR     ERROR     ERROR     WRONG     ERROR
latin1.csv         ERROR     ERROR     ERROR     WRONG     ERROR
shift_jis.csv      ERROR     ERROR     ERROR     WRONG     ERROR
utf16.csv          ERROR     ERROR     ERROR     WRONG     ERROR
utf8-bom.csv          ok        ok        ok        ok        ok
utf8.csv              ok        ok        ok        ok        ok

Two files loaded everywhere. UTF-8, with or without a BOM, is the one thing every reader assumes by default. Four files failed in four readers: Python, pandas, DuckDB and PostgreSQL all raised errors on the Windows 1252, Latin 1, UTF-16 and Shift_JIS files. That is the good outcome. The bad one is the fifth reader. SQLite's shell does not check the encoding of what it imports, so it loaded all four files and returned wrong text for every one of them, with no error. A loader that accepts anything is the dangerous kind.

The practical conclusion is that a broken file is usually caught by the first strict reader you hand it to, and that you want the strict reader first, before the lenient one makes the damage permanent. This is also why the order of the checks below starts with bytes and not with loading.

Look at the bytes before anything else

The preflight takes a second and answers the questions that decide everything. It reads the raw bytes and reports a byte order mark, NUL and other control bytes, UTF-8 validity with the position of the first failure, replacement characters already saved in the text, and sequences that look like mojibake.

"""Look at the bytes of a CSV before any reader does: BOM, UTF-8 validity, NULs, control characters, mojibake, replacement characters."""
import re
import sys

MOJIBAKE = re.compile("[ÃÂ][\u0080-¿]|â[€‚][\u0080-¿™œ“”]?")


def report(path: str) -> list[str]:
    raw = open(path, "rb").read()
    notes = []
    if raw.startswith(b"\xef\xbb\xbf"):
        notes.append("UTF-8 BOM present")
    if raw.startswith((b"\xff\xfe", b"\xfe\xff")):
        return ["UTF-16 byte order mark; decode with encoding='utf-16'"]
    nul = raw.count(b"\x00")
    if nul:
        notes.append(f"{nul} NUL byte(s)")
    ctl = sum(1 for b in raw if b < 0x20 and b not in (0x09, 0x0A, 0x0D))
    if ctl - nul > 0:
        notes.append(f"{ctl - nul} other control byte(s)")
    try:
        text = raw.decode("utf-8")
    except UnicodeDecodeError as e:
        line = raw[:e.start].count(b"\n") + 1
        return notes + [f"not valid UTF-8, first bad byte {e.start} on line {line} ({raw[e.start:e.end].hex()})"]
    if "�" in text:
        notes.append(f"{text.count(chr(0xfffd))} replacement character(s) already saved in the text")
    moji = len(MOJIBAKE.findall(text))
    if moji:
        notes.append(f"{moji} mojibake-looking sequence(s) such as é or ’")
    if b"\r\n" in raw and re.search(rb"(?<!\r)\n", raw):
        notes.append("mixed CRLF and LF line endings")
    return notes or ["no findings"]


if __name__ == "__main__":
    for p in sys.argv[1:]:
        for i, n in enumerate(report(p)):
            print(f"{p if i == 0 else '':22} {n}")

Run on the clean file, the BOM file, the Windows 1252 file, the UTF-16 file, a garbled file where UTF-8 was already read as Windows 1252, and a lossy file where question marks replaced letters:

cd enc && python ../../preflight_text.py utf8.csv utf8-bom.csv cp1252.csv utf16.csv garbled.csv lossy.csv

utf8.csv               no findings
utf8-bom.csv           UTF-8 BOM present
cp1252.csv             not valid UTF-8, first bad byte 23 on line 2 (e9)
utf16.csv              UTF-16 byte order mark; decode with encoding='utf-16'
garbled.csv            3 mojibake-looking sequence(s) such as é or ’
lossy.csv              no findings

Each result points at a different article. The Windows 1252 file is not valid UTF-8 and the first bad byte is E9 on line 2, so the next step is to find out what it is. The UTF-16 file says so directly. The garbled file contains sequences that only mojibake produces. And the lossy file reports no findings, which is the most important line in the output: question marks are valid UTF-8, and no byte level check can tell that letters used to be there. If a file is short of accents and the people who made it were expecting them, trust your eyes over a script.

Name the encoding

If the file is not valid UTF-8 and has no marker, the encoding is a decision. file -i and uchardet are the first two tools, and the Python libraries chardet and charset-normalizer are the fallback. On the six test files, uchardet named all six correctly. file -i named four and declined to name the Windows 1252 and Shift_JIS files, which is honest. charset-normalizer said cp1250 for both Western legacy files, which would have turned é into a different letter, and chardet returned the right family with a confidence of 0.06 or lower. The article on detecting the encoding of a CSV file has the table and the cross check I use: decode a sample with the answer and read the accented lines.

Where the choice is between Windows 1252 and Latin 1, take Windows 1252 for files from Windows and any file with curly quotes or a euro sign. They agree on every printable letter and differ in 32 positions, mostly the punctuation people care about.

Convert once, at the boundary

When you know the source encoding, convert to UTF-8 and keep the original. With iconv, iconv -f WINDOWS-1252 -t UTF-8 in.csv > out.csv; in Python, read with encoding="cp1252" and newline="" and write with encoding="utf-8". The conversion article covers both, the five byte values that Windows 1252 leaves undefined, and how to verify that the decoded text is the same before and after. A conversion that changes the byte count and not the text is working.

Do the conversion exactly once. A file converted twice has been decoded as the wrong thing the second time, and é becomes é and then a longer string of symbols. If a pipeline has several stages, put the conversion in the first and state in the documentation that everything after it is UTF-8. A comment in a README beats a clever auto detect that runs at every stage.

Or tell the loader, and skip the conversion

Each engine has a way to declare the file's encoding, and using it avoids a copy of the data. I used the Latin 1 file for three of them, since it has no characters that Latin 1 lacks.

PostgreSQL takes an ENCODING option on COPY. The same file that failed with invalid byte sequence for encoding "UTF8" loaded three clean rows with ENCODING 'LATIN1', as the invalid UTF-8 article shows.

MySQL takes CHARACTER SET on LOAD DATA, and the table's own character set controls how it is stored. Loading the Latin 1 file with CHARACTER SET latin1 into a utf8mb4 table stored José Müller as the bytes 4A 6F 73 C3 A9 ..., which is UTF-8:

cd enc && mysql -h mysql -uroot --local-infile=1 --default-character-set=utf8mb4 lab <<'SQL'
DROP TABLE IF EXISTS people;
CREATE TABLE people (id INT, name VARCHAR(40), city VARCHAR(40)) CHARACTER SET utf8mb4;
LOAD DATA LOCAL INFILE 'latin1.csv' INTO TABLE people CHARACTER SET latin1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
SELECT id, name, city, HEX(name) FROM people LIMIT 2;
SQL

id	name	city	HEX(name)
1	José Müller	São Paulo	4A6F73C3A9204DC3BC6C6C6572
2	Zoë Dupré	Zürich	5A6FC3AB2044757072C3A9

DuckDB takes an encoding option on read_csv. It accepted latin-1 and utf-16 in my run, and windows-1252 read the Windows file correctly, euro sign and curly quotes included:

cd enc && duckdb -box -c "SELECT id, name FROM read_csv('latin1.csv', encoding='latin-1') LIMIT 2; SELECT id, name FROM read_csv('utf16.csv', encoding='utf-16') LIMIT 2;"

┌────┬─────────────┐
│ id │    name     │
├────┼─────────────┤
│ 1  │ José Müller │
│ 2  │ Zoë Dupré   │
└────┴─────────────┘
┌────┬─────────────┐
│ id │    name     │
├────┼─────────────┤
│ 1  │ José Müller │
│ 2  │ Zoë Dupré   │
└────┴─────────────┘

cd enc && duckdb -box -c "SELECT id, name, note FROM read_csv('cp1252.csv', encoding='windows-1252') LIMIT 2" 2>&1 | head -8

┌────┬─────────────┬────────────────┐
│ id │    name     │      note      │
├────┼─────────────┼────────────────┤
│ 1  │ José Müller │ price €4.50    │
│ 2  │ Zoë Dupré   │ “smart quotes” │
└────┴─────────────┴────────────────┘

SQLite's .import has no encoding option and reads bytes as they are, which is the reason it returned wrong text in the matrix. Convert first. The practical rule is that any engine that has a switch should get the switch, and the ones that do not should get a UTF-8 file.

The byte order mark

Three invisible bytes, EF BB BF, cause a surprising amount of trouble because the readers split on them. pandas, DuckDB and SQLite strip them, while Python's utf-8 codec and PostgreSQL's HEADER match do not, so the first column becomes id and a lookup by name fails. The cure on the reading side is encoding="utf-8-sig"; on the writing side, add a BOM only for files that an Excel user will double click. The BOM article has the five way comparison and the commands for adding and removing one.

Spreadsheets are a separate problem

When the file is right and Excel still shows garbage, the reader is the problem. Excel on Windows is widely reported to read a UTF-8 file without a BOM as a legacy code page, which produces the same Ã pairs described above, and the usual remedies are writing the BOM or importing with the file origin set to UTF-8. I did not have desktop Excel on the bench, and the article on opening a UTF-8 CSV in Excel separates what I verified (the bytes) from what is reported (Excel's behavior). Leading zeros and date conversion are a different set of Excel problems, covered from the leading zeros article onward.

Damage already done

If the file is already garbled, two cases decide what is possible. When the text is mojibake, UTF-8 bytes that were read as Windows 1252 and saved, the repair is exact: encode the garbled text back to Windows 1252 and decode as UTF-8. ftfy.fix_encoding does it, including for text garbled twice. Use fix_encoding and not fix_text, because the latter also straightens curly quotes in text that was fine, as measured in the repair article.

When the text has question marks, nothing can be repaired, because the encoder wrote the byte 3F in place of the character and kept no record. In my test, exporting one table through Latin 1 turned 0 question marks into 16. The only remedy is the original data, which is why the question marks article puts so much weight on finding where the substitution happened and fixing the export.

Everything in this guide is built around that one distinction, between a wrong label and a lost character. A wrong label can be corrected at any time by anyone. A lost character can only be saved at the source.

What each engine assumes

The engines differ in where the encoding lives, and knowing it tells you where the misread happened.

The PostgreSQL manual's chapter on character set support lists which server and client encodings can convert to one another, and it is the reference for the client side of the problem. When a database holds correct text and the export is wrong, client_encoding is the first setting to check, because the server converts on the way out to whatever the client says it wants.

The engines also differ in how loudly they fail. PostgreSQL, DuckDB and Python raise errors, which I count as the right design. SQLite takes anything. MySQL's LOAD DATA takes most things and writes warnings. If a pipeline's only quality gate is "the load did not error", it has no gate on MySQL or SQLite.

Turning the bad files into a regression test

The six files from the matrix are worth keeping. A folder with one file per encoding, a BOM file, a NUL file and a file of invalid bytes becomes a test suite for your import path: feed every file to the loader and assert that the valid ones load with the right text and the invalid ones are rejected with an error you expect. When someone changes the loader, upgrades the library or moves the job to a different image, the suite tells you in a minute whether the encoding behavior changed.

Three assertions cover most of it. A file with a known name that contains accents loads with that name byte for byte. A file in a legacy encoding is either converted correctly or rejected, never loaded with wrong text. And a file with NUL bytes or invalid sequences fails the preflight with a message. The preflight script above is the first assertion's gate; the others are what the test checks. Mark the fixtures as binary in Git, with *.csv -text in .gitattributes, because Git's line ending normalization will otherwise rewrite some of them, as the line endings article shows.

What to ask the sender

Most garbled files are cheaper to prevent than to repair, and the prevention is a short conversation. Ask which program produced the export and on which operating system. Ask whether the program has an encoding or "code page" setting, and what it is. Ask whether the file is edited by hand afterwards, and in what editor, since an editor that re-saves in a different encoding is a common second author of the damage. Ask for UTF-8, and for the BOM only if the file is meant to be opened in Excel. Write the answers in the contract: encoding, delimiter, quoting, line endings, BOM and the meaning of an empty field.

If the sender cannot change anything, put your preflight at the door and reject files that fail with a reason that goes back to them. A rejection message that says "line 2 byte 13 is not valid UTF-8" gets an answer much faster than "the file is broken". The validation guide has more checks of this kind.

Bytes that are not text

Two byte classes cause trouble that looks like encoding and is not. NUL bytes, the byte 00, are kept as data by Python and DuckDB and cut a line short in PostgreSQL's \copy, which then reports extra data after last expected column. Stripping them from a 130 MB file took 0.34 s with tr -d '\000' and 0.17 s with a chunked Python loop, and the NUL article has the full timing table and the control character range that keeps tabs and line breaks. The trap in finding them is that grep -c without -a returned zero for a file with 400 of them.

Four byte characters are the other class. Emoji are stored in four bytes in UTF-8, and a MySQL column declared with the three byte utf8mb3 character set cannot hold them. LOAD DATA stored ?, finished with exit status 0 and printed two warnings, while a plain INSERT of the same text failed with error 1366. The emoji article has the exact commands and the checks that catch it. If your target is PostgreSQL or SQLite, this one does not exist.

A worked example, start to finish

Here is the routine on the Windows 1252 file, from raw export to a PostgreSQL table, in one run. It checks the bytes, converts once, checks again, then loads with HEADER match so a changed header would stop the load.

python ../../preflight_text.py cp1252.csv
iconv -f WINDOWS-1252 -t UTF-8 cp1252.csv > cp1252-utf8.csv
python ../../preflight_text.py cp1252-utf8.csv
psql <<'SQL'
DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts (id int, name text, city text, note text);
\copy contacts FROM 'cp1252-utf8.csv' WITH (FORMAT csv, HEADER match)
SELECT id, name, city, note, octet_length(name) - length(name) AS extra_bytes FROM contacts ORDER BY id;
SQL

cp1252.csv             not valid UTF-8, first bad byte 23 on line 2 (e9)
cp1252-utf8.csv        no findings
NOTICE:  table "contacts" does not exist, skipping
DROP TABLE
CREATE TABLE
COPY 3
 id |    name     |   city    |      note      | extra_bytes 
----+-------------+-----------+----------------+-------------
  1 | José Müller | São Paulo | price €4.50    |           2
  2 | Zoë Dupré   | Zürich    | “smart quotes” |           2
  3 | Åsa Öberg   | Malmö     | café           |           2
(3 rows)

The first preflight line says the original is not valid UTF-8, with the first bad byte at offset 23, the E9 of José. After iconv the second preflight finds nothing. The table then holds José Müller, São Paulo, the euro price and the curly quotes exactly as the generator wrote them. The last column is the check I like best: octet_length(name) - length(name) is the number of extra bytes beyond one per character, and it comes out as 2 for each name, because each has two accented letters at two bytes apiece. A column of zeros there would mean the text arrived as ASCII, or as something worse.

Notice what the example does not do. It does not detect the encoding automatically, because I knew where the file came from. It does not delete or replace any character. It does not touch the original. And it fails at the first point where something is wrong, which for a bad file is the first line of the run. That is a smaller routine than most people picture, and it is enough for most of what arrives.

A routine I would run on any file

Putting it in order, for a file that matters:

1. Run the preflight on the bytes. Note any BOM, NULs, control bytes, invalid sequences and mojibake patterns.

2. If it is UTF-16, decode it as UTF-16. If it is not valid UTF-8, name the encoding with two detectors and a visual check of the accented lines.

3. Keep the original. Convert to UTF-8 once, or declare the encoding to the loader if it has an option.

4. Remove NULs and stray control characters only after you know why they are there, and do it with a range that keeps tab, CR and LF.

5. Read the converted file with a strict reader, preferably the one that will load it, and compare a few known records with the source.

6. After a MySQL load, read the warnings. After any load, count rows and compare a handful of non-ASCII values.

This takes about ten minutes for a new sender and about one for a known one, and it is the reason the later steps in this section can assume a UTF-8 file. The other half is prevention: write down the encoding, the delimiter and the BOM convention in a note beside the file, as the data contract article suggests, and ask senders to export UTF-8.

Where each article fits

If you need to know the encoding first, start with detection. If you are looking at pairs of symbols, why accents turn strange explains them and the repair article fixes them. If you are looking at a BOM, read the BOM article. For conversions, see Latin 1 and Windows 1252 to UTF-8. For Excel, see opening UTF-8 in Excel. For bytes, see NUL bytes and control characters. For question marks, see where they come from. For emoji, see the MySQL case. And to find the failing line in a huge file, see checking for invalid UTF-8. The load procedures that depend on all of this are in loading a CSV into PostgreSQL and the MySQL import guide.

Costs worth knowing before you choose a repair

Three repairs are available, and they have different risks. Re-exporting from the source is the best, since it fixes the cause and keeps every character, and it is available more often than people assume, because the sender can usually run the export again with a different setting. Converting the file is second best when the source encoding is known, and it is lossless when the source is right. Repairing mojibake is third, exact when the garbling is a clean single pass and increasingly speculative the more steps the text has been through. The least attractive is hand editing in a spreadsheet, which re-saves with its own assumptions and is how many files turn from recoverable to lost.

The other cost is time. The preflight, a detector run and a conversion are minutes of work for a file you can see. At the other end, a lost character in a 3 GB file is a rerun of an export that may take hours, and a lost character in data that is not archived is gone. Spending the minutes first is the cheap side of that trade.

When the encoding is not the problem

Not every wrong looking text is an encoding fault. If the characters are right in the file and wrong in your terminal, the terminal's locale is the problem; check with xxd before touching the file. If the names are right but the sort order looks odd, that is collation, a database setting. If text is correct but a spreadsheet shows ##### or scientific notation for long numbers, that is formatting.

For a few people the better answer is a different format. A pipeline that exports from a database to another database does not need a text encoding at all; use a binary format such as Parquet, which stores strings as UTF-8 by definition, or a database to database transfer such as COPY ... BINARY, and the problem disappears. If a vendor cannot export UTF-8 and the file is hand edited in several tools, the real fix is a contract with the vendor, and the best use of this guide is the preflight script as a gate at the door. If the data is genuinely multilingual, with Japanese, Polish and Arabic in one file, then only UTF-8 carries it, and a legacy encoding is not a compromise but a loss.

The final point is the one I keep returning to. Garbled text feels like a data problem, and it is a labeling problem right up until the moment someone saves the wrong thing over the right one. Look at the bytes, keep the original, and change one thing at a time.