How to Open a UTF8 CSV in Excel Without Garbled Text

Someone opens your export in Excel and the customer names read José Müller. The file is fine. Excel on Windows assumes a legacy code page for text files that give it no reason to think otherwise, and your UTF-8 file gives it none. The usual fix is three bytes at the front of the file, and this article shows how to add them, how to check them, and the alternative that leaves the file alone. The cause in general terms is in why a CSV shows strange characters.

What goes wrong

A CSV written as UTF-8 and containing any non-ASCII character is a sequence of multibyte characters. Excel, when you open the file by double clicking it, has to decide how to read those bytes. With no marker in the file, the widely reported behavior on Windows is that it uses the system's ANSI code page, which for Western locales is Windows 1252, and that turns each two byte character into two symbols. This is the mechanism explained in the strange characters article, and the symptom is the same pairs beginning with Ã.

Two details make it confusing. ASCII only files open correctly, so the problem appears the first time a customer has an accent in their name. And opening the same file through a different route works, which is why one colleague sees correct text and another does not.

Fix 1: write the file with a BOM

A UTF-8 byte order mark is the three bytes EF BB BF at the start of the file. Excel on Windows treats it as a signature meaning UTF-8. It is unnecessary in UTF-8 and harmless to Excel, though it causes its own trouble for other readers, as the BOM article shows, so add it only to files meant for Excel.

First a small checker, so the decision is mechanical. It reports whether a file has non-ASCII bytes, whether it is valid UTF-8 and whether it has the signature:

"""Say whether a CSV will open correctly in Excel on Windows by double click: non-ASCII text needs a BOM to be read as UTF-8."""
import sys

for path in sys.argv[1:]:
    raw = open(path, "rb").read()
    has_bom = raw.startswith(b"\xef\xbb\xbf")
    try:
        raw.decode("utf-8")
        utf8 = True
    except UnicodeDecodeError:
        utf8 = False
    non_ascii = any(b > 0x7F for b in raw)
    if not utf8:
        verdict = "not valid UTF-8; convert it first"
    elif not non_ascii:
        verdict = "ASCII only; opens the same either way"
    elif has_bom:
        verdict = "UTF-8 with BOM; Excel should read it as UTF-8"
    else:
        verdict = "UTF-8 without BOM and with accents; add a BOM for Excel"
    print(f"{path:16} {verdict}")

Run on four files, an ASCII one, UTF-8 with accents, the same with a BOM, and a Windows-1252 file:

python ../../needs_bom.py ascii.csv utf8.csv utf8-bom.csv cp1252.csv

ascii.csv        ASCII only; opens the same either way
utf8.csv         UTF-8 without BOM and with accents; add a BOM for Excel
utf8-bom.csv     UTF-8 with BOM; Excel should read it as UTF-8
cp1252.csv       not valid UTF-8; convert it first

The second line is the case to fix. The fourth is not valid UTF-8, so a BOM would not help; the file needs converting from Windows 1252 first. To add the BOM in Python, copy the file through the utf-8-sig codec:

python - <<'PY'
import shutil
with open("utf8.csv", encoding="utf-8", newline="") as src, open("utf8-for-excel.csv", "w", encoding="utf-8-sig", newline="") as dst:
    shutil.copyfileobj(src, dst)
PY
python ../../needs_bom.py utf8-for-excel.csv; head -c 12 utf8-for-excel.csv | xxd

utf8-for-excel.csv UTF-8 with BOM; Excel should read it as UTF-8
00000000: efbb bf69 642c 6e61 6d65 2c63            ...id,name,c

The new file begins ef bb bf 69 64, which is the BOM followed by id, and the checker agrees. If you generate the file in code, open the output with encoding="utf-8-sig" and no other change is needed.

Fix 2: import instead of double clicking

If you cannot change the file, change how it is opened. In Excel on Windows, use the Data tab and choose to get data from a text or CSV file. The import dialog shows a preview and a file origin selector, and choosing 65001: Unicode (UTF-8) makes the preview show the right characters before the data is loaded. Microsoft's article on importing and exporting text files describes the import routes and the setting that controls the delimiter. Community threads report that the origin setting sometimes does not take effect on older versions, so check the preview.

This route is the right one when the file comes from a system you do not control, or when it will be re-read by a program that dislikes the BOM. It costs the recipient a few clicks, and no one but you will remember to do it, which is why the BOM is usually the better answer for files you send.

A different way out: UTF-16

Some exporters write UTF-16 with a UTF-16 BOM instead, on the understanding that Excel on Windows reads it; I have not verified that, so treat it as reported behavior. I generated one to look at the bytes: a tab separated file written with Python's utf-16 codec.

python - <<'PY'
text = open("utf8.csv", encoding="utf-8").read().replace(",", "\t")
open("utf16-tsv.txt", "w", encoding="utf-16", newline="").write(text)
PY
file utf16-tsv.txt; head -c 8 utf16-tsv.txt | xxd

utf16-tsv.txt: Unicode text, UTF-16, little-endian text
00000000: fffe 6900 6400 0900                      ..i.d...

file recognizes it as Unicode text, UTF-16, little-endian, and the first bytes ff fe are the UTF-16 marker. It is a legitimate delivery format for Excel and a poor one for everything else: it doubles the size of ASCII text, most Unix tools choke on the null bytes, and a tab delimiter replaces the comma. I would use it only when the recipient insists.

Leading zeros and other Excel damage

A BOM fixes the characters and nothing else. Excel also converts 00501 to 501, dates to dates and long numbers to scientific notation, as the leading zeros article explains, and no encoding setting affects any of it. If your concern is a file that arrives intact in a spreadsheet, plan for both problems.

Check before you send

The cheapest protection is a gate in whatever produces the file. Run the checker from above on the output and fail when it says a file with accents lacks a BOM, if Excel users are the audience, or when it says the file is not valid UTF-8, which is a bug in the export. A file that passes still deserves a thirty second look in the application your recipients use.

If the same files go to other consumers as well, publish two variants or document the BOM. The Unicode FAQ notes that some recipients of UTF-8 do not expect a BOM, which is the polite way of saying that a parser or shell script may choke on it. The trade-off is yours: a BOM helps one audience and costs the others, so decide by who opens the files and say so in the filename or the cover note, for example orders-excel.csv and orders.csv.

What I could and could not test

I verified the bytes: a BOM file starts with EF BB BF, utf-8-sig writes it, and the checker flags files that need one. I did not verify how any particular Excel version reads the result, so I rely on the widespread reports and the import documentation for that, and I recommend you open a sample file on the oldest Excel your readers use before you ship a batch. Excel for Mac and Excel on the web may behave differently from Excel on Windows, and I have no measurements for them or for other spreadsheet programs.

Finally, if the recipients are technical, skip the problem: send the data as a format with a declared encoding, or send the CSV with a note saying UTF-8 and the import steps.