Excel CSV UTF8 Versus CSV Comma Delimited Explained

The Save As list in Excel has five entries that all end in .csv or look like it, and the names do not tell you which one keeps your data. Pick the wrong one and you find out a week later, when a customer called Łukasz has become ?ukasz in the system that received the file, and nothing in the export warned you. The loss is permanent, because the question mark is written into the file as a real character. This article lays out what each option writes, so the choice is a decision and not a guess. For the damage on the reading side, see opening CSV files in Excel and Sheets without damage.

The five formats

Here is what each one writes. The details that matter are the encoding, the byte order mark and the line ending.

Only the first is safe for text in more than one language, and only the first and the last can hold characters outside Western European alphabets at all. The other three exist so that software from the 1990s can still read an export.

What plain CSV does to your text

The legacy format does not fail loudly. It maps each character to a byte in the code page, and where no mapping exists it writes a question mark. You can reproduce it without Excel, because the operation is a plain encode:

s = "Café ☕ Łódź"
b = s.encode("cp1252", errors="replace")
print(b)
print(b.decode("cp1252"))

b'Caf\xe9 ? ?\xf3d?'
Café ? ?ód?

The é and the ó survive, because Windows-1252 has them. The coffee cup, the Ł and the ź are gone, replaced by ?, and the file now holds the byte 3F where each of them was. No later conversion brings them back. Whether the loss matters depends on your data, and an address list with Polish, Czech, Turkish, Greek, Cyrillic or CJK names will lose some in the first row that has one. Why a CSV has question marks where names should be walks through diagnosing a file that has already been through this.

The reason people hit it is the menu order. Plain "CSV (Comma delimited)" has been in Excel for decades and appears first in the list and in muscle memory. The UTF-8 entry was added later and sits below it. Choosing by habit is the usual route to the loss.

The comma in the name is not a promise

Both CSV entries say Comma in their label, and in practice Excel writes the list separator of the operating system's regional settings. On a machine in the United States that is a comma. On a machine in Germany, France, Spain or Italy it is a semicolon, and the file saved as "CSV UTF-8 (Comma delimited)" has semicolons in it. This is why a colleague abroad sends a "comma-delimited" file that opens as one column on your machine (opening a semicolon separated CSV in Excel).

If you must produce commas from a machine set to semicolons, change the list separator in the Windows region settings while you export, or write the file from a script that names the delimiter. A one-off conversion of an existing file takes a line with the csv module, or with DuckDB's COPY ... (DELIMITER ','), and a script has the advantage that it does not depend on whose laptop ran it.

Why UTF-8 carries a byte order mark

The first three bytes of a CSV UTF-8 file are EF BB BF, the encoding of the character U+FEFF. The Unicode standard allows it at the start of UTF-8 text, where it carries no byte-order meaning and serves as a signature. Excel writes it because it has no other way to recognize a UTF-8 file: without a signature it reads a double-clicked CSV in the legacy code page, and accents become é. Writing the mark is the reason a UTF-8 file saved by Excel reopens correctly in Excel.

It is also the cause of the most common complaint about it. Tools that do not expect the mark treat it as part of the first field, so the header id becomes id, a column that looks like id and is not equal to it. Python fixes it with encoding="utf-8-sig", pandas with the same, and sed '1s/^\xEF\xBB\xBF//' strips it in a pipeline. The UTF-8 BOM in CSV files lists which readers cope and which do not. The two-sided answer is to keep the mark for files that people open in Excel, and to remove it for files that programs read.

The other three

Unicode Text is not a CSV. It writes UTF-16 with tab separators and a BOM. A tab-separated file is a fine thing (TSV versus CSV), and the UTF-16 encoding is what breaks it for anything but Excel and a few Windows tools, since two bytes per character fill the text with null bytes when read as bytes. Use it only when you need both tabs and full Unicode in an environment that expects that pairing.

CSV (Macintosh) writes the old Mac encoding and, in the documented behavior, carriage-return-only line endings. A modern parser that expects CRLF or LF sees one very long line. It exists for software that predates OS X.

CSV (MS-DOS) writes the OEM code page, with characters different from Windows-1252 in the upper half of the byte range, so accented letters change into box-drawing glyphs when read as Windows text. Both are legacy formats. If a vendor's specification asks for one, ask whether they accept UTF-8, since in nearly every case they do.

A decision in three questions

Who reads the file next? If a person opens it in Excel, choose CSV UTF-8. The mark makes the encoding automatic and the text survives a round trip. If a program reads it, choose UTF-8 as well, then drop the mark in the pipeline or in a script as the first step. If the receiver is a legacy system that insists on a code page, find out which one, and write it with a script that fails on an unmappable character instead of silently writing a question mark: s.encode("cp1252") without errors="replace" raises UnicodeEncodeError and tells you which character did not fit.

What delimiter do they expect? Check the machine's region setting before you save, or script the export. Is there any chance of mixed data across languages? Then UTF-8, always. The one situation where plain CSV Comma delimited wins is when the receiver is on a fixed legacy code page and the data is guaranteed to fit it.

When the plain CSV is the right choice

Use the legacy format when the receiving system documents it and cannot be changed: an old accounting package that reads Windows-1252 and chokes on a BOM, a fixed import job whose parser was written in 2005. In that case, the safeguard is a test, not the format. Write one row containing your hardest characters, run the file through the receiver, and read what came back. Keep a copy of the UTF-8 original so that the legacy file is a derived artifact you can regenerate once the receiver is fixed.

The standard behind all this is thin. RFC 4180 registers text/csv with a charset parameter and defaults to US-ASCII, and the Unicode FAQ on byte order marks explains what the signature is for. For checking what a file actually contains, detecting the encoding of a CSV file gives the commands.