Space-Grouped Thousands → Number¶

What

Parse the continental-European number format — space-grouped thousands with a comma decimal, "1 234 567,89" = 1234567.89 — into a clean numeric value.

Synthetic / teaching example

The data here is constructed, not sourced — sample.csv is hand-written amounts in the FR/CZ/SI style. The problem class is real; the rows are not.

Why interesting¶

French, Czech, Slovenian and many other EU exports group thousands with a space and use a comma for the decimal point. bxp's built-in grouped-number handling covers the US 1,234.56 form out of the box, and declaring csv_decimal_separator_in: "," covers the period-grouped continental form (1.234,56 → 1234.56) — but neither reaches the space-grouped form, so this is the small idiom that covers it. (A real cited example of the comma-decimal convention, handled with csv_decimal_separator_in, lives in real-world/french-dvf-realestate.)

Problem class documented in. (sources for the problem class — not for the data)

Decimal separator — Wikipedia documents the space-thousands + comma-decimal convention used across much of continental Europe.

The trick¶

The key expression (see inline comments in sample.json):

REPLACE(REPLACE([Amount], ' ', ''), ',', '.') * 1

strip the space thousands separators (REPLACE clears every occurrence, so one call handles 1 234 567);
swap the decimal comma for a dot;
* 1 coerces the result to a real number.

Final result¶

1 234 567,89  →  1234567.89
12 500,00     →  12500
1 050,50      →  1050.5

Each amount is now a plain number, ready to sum or compare.

Sample data¶

Run it with bxp-cli --config ./sample.json --template space_thousands_clean:

sample.json (config)sample.csvsample.csvx (result)

{
  // Teaching example — synthetic data. Continental-European number format:
  // space-grouped thousands and a COMMA decimal ("1 234 567,89" = 1234567.89),
  // common in FR / CZ / SI / many EU exports. bxp auto-handles the
  // period-thousands form ("1.234,56") but NOT space-grouped thousands, so this
  // is the one-line idiom for it.
  conversion_templates: {
    space_thousands_clean: {
      data_dir:           ".",
      file_pattern_in:    ".csv",
      file_pattern_out:   ".csvx",
      // Amounts are quoted because the decimal comma collides with the field
      // delimiter.
      csv_text_quote_in:  "double",

      input_schema: {
        $label: "[Label]",

        // THE TRICK — strip the space thousands separators, swap the decimal
        // comma for a dot, then * 1 to land a real number. REPLACE replaces
        // EVERY occurrence, so one call clears all the spaces.
        $amount: "REPLACE(REPLACE([Amount], ' ', ''), ',', '.') * 1"
      },

      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],

      output_schema: {
        label:  "$label",
        amount: "$amount"
      }
    }
  }
}

Label,Amount
Annual salary,"1 234 567,89"
Monthly rent,"12 500,00"
Utilities,"1 050,50"
Coffee,"42,00"

label,amount
Annual salary,1234567.89
Monthly rent,12500
Utilities,1050.5
Coffee,42