One Comma, Three Zeros: Parsing Supplier Prices Without Silent Magnitude Errors

A supplier file lists prices as text. One uses 1.234,50 where another uses 1,234.50. A parser that simply deletes commas, or that trusts the machine's regional setting, can multiply or divide a price by 100 or 1000 and still look normal. This article proposes a fixture and a few guards. The supplier and figures are hypothetical and are not anyone's real prices. Whether a real supplier file follows a given convention must be confirmed from that supplier's own sample, not assumed.

Principle: the format belongs to the supplier profile

Do not guess the format per cell. Record on a supplier profile: decimal separator is a comma, grouping separator is a dot or space or none. Parse with that profile only. Do not use the server's regional setting. Hold the result as a decimal type or whole minor units, not binary floating point, and keep the original text beside it.

The fixture

Profile for fictional supplier Northgate Parts: decimal comma, grouping dot, no currency symbols expected. Eight rows:

1. '12,50' gives 12.50, accepted.
2. '1.234,50' gives 1234.50, accepted.
3. '1234,50' gives 1234.50, accepted.
4. '0,99' gives 0.99, accepted.
5. '1.234' gives rejected as ambiguous in practice. Under the profile the dot is grouping, so it would be 1234, yet a stray decimal dot is a common slip. Send to review.
6. '1,234.50' gives rejected. The separators contradict the profile.
7. '12,5,0' gives rejected. More than one decimal comma.
8. '' (empty) gives rejected as missing, not as zero.

Result counts: 4 accepted, 4 rejected or held. A fixture whose counts do not add up to 8 has lost a row.

Magnitude guard

A well formed string can still be wrong by a factor of ten. When the SKU already has a stored price, compare the new value with it. If the ratio falls outside a limit you choose, for example more than a factor of 5 up or down, hold that row for review and do not apply it. In this hypothetical, a stored price of 12.50 and incoming 125,00 gives a ratio of 10, so it is held. Incoming 12,80 gives a ratio of 1.024 and passes. The limit is your own judgement and a held row is not necessarily an error, since real changes happen.

Decision branches

Branch A, the profile is unknown or unconfirmed: reject the whole file for profile confirmation. Do not infer from the first few rows.

Branch B, the file mixes formats between rows: stop the batch, since a mixed file means the profile assumption is false.

Branch C, currency symbols or text appear: strip only symbols the profile lists, and reject anything else.

Branch D, a row passes parsing but fails the magnitude guard: hold it with both values shown.

Stop conditions

Stop the import if more than a chosen share of rows are rejected or held, because this usually signals a wrong profile and not 'bad rows'. Never push held prices to a storefront or to a remote system, and do not auto repeat an uncertain remote write.

Proposed acceptance tests

These are proposals, not results.

1. Run the 8 fixture rows and compare accepted and rejected counts to 4 and 4.
2. Run the parser under two different regional settings and confirm identical output.
3. Confirm the original text is stored next to each parsed value.
4. Confirm 125,00 against a stored 12.50 is held, and 12,80 is applied.
5. Confirm a file with rows 2 and 6 together stops as mixed format.

A closing habit

Show reviewers the original string, the parsed value and the reason together. A price they can read in its source form is a price they can check by eye.

Sources