How to handle international number and currency formats in a CSV (1,234.56 vs 1.234,56)
Why '1.234,56' isn't a typo — it's the standard decimal format in much of Europe — and how to parse mixed-locale numeric CSV data safely.
Before you work through this by hand: you can check your own CSV free and see which of these problems your file actually has — duplicate rows, near-duplicates, broken quoting, encoding damage. It runs in your browser tab, nothing is uploaded, and there's no signup or email.
Two conflicting conventions
The US/UK convention uses a comma as a thousands separator and a period as the decimal point: "1,234.56" means one thousand two hundred thirty-four point five six. Much of continental Europe and Latin America does the reverse: "1.234,56" means the same number, with the period as the thousands separator and the comma as the decimal point. Reading a file with one convention using the other convention's rules doesn't error — it silently produces a wrong number (often off by a factor of 1000, or truncated at the wrong point).
Detecting which convention a file uses
If a numeric-looking column always has exactly one comma or period near the end (2-3 digits after it) and none elsewhere, that's usually the decimal separator; a comma or period appearing at a fixed 3-digit interval further left is the thousands separator. When in doubt, check a source-system locale setting rather than guessing from the data alone, the same way you would for ambiguous date formats.
Parsing with pandas once you know the convention
import pandas as pd; df = pd.read_csv('file.csv', thousands='.', decimal=',') — explicitly telling pandas which characters are the thousands separator and decimal point avoids it guessing (and avoids the default US-style assumption silently misparsing European-formatted numbers).
Currency symbols and codes add another layer
A value like "€1.234,56" or "$1,234.56" needs the currency symbol stripped before numeric parsing, and if a file mixes currencies (a currency-code column alongside a plain numeric-amount column), keep both columns rather than trying to normalize to one currency yourself — exchange-rate conversion is a business decision, not a data-cleaning one.
Same principle as our other normalization guides
As with case and phone-number normalization: parse into a clean, consistent numeric type for calculations, but keep the original formatted string around if you need to reproduce exactly what the source system showed.
See CSV Repair & Deduper (offline browser tool) — $19 one-time