Lesson 5 of 10 · Lab

Convert text to numbers and dates, European format

Parsing turns text into a number or a date by a format you name. Name the wrong one and values go empty or, worse, come out wrong without a warning.

24,50 is twenty-four euros fifty in most of Europe, and nonsense to the US format, which expects 24.50. 05/09/2026 is September 5 read day first and May 9 read month first.

Tomas's till at fictional Kramerica Bikes writes amounts and dates the European way. This live flow in Strata, Oplema's browser app for data prep, starts with the six columns you cleaned in the last lesson, so amount and hire_date are still text.

Number starts on the US format. How many of the 321 amounts will it read?

None. Under US rules a comma separates thousands, so 24,50 has the wrong grouping and 1.001,00 the wrong decimal point. Every amount becomes NULL, and the total is empty.

Read amounts and dates the European way

  1. Summarize opens first. gross shows NULL and priced reads 0: on the US format, Number read no amount at all.
  2. Select Number. Text looks like is on the US option, and every amount_eur in the preview is NULL.
  3. Set Text looks like to the EU option.
  4. Select Summarize. gross reads 12542 and priced 316. The 5 amounts still missing are staff hires marked gratis.
  5. In the palette, select Parse and drag DateTime onto the wire between Number and Summarize. If no wire runs through it, wire Number to DateTime and DateTime to Summarize, dot to dot.
  6. Select DateTime. Set Column to hire_date, Text looks like to the EU date option and Output as to DATE. Select the Output column field, select all (Ctrl+A or Cmd+A) and type hire_on.
  7. Select Summarize. Under Actions, select Add. The new action starts as Group by. Set Action to Count null, further down the list (scroll it), and Column to hire_on. Then select the Output field, select all (Ctrl+A or Cmd+A) and type undated in place of count_null. It reads 3.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What you should see

gross €12,542.00 from 316 amounts, and undated 3: the three hires dated see note. Every other date is in September.

Read the cells before choosing a format

The US number format emptied the whole column, which at least shows. A wrong date format is quieter. Read month first, this file gives 130 empty dates and moves 180 more into the wrong month. Only the 11 hires on September 9 read the same either way.

So look for a day above 12 before you choose: 14/09/2026 can only be day first. The NULLs that remain are honest, because gratis was never an amount and see note was never a date.

See the Number and DateTime references. For later: Parsing dates from text explains why day first and month first fail differently.

Common parsing mistakes

  • Keeping the default format. A default suits whoever set it, which may not be your file. Read a few cells first.
  • Testing with low days. 05/09/2026 parses either way. Find a day above 12.
  • Filling every NULL with 0. A free hire and a price nobody wrote down are different facts, and 0 makes them look the same. Keep NULL and count it.

Parsing amounts and dates in SQL and Excel

SELECT TRY_CAST(REPLACE(REPLACE(amount, '.', ''), ',', '.') AS DOUBLE) AS amount_eur,
       TRY_STRPTIME(hire_date, '%d/%m/%Y')::DATE AS hire_on
FROM harbour;

SQL has no format setting, so you remove the thousands dots, swap the comma for a point and state the date pattern yourself. In Excel, =NUMBERVALUE(A2, ",", ".") names both separators, and =DATE(RIGHT(B2,4), MID(B2,4,2), LEFT(B2,2)) builds a day-first date.