The route

Delimiters and encoding

Download the exercise files

Most "CSV" files you'll meet at work aren't comma-separated UTF-8. This one is an ERP export: fields separated by |, text in Latin-1, Windows line endings. You'll read it wrong on purpose, recognise the symptom, and fix it — then break it a second way that people break it in real life.

Goal

A CSV Source that reads orders_pipe_latin1.txt into 1,200 rows and 9 columns, with the accented city names intact.

You'll need

  • World 1 done.
  • The exercise files for this level: orders_pipe_latin1.txt.
  • About 15 minutes.

The file is 1,200 orders from the same fictional SaaS as World 1 — customer_id matches customers.csv. The cities were picked for their accents: São Paulo, Düsseldorf, Évora, Québec.

Steps

1. Read it with the defaults

Create a pipeline w2-1-orders. Drop a CSV Source named orders, point File Path at orders_pipe_latin1.txt, and leave everything else as it comes: First row is header checked, Delimiter ,.

Click Preview Output.

Wrong delimiter: the whole line lands in one column

One column, with a name that's actually the entire header line — order_id|customer_id|order_date|… — and every row is one long string. That's the signature of a wrong delimiter. The CSV Source doesn't guess the delimiter; it splits on exactly what you tell it.

2. Fix the delimiter

Set Delimiter to | and preview again. Nine columns, 1,200 rows, and the cities read correctly: Düsseldorf, Setúbal, Évora.

Wait — the file is Latin-1, and you didn't say so. Open Advanced Settings:

Advanced Settings: Encoding is Auto-detect

Encoding is Auto-detect. Odara sampled the bytes, recognised a Western European single-byte encoding and converted it for you. For most files that's the right setting, and you should leave it alone.

3. Break it the realistic way

Now do what someone on your team will eventually do because "everything is UTF-8 these days": set Encoding to UTF-8 and preview.

Forcing UTF-8 on a Latin-1 file

Csv error: Encountered UTF-8 error while reading CSV file:
invalid utf-8: invalid UTF-8 in field 3 near byte index 1 at line 1

Field 3 of the first data row is city — Düsseldorf. In Latin-1, ü is a single byte (0xFC) that isn't valid UTF-8 on its own, so a reader told to expect UTF-8 has to stop. The error names the field and row, which tells you exactly where to look.

Set Encoding back to Auto-detect. If you ever need to pin it — a file too small or too plain for detection to be sure — pick Latin-1 (ISO-8859-1) or Windows-1252; both read this file correctly.

4. Look at what you got

Add a Console from DELIVER ▸ Debug, connect it, set Row Limit to 10, and Execute. Expand the console output.

The orders, read correctly

Expected result

  • 1,200 rows, 9 columns: order_id, customer_id, order_date, city, country, sku, product, quantity, unit_price.
  • The first order is 50089, customer 1450, 2025-01-01, Düsseldorf, DE, Priority support, quantity 10.
  • No ü, é or � anywhere in the city column.

Common pitfalls

One column whose name is the whole header line. Wrong delimiter. Look at the first line of the file in a text editor; the character between the column names is your delimiter. ; and TAB are the usual suspects after |.

invalid UTF-8 in field N … at line M. The encoding is pinned to UTF-8 and the file isn't. Go back to Auto-detect, or pin the encoding the file actually uses.

Accents turn into é-style pairs somewhere downstream. The reverse mistake: a UTF-8 file read as Latin-1. Each accented character is two bytes in UTF-8, and a single-byte reader shows both. Auto-detect avoids it; a pinned wrong encoding causes it.

Quotes in the data break the columns. Advanced Settings ▸ Quote Character is " by default. If a system exports ' as its quote character, set it here — otherwise a comma or pipe inside a quoted field splits it.

What you built

A source that reads a file the way it actually is, not the way CSV files are supposed to be. Keep the rule: set the delimiter yourself, let the encoding be detected, and pin it only when you know it.

Check yourself

One question, drawn from a larger bank. Get it right to pass.

  1. Does the CSV Source detect the delimiter on its own?