Is 1.234 one, or one thousand? Getting CSV decimal separators right

Here is a number from a bank statement:

1.234

If the bank is American, that is one point two three four. If the bank is German, that is one thousand two hundred and thirty four. Same characters, 1000x apart.

You cannot tell which one it is by looking at it. That is the whole problem.

We had already solved this. For dates.

I build a browser-based converter that turns bank CSV files into formats accounting software will accept. Three weeks ago I wrote this comment at the top of the date parser:

The most dangerous thing here: MM/DD/YYYY and DD/MM/YYYY look identical. 03/04/2024 is either March 4th or April 3rd, and a single value can never tell you which. The fix is to judge the whole column at once. Raising a warning matters, because a whole column of wrong dates shifts the books by a month, and it is the kind of error that looks fine.

Every word of that applies to amounts. I did not notice.

The amount parser was guessing one value at a time:

// only comma present: is it a decimal point or a thousands separator?
// guess from whether exactly 3 digits follow it
const tail = s.slice(lastComma + 1);
const isGrouping = tail.length === 3 && /^\d{3}$/.test(tail);

That heuristic is fine on 1,234 and fine on 12,50. It is wrong on 1.234, and it is wrong silently.

What it costs

Three lines from a German statement, before the fix:

in the file	we read it as	the bank meant
-1.234	-1.23	-1234.00
2.500	2.50	2500.00
-12,50	-12.50	-12.50

Every amount in the column comes out 1000x too small. Nothing throws. The import succeeds. The register looks normal. You find out at reconciliation, if you find out at all.

Rent showing up as 1.23 is not a crash. It is a number that sits there looking like a number.

The fix is the same one, applied to the other column

Look at the whole column and find a value that gives the answer away. In order of strength:

1. Both separators in one value. 1,234.56 or 1.234,56. Whichever comes last is the decimal point. This settles it immediately.
2. A separator with something other than 3 digits after it. 12,5 cannot be a thousands separator. Neither can 1,2345.
3. The same separator twice in one value. 1.234.567 means the dot is grouping.
4. None of the above. Every value in the column looks like 1.234. Now you genuinely cannot tell.

Rule 4 is the one that matters. Most parsers pick a default here and say nothing. We pick US convention and raise a warning:

Amounts like "1.234" could mean 1.234 or 1,234. Every value in this file has
exactly three digits after the separator, so the file alone can't tell us.
We read "." as the decimal point (US convention). If this statement is
European, every amount is 1000x too small.

One qualifying value rescues the entire column. In the German sample above, -12,50 does it: two digits after the comma, so the comma is not grouping, so the column is European, so -1.234 is 1234.00.

A detail I only found by writing the test wrong

My first test file used commas as the CSV delimiter:

Date,Description,Amount
06/04/2024,KAFFEE,-12,50

That row has four fields, not three. Which is exactly why European CSV files use semicolons. The comma was already taken.

The part I actually want to remember

The method was already in the codebase. I wrote it down myself, in a comment, at the top of a file I open regularly. Then I wrote a second parser with the same class of ambiguity and guessed value by value for three weeks.

Writing something down is not the same as remembering it. When the same shape of problem shows up a second time, the useful move is to go look at how you solved it the first time, and I did not.

The converter is at qbofile.com if you want to see the behaviour. It runs entirely in the browser, so nothing gets uploaded.