The route

Magic File

Download the exercise files

The last three levels were about telling a reader what a file looks like. Magic File flips that around: you give it a path and it works out the format, the delimiter, the encoding and the column types by itself. This level points it at six files in five formats — one of them with no extension at all — and checks what it decided.

Goal

One Magic File source that reads each of the six files in mixed/ correctly, and a table of what it detected for each.

You'll need

  • The exercise files for this level: a mixed/ folder with six files.
  • About 20 minutes.
File What it really is
customers_eu.csv CSV separated by ;, as European exports often are
tickets.json a JSON array of objects
events.ndjson newline-delimited JSON — one object per line, 20,000 lines
products.parquet Parquet, a binary columnar format
stock.xlsx an Excel workbook with one sheet
export_20250301 a TAB-separated file with no extension

Steps

1. Read the file with no name

Create a pipeline w2-4-magic-read. From INGEST ▸ Files drop a Magic File node named mixed_file and set File Path to mixed/export_20250301.

The Properties panel is much shorter than a CSV Source's: no delimiter, no header checkbox, no encoding — just a note saying format and schema are auto-detected at runtime, and that it supports CSV, JSON, NDJSON, Parquet and Excel.

Click Preview Output.

Magic File reading a file with no extension

Four columns — invoice, customer_id, amount, paid — and 150 rows. There's no extension to go on, so Magic File read the content: it isn't Parquet or a ZIP-based Excel file (those have binary signatures), it doesn't start with { or [ (so not JSON), which leaves delimited text — and scoring the candidate delimiters picked TAB.

2. Walk through the other five

Change File Path to each of the other files in turn and preview each one. Fill in the table as you go:

File Rows Columns
customers_eu.csv
tickets.json
events.ndjson
products.parquet
stock.xlsx

The same node reading products.parquet

Notice what you didn't have to do: set ; for the European CSV, pick a sheet for the workbook, or tell it that events.ndjson is one object per line rather than one big array.

3. See where auto-detection needs help

Open Advanced Options. This is where you override detection when you know better than it can: Format Hint when a file's content is ambiguous, CSV Delimiter and CSV Has Header when a delimited file is too small or too plain to be sure, and Excel Sheet when a workbook has more than one sheet — it reads the first one otherwise.

Magic File's Advanced Options

The rule of thumb: let it detect, preview, and only override what the preview shows is wrong.

Expected result

File Rows Columns Detected as
customers_eu.csv 237 4 CSV, delimiter ;
tickets.json 240 5 JSON
events.ndjson 20,000 6 NDJSON
products.parquet 8 3 Parquet
stock.xlsx 16 3 Excel, sheet stock
export_20250301 150 4 CSV, delimiter TAB

Common pitfalls

A workbook with several sheets reads the wrong one. Detection picks the first sheet. Name the one you want in Advanced Options.

A column of numbers arrives as text. If any value in a column has a leading zero — 00123, 007 — detection reads the whole column as text, so account numbers, postal codes and product codes keep their zeros. That's usually what you want. If you do need to calculate with it, cast it in a SQL transform: CAST(account AS BIGINT) (and accept that the zeros go).

An XML file fails with a CSV error. XML isn't one of Magic File's formats, so it falls through to CSV and fails (incorrect number of fields for line 1). Use the XML Source from level 2.3.

It's slow on a huge file before anything happens. Detection samples the file first (1,000 rows by default). That's usually fast, but a very wide file or a slow network drive makes it noticeable.

What you built

A single reader for "whatever lands in the folder". World 5 puts it inside a loop that iterates over a folder of mixed files — and that only works because one node can read them all.

Check yourself

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

  1. When should you use Advanced Options?