The route

Excel: sheets, skipped rows and headers

Download the exercise files

Spreadsheets are made for people, and people put titles above their tables. This workbook has two data sheets that each start with a title, an export note and a blank line before the real header — plus a notes sheet with no table at all. You'll read the right sheet from the right row.

Goal

An Excel Source that reads the H1 2025 sheet of sales_2025.xlsx into 240 rows with the columns month, region, sales_rep, sku, units, revenue.

You'll need

  • The exercise files for this level: sales_2025.xlsx.
  • About 15 minutes.

Open the workbook in Excel or LibreOffice first and look at it the way its author did: three sheets — H1 2025, H2 2025, Notes — and on each data sheet, the table starts on row 4.

Steps

1. Read it with the defaults

Create a pipeline w2-2-sales. From INGEST ▸ Files drop an Excel Source named sales_h1 and set File Path. Leave Sheet Name as it comes — Sheet1 — and click Preview Output.

The default sheet name doesn't exist in this workbook

Failed to read sheet 'Sheet1': Xlsx error: Worksheet 'Sheet1' not found

Sheet1 is only a default. Real workbooks name their sheets.

2. Name the sheet

Set Sheet Name to H1 2025 — exactly, with the space — and preview.

Right sheet, wrong starting row

It reads, but it's wrong in an instructive way:

  • The first column is named Sales report — H1 2025 — the title in cell A1 became a header.
  • The other columns are column_2 … column_6, because the rest of row 1 is empty.
  • The first "data" rows are the export note and a row of nulls.
  • It claims 243 rows: 240 real ones, plus the note, the blank line and the real header line, now sitting in the data.

3. Skip the title rows

Set Skip Rows to 3. Skip Rows drops that many rows from the top of the sheet before anything else happens, so row 4 becomes the first row Odara sees — and with First row is header checked, it becomes the header.

Sheet Name and Skip Rows set, with the preview

Preview: six named columns, 240 rows, starting 2025-01 · North · E. Walsh · P-100.

4. Check the types

Look at units in the preview: 16.0000, not 16. Excel stores every number as a floating-point value — there's no integer type in a spreadsheet cell — so whole numbers arrive as decimals. It's harmless here; when you need integers downstream, cast them in a SQL transform (World 3).

5. Read the second half of the year

Add a second Excel Source, name it sales_h2, set Sheet Name to H2 2025 and Skip Rows to 3. Same shape, 240 rows. Point one at Notes too, and see what a sheet without a table gives you.

Expected result

  • sales_h1: 240 rows, 6 columns, first row 2025-01, North, E. Walsh, P-100, 16.0000, 464.0000.
  • sales_h2: 240 rows, first row 2025-07, North, D. Novak, P-100.
  • Notes: 1 row, one column named after the sheet's first sentence — a sheet without a table still "reads", which is why you look at the preview instead of trusting a green run.
  • Revenue for H1 adds up to 1,148,645.70 — you'll compute that yourself in World 3.

Common pitfalls

Worksheet 'X' not found. The name doesn't match — and it's case-sensitive: h1 2025 fails. Copy the sheet name from the tab in Excel rather than retyping it; watch for trailing spaces.

The first column is named after a title, and the rest are column_N. There are rows above the header. Count them and put that number in Skip Rows.

The header is right but the first data row is missing. You skipped one row too many — the header is now being skipped, and the first data row is being used as the header.

Integers show up with decimals. Expected for Excel. Cast in SQL if the target needs integers.

What you built

A source that reads a spreadsheet the way a person laid it out. Sheet name and skip rows are the two settings that make the difference, and both come from looking at the file first.

Check yourself

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

  1. The first column is named `Sales report — H1 2025` and the rest are column_2 … What's going on?