Detecting and Handling Embedded Headers Repeated Mid File

Some CSV files have a header at the top and more headers further down. The cause is usually mundane: someone concatenated three daily exports with cat, a reporting tool printed a header at the top of every page, or a script appended to a file and wrote the header each time it ran. A person scrolling the file sees the repeats and ignores them. A loader with typed columns does not, and fails on the first one, with an error that mentions a string where a number was expected. This article is about finding those lines, removing them without damaging the data and checking the seams. It is one of the shapes listed in CSV dialects and variants you will run into, and the usual cause of the one-line-too-many counts in checking row and column counts in a CSV.

What it looks like

Here is a small file made by concatenating three exports, so the header appears three times:

order_id,customer,total
1001,Acme Ltd,120.50
1002,Beta Corp,89.90
order_id,customer,total
1003,Cy Foods,15.00
order_id,customer,total
1004,Delta Inc,200.00

Seven lines, one real header, two repeats and four data rows. Counting lines tells you 7. A reader that treats the first line as the header and everything else as data reports 6 data rows, and two of them are not data.

The failure appears when you ask for a typed value. In DuckDB, summing the total column with a cast to decimal on the raw file:

SELECT sum(total::DECIMAL(10,2)) FROM read_csv('paged.csv', header=true, all_varchar=true);

ConversionException: Conversion Error: Could not convert string "total" to DECIMAL(10,2) when casting from source column total

The message names the value, total, which is the clue: a string equal to the column's own name is almost always a repeated header. Pandas gives a similar message about a string it could not convert to float, and a database COPY reports invalid input syntax for type numeric: "total" with a line number, which is the quickest way to find the first repeat.

Detect the repeats

Read everything as text first, so the reader cannot fail on the repeats, then count the rows equal to the header. In SQL:

SELECT count(*) AS lines FROM read_csv('paged.csv', header=true, all_varchar=true);
SELECT count(*) AS repeats FROM read_csv('paged.csv', header=true, all_varchar=true)
WHERE order_id = 'order_id' AND customer = 'customer' AND total = 'total';

On the sample, the first query returned 6 and the second 2. Testing every column, not just the first, matters. A row that matches only in the key column could be a real record: a customer genuinely named customer, an order note that says total and so on. Matching the whole row against the whole header is the definition of a repeated header, and anything less will one day delete data.

In a command-line pipeline, grep -c '^order_id,customer,total$' paged.csv counts the lines that are exactly the header, and the count should equal the number of seams plus one. For pages printed from a report, the repeated header may be slightly different on each page (with a page number in it, or a different number of padding spaces), so the match has to be looser: a row whose cells all match the header after trimming.

Remove them

Filter rows that equal the header, before any type conversion:

SELECT * FROM read_csv('paged.csv', header=true, all_varchar=true)
WHERE NOT (order_id = 'order_id' AND customer = 'customer' AND total = 'total');

('1001', 'Acme Ltd', '120.50')
('1002', 'Beta Corp', '89.90')
('1003', 'Cy Foods', '15.00')
('1004', 'Delta Inc', '200.00')

Four rows, all real. After filtering, cast and load as you would any file. The sum of the four totals is 425.40, which is the number you should check against the source. In the shell, the same removal is awk 'NR == 1 || $0 != hdr { print } NR == 1 { hdr = $0 }', which keeps the first line and drops any later line that is identical to it. In pandas, read with dtype=str and filter with df[df["order_id"] != "order_id"]. The order is what matters: read as text, filter, then convert. Reading with types first fails before you can filter, and reading with ignore_errors hides the rows you wanted to count.

Check the seams

A repeated header marks a seam, a place where one export ended and the next began, and seams are where records go missing or double. Look at the rows on either side of each one. If the exports were daily, each seam should have the previous day's last record before it and the next day's first record after it, and the dates should step by one. If the exports are overlapping windows, the same record can appear before and after a seam, so check for duplicate keys across the whole file (finding duplicate keys in a CSV). If the pieces are pages of a report, the last row of a page may be a subtotal that you do not want in the data.

Count what you should have. If you concatenated 3 files, there should be 2 repeated headers (3 headers minus the one you keep). If the number of repeats differs from the number of seams you expect, a file is missing a header, a file was empty, or two were appended into one. And check the total against the source: three files with 40,000 rows each should give 120,000 data lines, and a result of 119,998 means two lines went missing.

Do not create them in the first place

The cleanest fix is to stop producing the files. When concatenating CSV files, keep the header of the first and strip it from the rest:

head -n 1 part1.csv > all.csv
for f in part1.csv part2.csv part3.csv; do tail -n +2 "$f" >> all.csv; done

That handles files that end in a newline. A file that does not end in one makes the first line of the next join onto its last line, so check for it (tail -c1 file | xxd should show 0a). The issue is the subject of merging several CSV files with the same header, which also covers doing the merge in SQL, where the header is a name and not a line.

For scripts that append to a log-style CSV, write the header only if the file is empty or does not exist: check os.path.getsize(path) == 0 before writing it. And for report tools, find the setting that controls "repeat column headers on each page" in the export, or export the underlying data instead of the formatted report.

When the repeats are meaningful

A header that changes between sections is not a repeat. If one section has columns order_id,customer,total and the next has invoice_id,customer,amount, the file has two tables in it, and deleting the second header merges them wrongly. Look for the pattern with a query for rows where the first field is not a valid key (TRY_CAST(order_id AS INTEGER) IS NULL) and read what you find. A file of sections has a layout, and it needs a reader that knows it, not a filter.

Also keep the repeated rows if they carry information. A page header that includes the page's date or a batch number is metadata, and the data after it belongs to that batch. In that case, convert the header lines into a column: carry the batch ID forward onto each following row, then drop the header line. For a quick test of whether any lines are suspicious, a count of rows whose key fails to parse is the simplest tripwire, and a loader that logs rejected rows lists them for you (logging rejected rows from a CSV import for review). DuckDB documents the reader options used above in its CSV overview, and the pandas equivalent is in the read_csv reference.