Read European CSV Exports with Semicolons and Decimal Commas

A CSV export from a German, French, Dutch or Scandinavian system arrives, and pd.read_csv either fails outright or produces something odd: a UnicodeDecodeError on the first ü, a single column holding entire lines, amounts like 1.234,56 stuck as text. The file is fine. It follows the conventions of a locale where the comma is the decimal separator — so fields are separated by semicolons, numbers use a decimal comma and a dot for thousands, and the text is often in a Windows code page rather than UTF-8. pandas can read all of it, with the right options, and one of those options has a side effect that silently corrupts other columns. This page, part of Handling Excel File Formats and Conversions, reads such a file correctly and writes it to Excel.

Prerequisites

pip install pandas openpyxl

export.csv is encoded in Windows-1252, separates fields with semicolons, quotes one customer name that contains a semicolon, and writes dates as DD.MM.YYYY, amounts with a decimal comma and thousands dots, postcodes with leading zeros, and a version string.

Step 1 — Fix the encoding and the separator

The default read fails before it gets to the data:

import pandas as pd

try:
    pd.read_csv("export.csv")
except UnicodeDecodeError as exc:
    print("UnicodeDecodeError:", exc.reason)

raw = pd.read_csv("export.csv", sep=";", encoding="cp1252")
print(raw.dtypes.to_dict())
print(raw)

encoding="cp1252" reads the umlauts; sep=";" splits the columns, and the quoted "Müller; Sohn GmbH" stays in one cell because the CSV parser respects quotes. Files saved as "CSV UTF-8" by newer Excel versions need encoding="utf-8-sig" instead, which also strips the byte-order mark that would otherwise end up glued to the first column name. The amounts, dates and version are still text, and the postcode 01067 has become the integer 1067.

Step 2 — Parse the numbers, and see what thousands does to everything else

decimal="," tells pandas the comma is the decimal separator, and thousands="." that dots group thousands. Together they turn 1.234,56 into 1234.56. But the thousands option is not limited to the amount column — pandas applies it to every column it tries to read as a number:

naive = pd.read_csv("export.csv", sep=";", encoding="cp1252", decimal=",", thousands=".")
print(naive.dtypes.to_dict())
print(naive[["Datum", "Betrag", "Version"]])

Betrag is right. Datum is now the integer 31012026, and Version has become 152 and 20 — the dots were removed as thousands separators and the remaining digits parsed as numbers. No warning is printed. A report built on this file would show nonsense dates, or sort versions wrongly, and nothing in the job's log would say why.

Step 3 — Protect text-like columns with dtype and parse dates explicitly

The fix is to tell pandas which columns are not numbers. Give text-like columns — postcodes, versions, IDs — a str dtype, and parse the date column as a date in the same call so it is never treated as a number:

df = pd.read_csv(
    "export.csv", sep=";", encoding="cp1252",
    decimal=",", thousands=".",
    dtype={"PLZ": str, "Version": str},
    parse_dates=["Datum"], dayfirst=True,
)
print(df.dtypes.to_dict())
print(df)

Every column is now what it should be: Betrag a float, Menge an integer, Datum real dates, PLZ and Version text with their leading zeros and dots intact. dayfirst=True makes 01.02.2026 the first of February rather than the second of January. For stricter parsing, read the date column as text and convert it with an explicit format — pd.to_datetime(df["Datum"], format="%d.%m.%Y") — which raises on any value that does not match instead of guessing.

Step 4 — Or convert the amount columns yourself

When a file has many text columns and few numeric ones, it is simpler to leave thousands out entirely and convert just the amount columns. A small function makes the rule explicit and fails loudly on values that are not amounts:

def de_number(text):
    if pd.isna(text) or str(text).strip() == "":
        return None
    return float(str(text).strip().replace(".", "").replace(",", "."))

safe = pd.read_csv("export.csv", sep=";", encoding="cp1252", dtype=str)
safe["Betrag"] = safe["Betrag"].map(de_number)
safe["Menge"] = safe["Menge"].astype(int)
safe["Datum"] = pd.to_datetime(safe["Datum"], format="%d.%m.%Y")
print(safe.dtypes.to_dict())

Reading everything as str first means nothing is inferred behind your back; each conversion is a line you can see, test and change. This is the approach to prefer for scheduled jobs, where a column added to the export next month should not quietly change type. Convert Currency Strings to Numbers with pandas covers amounts with currency symbols and brackets as well.

Which of the two approaches to use depends on the shape of the file more than on taste. The built-in options are shorter and faster; the read-as-text approach is more predictable. For a one-off conversion of a file you can inspect, Step 3 is fine. For a job that will read a new export every night, choose Step 4:

Step 5 — Detect the separator when files vary

If exports arrive from several systems, some with commas and some with semicolons, detect the separator instead of hard-coding it. Python's csv.Sniffer looks at a sample and guesses:

import csv

with open("export.csv", encoding="cp1252", newline="") as fh:
    dialect = csv.Sniffer().sniff(fh.read(4096), delimiters=";,\t")
print(repr(dialect.delimiter))

sniffed = pd.read_csv("export.csv", sep=dialect.delimiter, encoding="cp1252", dtype=str)
print(sniffed.columns.tolist())

Restricting delimiters to the realistic candidates makes the guess more reliable. pd.read_csv(sep=None, engine="python") performs a similar sniff internally. Log the detected separator, so a wrong guess is visible when a file with an unusual first line comes through.

Step 6 — Write the result to Excel

Once the values are real numbers and dates, writing them to Excel is ordinary — and the reader's Excel displays them in the reader's locale, decimal commas included, because the workbook stores numbers, not text:

with pd.ExcelWriter("export.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, index=False, sheet_name="Export")
    ws = writer.sheets["Export"]
    for (cell,) in ws.iter_rows(min_row=2, min_col=3, max_col=3):
        cell.number_format = "#,##0.00"
    for (cell,) in ws.iter_rows(min_row=2, min_col=2, max_col=2):
        cell.number_format = "DD.MM.YYYY"

back = pd.read_excel("export.xlsx", dtype={"PLZ": str, "Version": str})
print(back.dtypes.to_dict())

#,##0.00 is a locale-neutral code: a German Excel shows 1.234,56, an English one 1,234.56, from the same cell. That is the real advantage of converting CSV to a workbook — the file stops depending on the reader's regional settings.

Going the other way: CSV for European Excel users

When a colleague in one of these locales needs a CSV rather than a workbook, write it in their conventions so Excel opens it straight into columns:

df.to_csv("for_excel_de.csv", sep=";", decimal=",", index=False, encoding="utf-8-sig", date_format="%d.%m.%Y")
print(open("for_excel_de.csv", encoding="utf-8-sig").read().splitlines()[1])

utf-8-sig writes the byte-order mark that tells Excel the file is UTF-8, so umlauts survive; without it, Excel may decode the file as the local code page. Convert Excel to CSV with Python covers encodings and quoting for CSV output in general.

Common pitfalls

• thousands="." without protecting other columns. Dates, versions and dotted codes are turned into integers without warning.
• Guessing the encoding. Try utf-8-sig first, then cp1252; a decode error names the byte that failed.
• Month-first dates. 01.02.2026 is 1 February in these locales; pass dayfirst=True or an explicit format.
• Postcodes as numbers. Give them a str dtype or 01067 becomes 1067.

Performance notes

The C parser handles sep, decimal and thousands natively and reads millions of rows quickly. sep=None switches to the slower Python engine, so sniff once with csv.Sniffer and pass the result. Converting columns with map is slower than the built-in options on very large files, but only the amount columns need it, and correctness is worth the seconds.

Conclusion

European CSV exports need sep=";", the right encoding (cp1252 or utf-8-sig), decimal="," and usually thousands=".". Because thousands applies to every column pandas treats as numeric, protect text-like columns with dtype=str and parse dates in the same call — or read everything as text and convert the amount columns yourself. Write the result to Excel with locale-neutral number formats, and write CSV back with sep=";", decimal="," and utf-8-sig for readers who need it.

Frequently asked questions

Why do European CSV files use semicolons? In locales where the comma is the decimal separator, a comma cannot also separate fields without quoting every number. Excel in those locales saves CSV with semicolons instead, and many ERP systems follow the same convention.

Is it safe to pass thousands="." to read_csv? Only for columns that really are numbers. pandas applies it to every column it tries to parse as numeric, so dates such as 31.01.2026 become 31012026 and version strings such as 2.0 become 20. Give text-like columns a str dtype, or convert amount columns yourself.

What encoding do these files use? Usually Windows-1252 (cp1252) when saved by older Excel versions or ERP exports, or UTF-8 with a byte-order mark (utf-8-sig) when saved as "CSV UTF-8". A UnicodeDecodeError on the first umlaut means you guessed wrong.

How do I write a CSV that European Excel opens correctly? Use to_csv with sep=";", decimal="," and encoding="utf-8-sig". The byte-order mark tells Excel the file is UTF-8, and the semicolons and decimal commas match what Excel expects in those locales.

Related

• Up one level: Handling Excel File Formats and Conversions — every format and conversion.
• Convert CSV Files to Excel with Python — the general CSV-to-workbook pipeline.
• Parse Excel Dates into Python datetimes with pandas — date formats in depth.
• Keep Leading Zeros When Reading Excel with pandas — the same dtype rule for workbooks.