decimal separator comma vs dot csv import locale bug

Fix decimal separator comma vs dot bugs in CSV import

TL;DR

The CSV numbers use the sender's locale separators, so parse them with a locale-aware parser instead of parseFloat. parseFloat only understands dots, so 1,5 becomes 1 and 1.000 becomes 1. Detect or configure the source locale, normalize separators, then parse.

The error

CSV import turns "1,5" into 1 and "1.234,56" into 1.234
European-format numbers parse wrong with parseFloat

Fix it

Step 1: Confirm the failure with the raw values

node -e "console.log(parseFloat('1,5'), parseFloat('1.234,56'))"

Expected: You see 1 and 1.234, both wrong.

Step 2: Normalize using the source locale

node -e "const de='1.234,56'; const n=Number(de.replace(/\./g,'').replace(',','.')); console.log(n)"

Expected: The European format becomes 1234.56.

Step 3: Or parse with Intl.NumberFormat for the locale

node -e "const parts=new Intl.NumberFormat('de-DE').formatToParts(1234.56); console.log(parts.map(p=>p.type+':'+p.value).join(' '))"

Expected: You see the group and decimal separators to strip and replace.

Step 4: Add a regression test with both formats

node -e "console.log('test: 1,5 to 1.5 and 1.234,56 to 1234.56 for de, 1,234.56 to 1234.56 for en')"

Expected: Both formats parse correctly.

When to use this

• CSV import mangles numbers with comma decimals
• European number formats parse wrong

When NOT to use this

• The CSV delimiter is the comma, that is a separate delimiter setting
• Numbers are right but dates are wrong, handle dates separately

Tool and version compatibility

• Node and browsers, Intl.NumberFormat formatToParts
• Any CSV parser, handle numbers after splitting fields

Variant phrasings

unknown source locale per file

Sniff the header or ask the uploader. Auto-detecting separators from data is error-prone, prefer explicit config.

currency symbols mixed into the numbers

Strip the symbol first, then normalize separators. Do it in that order.

Why it happens

Decimal and grouping separators are locale conventions, comma-decimal in much of Europe, dot-decimal in the US. parseFloat implements only the US convention, so it stops at the first comma and misreads dots as decimals.

Edge cases

• Some locales use a space or apostrophe as the group separator, handle those too
• Negative numbers in parentheses are an accounting convention, parse them separately
• Never guess per-cell, one file means one convention

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_XxPJsMaxOa2lL5M9hWzXRA