Checking a CSV for Invalid UTF8 Before You Load It

The load stops at line 2 with an invalid byte sequence, and the file is 3 GB. Before you decide what to do, you want every bad line, not just the first one, and you want to know whether there are two of them or two hundred thousand. This article shows four ways to check a file for invalid UTF-8, what each reports on the same small test file, and what the failed check does and does not tell you. The cause behind most of these is a legacy encoding, covered in detecting the encoding of a CSV file.

The most common cause

The file was written in Windows 1252 or Latin 1 and you are reading it as UTF-8. An accented letter in a legacy encoding is one byte, E9 for é and FC for ü, and neither is a valid start of a UTF-8 sequence in that position. The test file is those three lines:

id,name
1,Jos�
2,Zoe
3,M�ller

The terminal prints the invalid bytes as replacement characters, which is already a clue.

iconv: a yes or no with an offset

iconv converting UTF-8 to UTF-8 does nothing to valid input and fails on invalid input, so it doubles as a validator:

iconv -f UTF-8 -t UTF-8 bad-utf8.csv > /dev/null; echo "exit status $?"

iconv: illegal input sequence at position 13
exit status 1

It stopped at position 13 with exit status 1. Position 13 is the byte offset of the E9, counting from zero. That is enough for a pipeline gate, since it fails on the first problem, and no use for finding the rest.

isutf8: the line and the rule

isutf8, from the moreutils package, reports the line, the character and the specific rule the byte broke, and it exits with 0 on a clean file:

isutf8 bad-utf8.csv; echo "exit status $?"
isutf8 enc/utf8.csv; echo "exit status $? (clean file)"

bad-utf8.csv: line 2, char 6, byte 13: After a first byte between E1 and EC, expecting the 2nd byte between 80 and BF.
exit status 1
exit status 0 (clean file)

line 2, char 6, byte 13: After a first byte between E1 and EC, expecting the 2nd byte between 80 and BF. The E9 byte begins a three byte sequence, and the byte after it, a newline, is not a continuation byte. The second command is a clean file, which printed nothing and exited 0. Like iconv, it stops at the first error.

grep: every bad line, no extra tools

In a UTF-8 locale, grep -a -n -x -v '.*' prints lines that do not match a pattern that matches any valid line, which means the lines that are not valid UTF-8, with their numbers:

LC_ALL=C.UTF-8 grep -naxv '.*' bad-utf8.csv

2:1,Jos�
4:3,M�ller

Lines 2 and 4 are the two bad ones. The locale matters: LC_ALL=C.UTF-8 makes . mean "one UTF-8 character", and in the plain C locale it matches any byte, so every line is "valid" and the check finds nothing. This is the shortest check that lists everything. It is also the most dependent on the environment, which is why I use the next one in scripts.

A script that lists every bad line

Python's decoder reports the position and the reason, so a loop over the lines can report all of them with the offending bytes and context:

"""Report every line of a file that is not valid UTF-8, with the byte offset and the surrounding bytes."""
import sys

path = sys.argv[1]
bad = 0
offset = 0
with open(path, "rb") as f:
    for number, line in enumerate(f, start=1):
        try:
            line.decode("utf-8")
        except UnicodeDecodeError as e:
            bad += 1
            ctx = line[max(0, e.start - 6):e.end + 6]
            print(f"line {number}, byte {offset + e.start}: {line[e.start:e.end].hex()} in {ctx!r}  ({e.reason})")
        offset += len(line)
print(f"{bad} bad line(s)")
sys.exit(1 if bad else 0)

Run on the test file:

python find_bad_utf8.py bad-utf8.csv; echo "exit status $?"

line 2, byte 13: e9 in b'1,Jos\xe9\n'  (invalid continuation byte)
line 4, byte 24: fc in b'3,M\xfcller\n'  (invalid start byte)
2 bad line(s)
exit status 1

Each report has the line number, the byte offset in the file, the byte in hex, the neighborhood and the reason. E9 is flagged as an invalid continuation byte and FC, which can never start a UTF-8 sequence, as an invalid start byte. The exit status is 1 when anything is bad. It reads the file in one pass and holds one line at a time, so it works on a file larger than memory.

If the count of bad lines is a small number, the file is mostly UTF-8 with some stray Latin 1, and the likely cause is concatenated sources. If it is most of the lines that contain an accent, the whole file is a legacy encoding and every accented line is bad.

What the databases say

PostgreSQL, loading the file into a table in a UTF-8 database, stops at the first bad line and reports the bytes around it:

psql <<'SQL'
DROP TABLE IF EXISTS badload;
CREATE TABLE badload (id int, name text);
\copy badload FROM 'bad-utf8.csv' WITH (FORMAT csv, HEADER true)
SQL

DROP TABLE
CREATE TABLE
ERROR:  invalid byte sequence for encoding "UTF8": 0xe9 0x0a 0x32
CONTEXT:  COPY badload, line 2

invalid byte sequence for encoding "UTF8": 0xe9 0x0a 0x32, with COPY badload, line 2. The three bytes are the invalid E9, the newline after it and the next character, 2. The load is all or nothing, so no row was inserted. DuckDB reads the file's first bytes with its sniffer and refuses with a message that names the file and offers the fix:

duckdb -c "SELECT * FROM read_csv('bad-utf8.csv')" 2>&1 | head -8

Invalid Input Error: CSV Error on Line: 2
Original Line: id,name
Invalid unicode (byte sequence mismatch) detected. This file is not utf-8 encoded.

Possible Solution: Set the correct encoding, if available, to read this CSV File (e.g., encoding='UTF-16')
Possible Solution: Enable ignore errors (ignore_errors=true) to skip this row

  file = bad-utf8.csv

It suggests encoding='UTF-16' as an example, but the same option accepts latin-1 and others. If the check fails and you have an idea of the real encoding, tell the loader. PostgreSQL's COPY has an ENCODING option, and with 'LATIN1' the same file loaded into three rows:

psql <<'SQL'
\copy badload FROM 'bad-utf8.csv' WITH (FORMAT csv, HEADER true, ENCODING 'LATIN1')
SELECT * FROM badload;
SQL

COPY 3
 id |  name  
----+--------
  1 | José
  2 | Zoe
  3 | Müller
(3 rows)

The names come out as José and Müller, which is what the bytes were written as. That is the right ending for this problem: identify the encoding, then load with it or convert the file once.

Choosing what to do with the answer

The result of the check decides the next step. If zero lines are bad, the file is valid UTF-8 and the load error was something else, perhaps a NUL byte or a width problem. If every line with an accent is bad, the whole file is a legacy encoding, and you identify it and convert once, as in the conversion article. If a few scattered lines are bad in an otherwise clean file, two sources were concatenated, and the right move is to split the file at the boundary, convert the bad part and rejoin.

The PostgreSQL manual's page on character set support explains the relation between the server encoding, the client encoding and the ENCODING option, and it is the place to check before you assume a database setting is at fault. For the check itself, the GNU iconv documentation, mirrored in the Linux man page, lists the exit statuses.

What these checks do not prove

A passing check proves that the bytes are valid UTF-8, nothing more. An ASCII-only file passes under any encoding. A Windows 1252 file whose accented letters happen to form valid UTF-8 sequences, which is rare but possible with certain character pairs, passes and is still wrong. And a file that has been garbled into valid UTF-8 mojibake, with é where é belongs, passes perfectly, because é is a valid string. If your concern is the correctness of the text, check a few known records by eye, as the mojibake article describes.

A failing check is the cue to find the encoding. Deleting the offending lines loses records, and replacing the bytes with ? loses characters, as the question mark article explains. Both are last resorts.