Convert European Number Formats (1.234,56) to US Format in CSV Files

You got a CSV from a European supplier or partner. The numbers look reasonable: 1.234,56 for one thousand two hundred thirty-four euros and fifty-six cents. You import it into your US-locale software, and now you owe €1.23 on every order instead of €1,234.56. Or worse, the import silently succeeds and you don't notice until the accounting reconciliation flags discrepancies. European and US number formats are nearly identical visually — and that's exactly what makes them dangerous when they cross borders.

Why the Formats Differ

The US convention uses a comma as a thousands separator and a period as a decimal separator: 1,234.56. Most of continental Europe (Germany, France, Spain, Italy, Netherlands, etc.) uses the opposite: a period as a thousands separator and a comma as a decimal separator: 1.234,56. The UK and Ireland use the US convention. Switzerland and some Scandinavian countries use a third format (apostrophe or space as thousands separator). The format is set by your operating system's locale, not your file format, which is why CSVs cross borders cleanly until you actually look at the numbers.

Why This Is Hard to Get Right

If you just do a find-replace "swap commas and periods," you'll mangle dates, addresses, and other comma-containing fields. The conversion has to be column-aware: only the numeric columns get converted, and only the numeric values within those columns.

The ambiguous case

Some numbers are ambiguous between the two formats. "1,234" could be one thousand two hundred thirty-four (US format) or one-point-two-three-four (European format). "1.234" has the same ambiguity. The only way to disambiguate is to know the source locale or look at the rest of the column for clues (most-decimal-places, most-thousands-grouping).

Ambiguous in isolation:
1,234       <- US: 1234   European: 1.234
1.234       <- US: 1.234  European: 1234
1,234.56    <- US: 1234.56 (European parser fails)
1.234,56    <- European: 1234.56 (US parser fails)

Disambiguating clues:
- If you see commas AND periods in the same number, the comma is decimal in European format and thousands in US format
- If most values have exactly 2 decimal places (currency), the decimal separator is whichever character is in that position

Where European Numbers Break

Accounting software

QuickBooks, Xero, Sage — all expect the locale-correct format for the account. If your QuickBooks is set to US locale and you import European-formatted CSVs, every number is wrong. Xero is locale-flexible (set per organization), but the CSV must match the organization's locale.

Excel auto-conversion

Excel respects your system locale when opening CSVs. A US-locale Excel opening a European CSV will treat 1.234,56 as text (because it doesn't parse), but some cells may get auto-interpreted differently — Excel might decide that 1.234 is January 234th and turn it into a date. This is a frequent source of silent data corruption.

Databases and ETL pipelines

Postgres COPY, MySQL LOAD DATA, and BigQuery imports all expect a single canonical numeric format (typically 1234.56, no thousands separator). They'll error out or coerce European numbers to zero, depending on how strict the column type is.

How to Convert: Step by Step

Step 1: Identify the numeric columns

Don't blanket-convert. Identify which columns hold numbers (Price, Amount, Quantity, Total, etc.) and which hold text that might contain commas/periods (Address, Description, Date in DD.MM.YYYY format). Only the numeric columns get the conversion.

Step 2: Confirm the source locale

Spot-check 10-15 rows in the numeric columns. If you see periods being used as thousands separators (1.234) consistently, it's European. If commas are used as thousands separators (1,234), it's US. If both appear (1.234,56), it's European with thousands; (1,234.56) is US with thousands. Don't assume — check.

Step 3: Apply the conversion

The simplest approach for the European → US case: first remove all periods (which are thousands separators), then replace the comma with a period (which becomes the decimal separator). Order matters — if you swap the period and comma simultaneously, you can't tell which was which.

European to US conversion (in order):
1.234.567,89
    ↓ step 1: remove periods
1234567,89
    ↓ step 2: replace comma with period
1234567.89

Better (preserves thousands grouping):
1.234.567,89
    ↓ step 1: replace periods with placeholder
1¤1¤2¤3¤4 (placeholder)
    ↓ step 2: replace comma with period
1¤1¤2¤3¤ .89
    ↓ step 3: replace placeholder with comma
1,234,567.89

Step 4: Strip currency symbols

European CSVs often have the € symbol embedded in the price column (€19,99 instead of just 19,99). Most accounting and ETL systems can't parse currency symbols inside numeric fields. Strip them before conversion. The currency is typically the same throughout the file, so you can record it in a separate Currency column if needed.

Step 5: Verify

After conversion, eyeball-check 10-20 values to make sure they look right. Sum a column and compare to the original (use a calculator that respects the source locale). If the sums match, the conversion worked. If they don't, you've corrupted some values.

The Date Trap

European-format files often pair with European date formats: DD.MM.YYYY (Germany) or DD/MM/YYYY (France, Spain). Convert numbers and you still have wrong dates. The reverse is also true — converting dates without converting numbers leaves currency in the wrong format. Treat numeric and date columns as two independent passes of the cleanup, both of which need to happen.

Date format crossover

Regex Pitfalls

If you're using regex find-replace to do the conversion, be careful. A naive pattern like "swap , and ." will break IP addresses, version numbers, file paths, and decimals-of-decimals. Always anchor to a number-like pattern and apply only inside the numeric columns.

Wrong (will break addresses, IPs, version numbers):
Find: , Replace: .  (across whole file)

Better (only in numeric columns, only on number-like patterns):
Find: ^(-?[\d.]+),(\d+)$
Replace: $1.$2
(then handle thousands separators separately)

Best:
Parse the column as numbers using the source locale,
then format the numbers using the target locale.

Prevention

Ask for the format up front

If you regularly receive CSVs from European suppliers or international systems, ask them to export in a US-canonical format (1234.56 with no thousands separator). Most ERPs and accounting systems have a CSV export setting for the decimal/thousand separators. The supplier may be unaware their default differs from yours.

Lock down the system locale on import

If you import CSVs into Excel regularly, set Excel's locale explicitly for the import (Data > Get Data > From Text/CSV > set the Locale dropdown). This forces Excel to interpret numbers in the source's locale rather than your system locale, eliminating most silent conversion errors.

When the Source Format Isn't Consistent

Sometimes a single CSV contains mixed formats — usually because it was assembled from multiple sources or manually edited. This is the worst case. Spot-checking won't work; you need a column-by-column audit. The pragmatic fix: split the CSV into chunks, manually identify the format of each chunk, convert each separately, then merge. It's tedious but it's the only reliable way to deal with inconsistent source data.