A Supplier Price List With Merged Header Rows Fixed

Suppliers send price lists as spreadsheets and often as CSV exports of those spreadsheets, and a spreadsheet is a layout, not a table. There is a title on the first row, a validity note on the second, a blank row for air, then the header, then the data, in which each category name is written once on its first row and left blank beneath, as it would be in a merged cell. At the foot, a note about discounts. A person reads it perfectly. A parser sees a file whose first four rows are not data, whose category column is mostly empty and whose last rows are prose. The cleaning is three moves: skip the preamble, fill the blanks downward, and keep only rows that are items. This article does it on a small file in DuckDB and shows how to prove that no product was lost. It is one of the problems in worked CSV problems and the patterns behind them, and a sibling of parsing multi line headers in report style CSV files.

The file

ACME SUPPLY CO - PRICE LIST Q4,,,
Prices exclude VAT. Valid until 31 Dec.,,,
,,,
Category,Item,Unit,Price
Cables,USB-C 1 m,each,4.20
,USB-C 2 m,each,5.80
,HDMI 2 m,each,7.10
Adapters,USB-C to HDMI,each,12.90
,USB-A to USB-C,each,3.40
,,,
Notes: bulk discounts on request,,,

Eleven lines. Lines 1 and 2 are title and terms, line 3 is blank, line 4 is the header, lines 5 to 9 hold five items under two categories, line 10 is blank and line 11 is a note. Every line has four comma-separated fields, because the spreadsheet had four columns, which is why a CSV reader does not complain: the file is perfectly regular as CSV and irregular as a table.

A naive read with the header on line one makes ACME SUPPLY CO - PRICE LIST Q4 the first column name and gives three unnamed columns. The right read skips the preamble, and the rest follows.

Skip, number, fill down

Skip the first four lines so that data starts at line 5, read with no header and explicit column names, and number the rows, since the fill-down needs a stable order:

WITH raw AS (
  SELECT row_number() OVER () AS n, *
  FROM read_csv('price_list.csv', header=false, skip=4,
                columns={'category':'VARCHAR','item':'VARCHAR','unit':'VARCHAR','price':'VARCHAR'},
                null_padding=true)
)
SELECT n,
       last_value(nullif(category, '') IGNORE NULLS) OVER (ORDER BY n) AS category,
       item, unit, price::DECIMAL(10,2) AS price
FROM raw
WHERE item <> '' AND item IS NOT NULL AND TRY_CAST(price AS DECIMAL(10,2)) IS NOT NULL
ORDER BY n;

n  category  item             unit  price
1  Cables    USB-C 1 m        each   4.20
2  Cables    USB-C 2 m        each   5.80
3  Cables    HDMI 2 m         each   7.10
4  Adapters  USB-C to HDMI    each  12.90
5  Adapters  USB-A to USB-C   each   3.40

The last_value(... IGNORE NULLS) window carries the most recent non-NULL category down the rows: nullif(category, '') turns the empty strings of the blank cells into NULL, and IGNORE NULLS tells the window to skip them and return the last real value. Row 2 has no category of its own and gets Cables from row 1. Row 4 starts Adapters, and row 5 inherits it. The ORDER BY n is the contract: the fill is only correct if the rows are in file order, which is why they were numbered at the start. Without a number, a window with no order has no meaning, and a query that reorders rows would put categories on the wrong items.

The WHERE clause is the filter that drops what is not an item. It requires a non-empty item and a price that parses as a decimal. The blank line after the data has an empty item and is dropped. The note line, Notes: bulk discounts on request, has text in the first column, but it is the category column, and its item and price are empty, so it is dropped too. The filter does not name the footer. It describes what an item is, and anything else falls out, which is why the same query still works when the next issue has a different footer.

The proof

Three checks make the result trustworthy. The item count should equal the number you see in the source sheet. Here it is 5, and a person counting the rows in the original agrees. The category count should match as well: two categories, Cables and Adapters, each with the right number of items (3 and 2). And every kept row must have a parseable price, which the filter guarantees, so a different check is needed to catch the opposite error, a real item dropped because its price was written oddly. Count the rows that were excluded and look at them:

SELECT n, category, item, price FROM raw
WHERE NOT (item <> '' AND item IS NOT NULL AND TRY_CAST(price AS DECIMAL(10,2)) IS NOT NULL);

On the sample this returns the blank line and the note line, nothing else. If a row with an item and an odd price, say POA or 4,20 with a decimal comma, appears here, it is a product the filter dropped, and you want to know. The excluded-rows query is the part people skip, and it is the one that finds the missing product.

When the preamble varies

The skip=4 is fragile. If the supplier adds a line to the terms next quarter, the header moves to line 5 and the query silently reads the header as an item (and drops it, since its price does not parse) and shifts nothing else, so it may look fine. Do not hard-code the skip. Find the header by its content: read the file as one text column, locate the first line that starts with Category,, and use that line number:

SELECT min(n) AS header_line FROM (
  SELECT row_number() OVER () AS n, line
  FROM read_csv('price_list.csv', header=false, columns={'line':'VARCHAR'}, delim='\x01', quote='', escape='')
) WHERE line LIKE 'Category,%';

Fail with a clear message if there is no such line, and pass the number into the skip option of the main read. A header that moves is acceptable, and a header that cannot be found stops the load, which is what you want.

Also decide what to do about the other regular changes: a new column added to the right (extra empty fields at the end of the title lines, and a new header name), a unit column that stops being filled, a second sheet exported as a second block below the first. Each is a reason the script should compare the header it found with the one it expects (handling a CSV provider who changes columns without warning).

Merged cells in other places

The category column is the usual merged cell. The same technique handles others: a supplier name in the first column that applies to the rows below, a date written once per day, a region on the first row of each block. The rule is the same, fill down with IGNORE NULLS, over rows in file order. Merged cells across columns, a header cell that spans three columns, need a fill to the right, which is a different problem and is covered in parsing multi line headers in report style CSV files.

Be careful about the case where a blank is meaningful. If a category cell is blank because the item belongs to no category, and not because it was merged, forward fill would assign it to the wrong group. The test is whether the first data row has a category: a blank in the first row means the layout is different from what you assumed, and the script should stop.

When the layout changes between issues

A supplier who changes the layout every issue is a supplier whose price list should come in a different form. Ask for a flat export, a single header and one item per row with the category in every row, or a data feed. If they cannot, keep the script small, keep a sample of each layout beside it as a test and make it fail on anything unfamiliar. A price list drives money decisions, and a parsing error that drops a product or shifts a price is expensive in a way that a failed job is not. The window function behind the fill is documented in DuckDB's window function reference, and the reader options in its CSV overview.