European Number Format in a CSV (Comma Decimals, Dot Thousands)
The quick answer: A file that shows 1.234,56 uses the European convention — a dot for thousands and a comma for the decimal — while US/UK tools expect the opposite, 1,234.56. A US/UK-configured Excel swaps the roles of the two marks and misreads the values: 1,5 (one-and-a-half) becomes 15 or lands as text, and 1.234,56 is mangled. Nothing is corrupt — the two tools disagree about which character is the decimal point. Fix it three ways: tell Excel the source locale on import, temporarily switch Excel’s own separators to match, or rewrite the marks in the number columns. This is a value-level locale mismatch, distinct from a CSV that opens all in one column and from dates in the wrong order.
What a US/UK tool actually reads
Every misread traces to one swap: your file’s decimal is a comma, but Excel treats commas as thousands groupers and dots as decimals. Each value fails predictably, so seeing them side by side tells you instantly whether a column has been mangled.
| What the file shows | A US/UK tool reads it as | It actually means |
|---|---|---|
1,5 | 15, or kept as text | 1.5 |
1.234,56 | mangled / rejected | 1234.56 |
1.000 | 1 | one thousand |
10,00 | 1000, or text | 10.00 |
The most dangerous row is 1.000: read as a decimal it collapses to 1, a thousand-fold error. Because the result still looks like a number, a spreadsheet absorbs a whole column of these with no warning triangle — which is why the bug survives to the next report.
Why it happens
A CSV carries no locale. It is plain text with no field that says “these numbers use a comma decimal,” so whichever program opens it applies its own regional settings. Many European locales — German, French, Spanish, Italian, Dutch and more — write the decimal as a comma and group thousands with a dot. When such a file reaches a machine set to English (US or UK), the marks flip meaning. The file did not change; the reader did — the same file on a German-configured PC displays perfectly, which is why the sender insists “it works on my end.”
Fix 1: set the source locale on import
The safest fix tells Excel the file’s home locale, so it parses the numbers correctly without you touching the data. Use the query import path, not a double-click:
- In Excel open Data → From Text/CSV and select the file.
- In the preview window click Transform Data to open the Power Query editor.
- Right-click the number column and choose Change Type → Using Locale.
- Set Data Type to Decimal Number and pick the source region, for example German (Germany), then OK.
- Click Close & Load.
1.234,56now arrives as1234.56, displayed in your own locale.
This is the honest first choice: it never rewrites the file, only interprets it, so text columns and IDs are left untouched. Repeat the step for each numeric column.
Fix 2: temporarily match Excel’s separators
If you only need to read the file once, switch Excel’s separators to match it, then switch them back. Go to File → Options → Advanced, untick “Use system separators,” set Decimal separator to a comma and Thousands separator to a dot, and click OK. The file now opens correctly. Change both settings back afterwards — they affect every other file and formula you open. This is quick but blunt: a viewing fix, not a conversion, and it makes your normal US/UK files display wrongly until you reverse it.
No one-click button for this one — and we won’t pretend otherwise
Converting comma-decimals to dots safely means telling a parser which columns are numbers, so the reliable route is Fix 1 above: import with Data → From Text/CSV and Change Type → Using Locale. If the same file also arrived with the wrong field delimiter or a broken encoding, our free delimiter converter and encoding checker fix those locally — nothing uploaded.
Use the locale import (Fix 1) →Fix 3: rewrite the marks in the file
When you need the file itself to be US/UK-correct — to feed a database, an API or a colleague’s tool — normalize the marks, but only in the number columns. Order matters: first delete the dot thousands separators, then replace the decimal comma with a dot. Reversing it turns 1.234,56 into a broken 1.234.56.
The one that bites: a blind, whole-file find-and-replace of every . and , corrupts the data. If any text field holds a comma (an address, a note) or a dot (a URL, an abbreviation), the swap scrambles it; and if the file mixes formatted and unformatted numbers, some rows come out wrong while others look fine. Scope the change to the numeric columns, and verify a sample — confirm a known “one thousand” reads as 1000, not 1 — before trusting the file. Where you can, prefer Fix 1, which parses without rewriting anything.
A related trap: numbers Excel already imported as text because the comma looked non-numeric. Converting the marks fixes the format, but the cells may stay left-aligned; see number stored as text in Excel to turn those back into real numbers.
This is not the one-column problem
Keep three separate locale bugs apart, because each is fixed in a different place. This guide is about the values inside correctly-split columns. If instead every field is crammed into column A, that is a delimiter mismatch, covered in CSV all data in one column. If the numbers are fine but the dates read as the wrong day, that is the date-order cousin in Excel changed my CSV date format. Files from a comma-decimal locale often carry all three, since the same settings that flip the decimal also switch the delimiter to a semicolon and reorder the date. Fix them in that order — delimiter, then values, then dates — re-checking a sample after each.
Frequently asked questions
Why are my European numbers wrong in Excel?
The file uses a comma decimal and dot thousands (1.234,56), but your Excel is set to the US/UK convention where those roles are reversed, so it reads the marks the wrong way round — 1,5 becomes 15 or text. The file is not corrupt; the tools disagree about which character is the decimal point. Fix it by setting the source locale on import, matching Excel’s separators, or rewriting the marks in the number columns.
How do I convert 1.234,56 to 1234.56?
Cleanest is to import with the source locale: Data → From Text/CSV → Transform Data, right-click the number column, Change Type → Using Locale, then choose Decimal Number and the source region. To change the raw file instead, first remove the dot thousands separators, then swap the decimal comma for a dot — only in the number columns, never as one global replace.
Is this the same as the CSV opening in one column?
No. The one-column problem is a delimiter mismatch — Excel splits rows on the wrong separator so every field lands in column A. This is a value mismatch: the columns split correctly, but the numbers inside are misread because the decimal and thousands marks are swapped. They often arrive together, but are fixed in different places.
Can I just find-and-replace the commas and dots?
Only carefully. A blind global swap of every . and , corrupts the file if any text field contains a comma or a dot, or if the file mixes number formats. Restrict the change to the numeric columns, do it in two ordered steps (strip dot thousands, then comma to dot), and verify a sample first.
Sources: Microsoft’s guidance on importing text and CSV files, and a worked case of the separator mismatch in the Microsoft Q&A community. The comma-decimal, dot-thousands convention is standard across many European locales.