Converting a CSV From Latin 1 or Windows 1252 to UTF8

Most of the legacy CSV files I meet are Windows-1252, written by a Windows program that never heard of UTF-8, and they need to become UTF-8 before anything modern will read them. The conversion is one command. The care goes into choosing the right source encoding, because Latin 1 and Windows 1252 look identical for most text and differ in exactly the characters that show up in prices and quotes. The first step, finding out which encoding you have, is in detecting the encoding of a CSV file.

What you need

The original file, a place to write the converted copy, and a reasonable belief about the source encoding. Keep the original. A conversion is lossless only when the source encoding is right, and you want a way back if it was not.

The conversion with iconv

This is the file converted, and the first thing I check afterwards: that file reports UTF-8 and that the accents read correctly.

cd enc && iconv -f WINDOWS-1252 -t UTF-8 cp1252.csv > /tmp/cp1252-utf8.csv && file -i /tmp/cp1252-utf8.csv && cat /tmp/cp1252-utf8.csv

/tmp/cp1252-utf8.csv: text/csv; charset=utf-8
id,name,city,note
1,José Müller,São Paulo,price €4.50
2,Zoë Dupré,Zürich,“smart quotes”
3,Åsa Öberg,Malmö,café

iconv takes the source encoding with -f, the target with -t, and reads standard input if no file is given. The euro sign, which is the single byte 80 in the source, became the three bytes E2 82 AC. Everything else came across unchanged in appearance.

The conversion in Python

When you are already parsing the file, convert as part of the read and write. Reading with encoding="cp1252" and newline="" and writing with encoding="utf-8" keeps the quoting logic in the CSV module and fixes the encoding on the way through:

cd enc && python - <<'PY'
import csv
with open("cp1252.csv", newline="", encoding="cp1252") as src, open("/tmp/py-utf8.csv", "w", newline="", encoding="utf-8") as dst:
    csv.writer(dst, lineterminator="\n").writerows(csv.reader(src))
print(open("/tmp/py-utf8.csv", encoding="utf-8").read().splitlines()[1])
PY
file -i /tmp/py-utf8.csv

1,José Müller,São Paulo,price €4.50
/tmp/py-utf8.csv: text/csv; charset=utf-8

Setting lineterminator is a choice, as the line endings article explains, and it is the only other thing that changed.

Latin 1 or Windows 1252

The two encodings assign the same characters to every byte except the range 80 to 9F. Windows 1252 puts printable characters there, such as the euro sign at 80 and curly quotes at 93 and 94, and ISO 8859 1 puts control characters there. Here are the bytes a Windows program writes for the text "smart" €4.50, decoded both ways:

python - <<'PY'
raw = bytes([0x93, 0x73, 0x6D, 0x61, 0x72, 0x74, 0x94, 0x20, 0x80, 0x34, 0x2E, 0x35, 0x30])   # "smart" and a euro price, as Windows writes them
print("bytes     :", raw.hex(" "))
print("cp1252    :", raw.decode("cp1252"))
print("latin-1   :", repr(raw.decode("latin-1")))
PY

bytes     : 93 73 6d 61 72 74 94 20 80 34 2e 35 30
cp1252    : “smart” €4.50
latin-1   : '\x93smart\x94 \x804.50'

Decoded as Windows 1252 the text is correct. Decoded as Latin 1 it contains \x93, \x94 and \x80, which are invisible control characters that nothing complains about. A file converted from the wrong one of the two looks fine until someone notices that the prices have no currency sign and the quotes are missing. If the file came from Windows, use cp1252. If it is a Unix or database export that says ISO-8859-1 in its documentation, use Latin 1, and expect no characters in the 80 to 9F range at all.

One warning for Python: the latin-1 codec accepts every possible byte, so it never raises an error. That makes it a good tool for inspection and a bad one for conversion, since it hides a wrong guess.

Browsers resolve the same ambiguity by decree. The WHATWG Encoding Standard treats the label iso-8859-1 as Windows 1252, because pages labeled Latin 1 so often contain Windows characters. If a colleague says the file "looks fine in Chrome" and breaks in your script, that is the reason: the browser applied Windows 1252 and your script applied strict Latin 1.

Converting a folder of files

When a sender delivers a month of exports in one encoding, loop over them and write the converted copies elsewhere, so the originals stay put. This is the whole thing for two files, with file -i as the check at the end:

mkdir -p /tmp/legacy /tmp/converted
cp cp1252.csv latin1.csv /tmp/legacy/
for f in /tmp/legacy/*.csv; do
  iconv -f WINDOWS-1252 -t UTF-8 "$f" > "/tmp/converted/$(basename "$f")"
done
file -i /tmp/converted/*.csv

/tmp/converted/cp1252.csv: text/csv; charset=utf-8
/tmp/converted/latin1.csv: text/csv; charset=utf-8

Both converted files report charset=utf-8. In a real run I would stop the loop at the first failure by adding set -e, so that a single file with an undefined byte is noticed and not skipped. The man page for iconv lists the options, including //TRANSLIT and //IGNORE for the target encoding, which are the ways of being lossy on purpose. iconv streams, so file size is not a limit; a 3 GB file converts in the time it takes to read and write it.

Bytes that Windows 1252 does not define

Five byte values, 81, 8D, 8F, 90 and 9D, have no character in Windows 1252. They show up in files that were once converted badly, and iconv refuses them:

printf 'caf\xe9 \x81 end\n' > /tmp/undefined.csv
iconv -f WINDOWS-1252 -t UTF-8 /tmp/undefined.csv; echo "exit status $?"
iconv -f WINDOWS-1252 -t UTF-8 -c /tmp/undefined.csv | xxd | head -2
python -c "print(open('/tmp/undefined.csv','rb').read().decode('latin-1').encode('utf-8'))"

café iconv: illegal input sequence at position 5
exit status 1
00000000: 6361 66c3 a920 2065 6e64 0a              caf..  end.
b'caf\xc3\xa9 \xc2\x81 end\n'

The strict run stopped with illegal input sequence at position 5 and exit status 1, the position being the byte offset of the 81. With -c it drops the offending byte and carries on, which silently deletes data. The last line shows the Python route that never fails: decoding as Latin 1 maps 81 to the control character U+0081, and the result is a UTF-8 file with a stray control character in it. Neither answer is right, because the byte has no meaning. Find where it comes from, fix the source, and in the meantime replace it with a visible marker you can search for.

Check that nothing changed

Compare the decoded text of the original with the decoded text of the result. Byte counts will differ, since the euro sign grew from one byte to three, and that is expected. The text must not:

cd enc && python - <<'PY'
before = open("cp1252.csv", "rb").read().decode("cp1252")
after = open("/tmp/cp1252-utf8.csv", "rb").read().decode("utf-8")
print("same text after conversion:", before == after, "| bytes", len(open("cp1252.csv","rb").read()), "->", len(open("/tmp/cp1252-utf8.csv","rb").read()))
PY

same text after conversion: True | bytes 111 -> 127

The file went from 111 to 127 bytes and the decoded text is identical. That comparison catches the wrong source encoding when the wrong choice produces different characters, which it does for anything outside plain Latin letters. It does not catch a Latin 1 versus Windows 1252 mix-up on text that has none of the 32 differing characters, because then the two are the same.

When the file is neither of these

If uchardet says Shift_JIS, GBK, cp1250 or a Cyrillic table, the same two commands apply with a different source name; iconv -l lists the names it accepts. If the file is already UTF-8 and merely displays wrongly, converting it again will damage it: it turns é into é for good. Test first by decoding as UTF-8 and checking that nothing is lost, as the mojibake article explains. For mixed files, where some lines are UTF-8 and others are Windows-1252, no single conversion works, and you need a line by line approach, with the check in validating a CSV for invalid UTF-8 to find the boundary.

Finally, if a downstream system accepts only a legacy encoding, convert the other way. iconv -f UTF-8 -t WINDOWS-1252//TRANSLIT approximates characters it cannot represent, and the result is lossy, so the question marks article is the warning label for that direction.