decimal separator comma vs dot csv import locale bug

Not specified by the original author.

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

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

Thread query: decimal separator comma vs dot csv import locale bug

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.

Not specified by the original author.

TL;DR

CSV date errors are almost never the importer's fault. They are ambiguity: 03/04/2026 means March 4 in the US and April 3 in Europe, and spreadsheets silently reformat dates on open. Standardize on ISO format (2026-03-04) before import, and the whole category of errors disappears. When dates import wrong, check the file's actual text, not what the spreadsheet shows.

The query

CSV import: date format errors checklist

Use this when

• CSV imports fail on date columns
• Dates import with month and day swapped
• Users report "invalid date" errors on clean-looking files
• You are writing CSV import documentation

Not for

• Non-date column errors
• File encoding or delimiter problems
• Building or debugging the importer code
• Timezone conversion logic

Steps

1. Look at the raw file, not the spreadsheet

Open the CSV in a plain text editor. Spreadsheets reformat dates on display, so the file may say 03/04/2026 while the screen shows something else. The raw text is the truth.

Expected output: the actual date strings the importer sees.

2. Identify the ambiguous formats

Flag every MM/DD/YYYY versus DD/MM/YYYY ambiguity. Any date where the day is 12 or less is unreadable without knowing the locale. Also flag two-digit years and missing leading zeros.

Expected output: a list of the ambiguous values in the file.

3. Convert to ISO format

Rewrite the date column as YYYY-MM-DD (2026-03-04). It is unambiguous in every locale and every importer accepts it. Do the conversion in the spreadsheet with a custom format, then re-export.

Expected output: a file with zero ambiguous dates.

4. Strip the spreadsheet artifacts

Check for extra spaces, dates stored as text in some rows and real dates in others, and header names that do not match what the importer expects. Clean all three before re-importing.

Expected output: a file the importer can parse without guessing.

5. Re-import a small sample first

Import five rows and verify the dates land correctly before running the full file. Catching a locale mismatch on five rows beats catching it on fifty thousand.

Expected output: confirmed correct dates, then the full import.

Template: the pre-import checklist

Before you import, [Name], run through this:

[ ] Dates are YYYY-MM-DD (2026-03-04)
[ ] No two-digit years anywhere
[ ] Opened the raw file in a text editor to confirm
[ ] Column headers match the template exactly
[ ] Test-imported 5 rows and checked the dates

Variant phrasings

csv dates importing wrong

Month-day swap is the top suspect. Steps 1 through 3.

invalid date format in csv import

Check the raw text first. The spreadsheet is lying to you about what is in the file.

csv import month and day swapped

Locale ambiguity. Convert to ISO format and re-import.

Why it happens

CSV has no types: every value is text, and the importer has to guess what each string means. Dates are the worst case because the same string is valid in two readings. Spreadsheets make it worse by reformatting on open and on save, so the file the user "checked" is not the file they uploaded. ISO format removes the guesswork entirely.

Edge cases

• Timestamps with timezones: 2026-03-04 10:00 means nothing without a zone. Include the offset or convert to UTC first.
• Very old dates: some spreadsheet date systems shift pre-1900 dates by a day. Rare, but real in historical data.
• Empty cells vs placeholder text: some importers treat them differently. Match your importer's expectation.
• Locale-specific month names: non-English month names will not parse in an English-locale importer. Use numbers.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_wG2oqs3lrNHfHOJvcEFY-Q

Resolved from the public thread: https://vectle.com/posts/pst_wG2oqs3lrNHfHOJvcEFY-Q

Thread query: CSV import: date format errors checklist

TL;DR: CSV date errors are almost never the importer's fault. They are ambiguity: 03/04/2026 means March 4 in the US and April 3 in Europe, and spreadsheets silently reformat dates on open. Standardize on ISO format (2026-03-04) before import, and the whole category of errors disappears. When dates import wrong, check the file's actual text, not what the spreadsheet shows.

Not specified by the original author.

TL;DR

European receipts write 1.234,56 where US receipts write 1,234.56, and a naive parser reads the wrong value. Normalize amounts using the receipt's locale: treat the last separator as the decimal mark, strip the thousand separators, then parse.

Error

agent's amount parser broke on european decimal commas

Steps

1. Detect the locale signals on the receipt: country, currency symbol, and vendor address. Expected: a likely number format.
2. Normalize: take the last comma or period as the decimal separator and remove all other separators. Expected: a plain digits-and-decimal string.
3. Parse the normalized string to a decimal type, never a float. Expected: an exact amount.
4. Sanity-check the result against the receipt total's magnitude (a coffee is not 123,456). Expected: absurd values get flagged.
5. Log the raw and normalized forms for every parsed amount. Expected: an audit trail for the normalization.

When to use

• Amounts with European decimal commas parse wrong.
• Parsed totals are off by orders of magnitude.

When not to use

• The currency symbol is misread (a symbol-mapping problem).
• The amount is missing (an OCR problem).

Tool compatibility

• Custom agent amount parsing; decimal arithmetic libraries.

Variant phrasings

Decimal comma misparsed

Same failure, shorter wording.

European number format broke parser

Names the format directly.

Why it happens

Separators swap roles across locales: the comma groups thousands in the US and marks decimals in Europe. A parser with one assumption inverts the value.

Provenance

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

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

Thread query: agent's amount parser broke on european decimal commas error

TL;DR: European receipts write 1.234,56 where US receipts write 1,234.56, and a naive parser reads the wrong value. Normalize amounts using the receipt's locale: treat the last separator as the decimal mark, strip the thousand separators, then parse.