Write CSV Files That Open Correctly in Excel

Someone double-clicks the CSV your script produced and Excel shows Müller instead of Müller, or puts each whole line into column A, or turns order number 00412 into 412 and a 16-digit card reference into 4.53201E+15. Nothing is wrong with the file in the strict sense — Python wrote valid CSV — but Excel opens CSV files by guessing, and its guesses depend on the user's regional settings. Because the recipient will not use the import wizard, the file has to be written so that the guesses come out right. This page covers the encoding, separator, number and date choices that make a CSV open cleanly by double-click, and the cases where the honest answer is to send an .xlsx instead. It belongs to Handling Excel File Formats and Conversions.

Prerequisites

pip install pandas

The examples use one small DataFrame with the usual troublemakers: accented names, an amount with decimals, an ID with leading zeros, a long reference number, a date, and a free-text note containing a comma, quotes and a line break.

from pathlib import Path
import pandas as pd

customers = pd.DataFrame({
    "CustomerID": ["00412", "00977", "01530"],
    "Name": ["Müller GmbH", "Café Señor", "Øresund ApS"],
    "Balance": [1234.5, -88.25, 0.0],
    "Reference": ["4532015112830366", "4716123456789012", "6011000990139424"],
    "Since": pd.to_datetime(["2019-03-04", "2021-11-30", "2024-01-15"]),
    "Note": ['Pays "on time", usually', "Call before\ndelivery", ""],
})
print(customers.dtypes)

Step 1 — Write UTF-8 with a byte order mark

customers.to_csv("plain.csv", index=False)
customers.to_csv("excel.csv", index=False, encoding="utf-8-sig")
print(Path("plain.csv").read_bytes()[:12])
print(Path("excel.csv").read_bytes()[:12])

The only difference is the first three bytes: utf-8-sig writes EF BB BF, the UTF-8 byte order mark (BOM). When Excel opens a CSV by double-click it looks for that mark; without it, Windows Excel decodes the file with the legacy code page of the user's locale, which turns every ü into ü. Python's own pd.read_csv and most other tools ignore or strip the mark, so writing it costs nothing. This is the single most important setting in this page.

Step 2 — Use the separator the reader's Excel expects

Excel splits a CSV on the list separator from the operating system's regional settings. In the United States and the UK that is a comma. In most of continental Europe, where the comma is the decimal mark, it is a semicolon — and a comma-separated file opens with every row crammed into column A. If you know the audience, write for it:

customers.to_csv("excel_de.csv", index=False, encoding="utf-8-sig", sep=";", decimal=",")
print(Path("excel_de.csv").read_text(encoding="utf-8-sig").splitlines()[1])

sep=";" changes the separator and decimal="," writes 1234,5 instead of 1234.5, so German, French or Dutch Excel reads the balance as a number. Mixing them up is worse than either: a semicolon file with decimal points opens in German Excel with 1234.5 as text, or worse, as a date.

When one file must serve both kinds of user, Excel honours a first line of the form sep=;, which overrides the regional setting:

with open("excel_any.csv", "w", encoding="utf-8-sig", newline="") as fh:
    fh.write("sep=;\n")
    customers.to_csv(fh, index=False, sep=";", decimal=",")
print(Path("excel_any.csv").read_text(encoding="utf-8-sig").splitlines()[:2])

Use the hint with care: it is an Excel convention, not part of the CSV format. Other programs, including pd.read_csv, see sep=; as the header row unless told to skip it (skiprows=1), so only add it to files meant purely for people.

Step 3 — Keep leading zeros and long numbers

Excel converts anything that looks like a number. 00412 becomes 412, and a 16-digit reference becomes a floating-point number shown as 4.53202E+15 — and because Excel keeps only 15 significant digits, the last digit is replaced by a zero the moment the file is saved again. CSV has no way to mark a field as text, so the only CSV-level fix is to make the value not look like a number. The common trick is to write it as a formula that returns text:

protected = customers.assign(
    CustomerID='="' + customers["CustomerID"] + '"',
    Reference='="' + customers["Reference"] + '"',
)
protected.to_csv("excel_ids.csv", index=False, encoding="utf-8-sig")
print(Path("excel_ids.csv").read_text(encoding="utf-8-sig").splitlines()[1])

Each ID is written as "=""00412""" — CSV quoting around ="00412" — which Excel evaluates as a formula producing the text 00412. It works by double-click, but it pollutes the file for every other consumer, which now receives ="00412" as the value. Use it only for files that exist to be opened in Excel; for anything else, IDs with leading zeros are a strong reason to send an .xlsx, where the cell can simply be text — see Format ZIP Codes and IDs with Leading Zeros.

Step 4 — Write dates Excel will recognise

A datetime64 column is written as 2019-03-04 by default (or with 00:00:00 if any value has a time). ISO yyyy-mm-dd is the safest choice: Excel recognises it as a date in every locale. Writing local formats such as 04/03/2019 invites the classic day–month swap, where an American Excel reads 4 March as April 3rd:

customers.to_csv("excel_dates.csv", index=False, encoding="utf-8-sig", date_format="%Y-%m-%d")
print(Path("excel_dates.csv").read_text(encoding="utf-8-sig").splitlines()[1])

date_format controls every datetime column in one place. If times matter, "%Y-%m-%d %H:%M" is also recognised. Time zones should be converted and removed before writing; Excel has no notion of them.

Step 5 — Quote text with commas, quotes and line breaks

pandas quotes fields that contain the separator, a quote character or a line break, and doubles embedded quotes, which is exactly what Excel expects:

line = Path("excel.csv").read_text(encoding="utf-8-sig")
print(line)

The note Pays "on time", usually is written as "Pays ""on time"", usually", and the note with a line break is wrapped in quotes so the break stays inside one cell. Do not "help" by stripping quotes or setting quoting=csv.QUOTE_NONE — that is what produces rows split across lines and shifted columns. Text that starts with =, +, - or @ is a different danger: Excel executes it as a formula, which is how formula injection works; neutralise it as described in Prevent Formula Injection in Excel Exports.

Step 6 — Know when CSV is the wrong format

Everything above works around one fact: CSV carries no types, and Excel guesses. When the file must preserve text IDs, exact long numbers, real dates and look right to people in different countries, an .xlsx written with to_excel simply stores each cell's type and needs no guessing at all:

customers.to_excel("customers.xlsx", index=False)
from openpyxl import load_workbook
ws = load_workbook("customers.xlsx").active
print(ws["A2"].value, ws["A2"].data_type, "|", ws["D2"].value, ws["D2"].data_type)

The customer ID stays 00412 as text and the reference keeps all 16 digits. Send CSV when the consumer is another program or when the user explicitly asked for it; send .xlsx when a person will open the file. The trade-offs are compared in Excel vs CSV vs Parquet for Python Data Pipelines.

Common pitfalls

• Plain UTF-8 without the BOM. Valid, and garbled in Windows Excel; use utf-8-sig.
• Comma files for European users. One column per row; use sep=";" with decimal=",".
• Mixing separator and decimal mark. Decimal commas inside comma-separated files split numbers across columns.
• Expecting leading zeros to survive. They never do by double-click unless protected; better, send .xlsx.
• Windows line endings. pandas writes \n; Excel copes either way, but pass lineterminator="\r\n" if a legacy system demands CRLF.

Performance notes

to_csv is far faster than to_excel — a million rows write in seconds rather than minutes — which is one honest reason to choose CSV for very large extracts. The settings above cost nothing measurable. Remember Excel's own ceiling: 1,048,576 rows per sheet. A larger CSV opens truncated, with a warning that is easy to miss, so split big extracts or tell the recipient to load them through Power Query instead of double-clicking.

Conclusion

Write CSV for Excel with encoding="utf-8-sig", match sep and decimal to the reader's regional settings (or add the Excel-only sep= hint line), write ISO dates, let pandas quote awkward text, and protect or avoid identifiers that look like numbers. When the file must keep types for people in more than one locale, send an .xlsx instead — it removes the guessing entirely.

Frequently asked questions

Why does Excel show strange characters like ü in my CSV? The file is UTF-8 without a byte order mark, so Excel decodes it with the local code page. Write it with encoding="utf-8-sig" in pandas to_csv, which adds the mark Excel looks for.

Why does my CSV open in a single column in Excel? Excel splits on the list separator from the system's regional settings, which is a semicolon in many European locales. Write with sep=";" and decimal="," for those users, or add a first line sep=; to tell Excel which separator to use.

How do I keep leading zeros in a CSV opened in Excel? CSV cannot mark a field as text, so Excel converts 00412 to 412. Writing the value as ="00412" makes Excel show it as text, but other programs then see that formula; sending an xlsx file with the column stored as text is the cleaner fix.

What date format should I use in a CSV for Excel? Use ISO dates, yyyy-mm-dd, with date_format="%Y-%m-%d". Excel recognises them in every locale, while formats such as 04/03/2019 are read as different dates in different countries.

Related

• Up one level: Handling Excel File Formats and Conversions — moving data between formats.
• Convert CSV Files to Excel with Python — turning a CSV into a typed workbook.
• Read CSV Exports with Semicolons and Decimal Commas — the reading side of the same problem.
• Fix UnicodeDecodeError When Reading CSV Files Saved by Excel — when Excel's CSV comes back to Python.