Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Extract Report Header Fields from an Excel Sheet with Python

Many exported reports put the branch, period, currency and run date in label-value pairs above the table. Read those fields with openpyxl wherever they sit, find where the table starts, parse the period into real dates and attach the fields to every row before combining files.
Messy spreadsheetsReading Excel filesopenpyxl

Exports from accounting, ERP and banking systems rarely start with the table. The first rows carry a title, then fields like "Branch: Leeds", "Period: 01/01/2026 - 31/03/2026", "Generated" with a timestamp, and "Currency: EUR" — and only then the column headers. pd.read_excel either chokes on that block or, with skiprows, throws it away, even though those fields are exactly what distinguishes one file from the next. This guide, part of Extracting Data from Messy Excel Sheets, reads the header fields and the table from the same sheet, wherever the block happens to start.

A report with metadata above the table The first rows hold a title, a Branch label with its value two cells to the right, a Period written as one text cell, a Generated timestamp and a Currency code. After a blank row, the table header lists Product, Units and Revenue, followed by the data rows. A B C 1 Sales by Branch 2 Branch: York 3 Period: 01/01/2026 - 31/03/2026 4 Generated 2026-04-02 08:15 5 Currency EUR 6 7 Product Units Revenue 8 Tea 10 120.00 9 Coffee 4 88.50 gold cells are header fields; blue is the table's own header row

Prerequisites

Bash
pip install pandas openpyxl

Two sample reports with the same layout, the second shifted down by two rows — the kind of variation that breaks a fixed skiprows:

Python
import datetime as dt
import re

import pandas as pd
from openpyxl import Workbook, load_workbook

def make_report(path, branch, blank_rows_above):
    wb = Workbook()
    ws = wb.active
    for _ in range(blank_rows_above):
        ws.append([])
    ws.append(["Sales by Branch"])
    ws.append(["Branch:", None, branch])
    ws.append(["Period: 01/01/2026 - 31/03/2026"])
    ws.append(["Generated", dt.datetime(2026, 4, 2, 8, 15)])
    ws.append(["Currency", "EUR"])
    ws.append([])
    ws.append(["Product", "Units", "Revenue"])
    ws.append(["Tea", 10, 120.0])
    ws.append(["Coffee", 4, 88.5])
    wb.save(path)

make_report("leeds.xlsx", "Leeds", 0)
make_report("york.xlsx", "York", 2)

Step 1 — Recognise the three kinds of header line

The block above the table mixes three shapes:

  • A label and a value in separate cells — "Branch:" in A, "York" in C, with an empty or merged cell between.
  • A label and a value in one text cell — "Period: 01/01/2026 - 31/03/2026".
  • A title — a single text cell with no colon.

Scanning with values_only=True and dropping empty cells turns the first shape into a simple pair, whatever the spacing. A regular expression splits the second. Anything else with a single text value is the title.

Python
INLINE = re.compile(r"^\s*([A-Za-z][A-Za-z ]{0,30}?)\s*:\s*(.+?)\s*$")

print(INLINE.match("Period: 01/01/2026 - 31/03/2026").groups())
# ('Period', '01/01/2026 - 31/03/2026')
print(INLINE.match("Sales by Branch"))            # None

Limiting the label to 30 letters and spaces stops a sentence that happens to contain a colon — a footnote, say — from being taken as a field.

Step 2 — Scan until the table header

Read rows from the top, collecting fields, until you reach the row that contains the table's column headers. Its position is also what pandas needs to read the table:

Python
def read_report(path, table_columns=("Product", "Units", "Revenue"), max_rows=30):
    ws = load_workbook(path, read_only=True, data_only=True).active
    fields, title, header_row = {}, None, None
    for idx, row in enumerate(ws.iter_rows(max_row=max_rows, values_only=True)):
        values = [v for v in row if v not in (None, "")]
        if not values:
            continue
        if set(table_columns) <= {str(v).strip() for v in values}:
            header_row = idx
            break
        first = values[0]
        if isinstance(first, str) and len(values) == 1:
            match = INLINE.match(first)
            if match:
                fields[match.group(1).lower()] = match.group(2)
            elif title is None:
                title = first.strip()
        elif isinstance(first, str):
            fields[first.strip().rstrip(":").strip().lower()] = values[1]
    if header_row is None:
        raise ValueError(f"{path}: columns {table_columns} not found in the first {max_rows} rows")
    table = pd.read_excel(path, header=header_row)
    return title, fields, table

title, fields, table = read_report("york.xlsx")
print(title)       # Sales by Branch
print(fields)
print(table)

The York file has two extra blank rows at the top, and it still works: the scan counts every row, and header= takes the same zero-based position. Opening in read_only mode keeps the scan cheap even on large exports, since only the first rows are read. The error names the file and the missing columns when a report has changed layout, which is far easier to act on than a KeyError later. Locating header rows by content is covered further in Find the Header Row in an Excel Sheet with a Shifting Layout.

One pass over the top of the sheet Rows are read from the top. Empty rows are skipped, rows with a label and value become fields, a single inline Label colon value cell is split into a field, the first other text is the title, and the scan stops at the row that contains the table's column names, whose index is passed to pandas. Read a row values only Skip if empty spacer rows Table header? stop, keep index Pair or inline label → value pandas reads table header=index

Step 3 — Turn field text into typed values

Header fields arrive as text or as whatever type Excel stored. The period is text that needs splitting into two dates; the generated timestamp is already a datetime because the cell held a real date:

Python
def parse_period(text):
    parts = re.split(r"\s+(?:-|–|to)\s+", text.strip())
    if len(parts) != 2:
        raise ValueError(f"cannot parse period {text!r}")
    return [pd.to_datetime(p, dayfirst=True) for p in parts]

start, end = parse_period(fields["period"])
print(start.date(), end.date())                  # 2026-01-01 2026-03-31
print(type(fields["generated"]).__name__)        # datetime

Splitting on a dash surrounded by spaces, rather than on any dash, keeps ISO dates such as 2026-01-01 - 2026-03-31 intact. dayfirst=True is essential for European exports: without it, 01/03/2026 would be read as 3 January. If a system writes dates in more than one style, parse with an explicit format= and let it fail loudly.

Step 4 — Check the fields you depend on

A report missing its branch or period should stop the run, not produce rows that cannot be traced. Check required fields and their types in one place:

Python
REQUIRED = {"branch": str, "period": str, "currency": str}

def check_fields(path, fields):
    problems = [f"missing {k}" for k in REQUIRED if k not in fields]
    problems += [f"{k} is {type(fields[k]).__name__}" for k, t in REQUIRED.items()
                 if k in fields and not isinstance(fields[k], t)]
    if problems:
        raise ValueError(f"{path}: " + "; ".join(problems))

check_fields("york.xlsx", fields)
print("fields ok")

Unknown extra fields are fine — systems add lines like "Printed by" — so only the fields the pipeline uses are enforced. Stricter validation of the table itself is covered in Validating Excel Data with Python.

Step 5 — Tag every row and combine reports

Once each report is read, copy the header fields onto its rows as ordinary columns and stack the files. The branch and period now travel with the data:

Python
frames = []
for path in ["leeds.xlsx", "york.xlsx"]:
    title, fields, table = read_report(path)
    check_fields(path, fields)
    start, end = parse_period(fields["period"])
    frames.append(table.assign(
        branch=fields["branch"], period_start=start, period_end=end,
        currency=fields["currency"], source_file=path,
    ))
combined = pd.concat(frames, ignore_index=True)
print(combined[["branch", "Product", "Revenue", "period_start"]].values.tolist())
print(combined.groupby("branch")["Revenue"].sum().to_dict())   # {'Leeds': 208.5, 'York': 208.5}

Keeping source_file as well costs one column and answers "where did this row come from?" when a number is questioned months later. Combining many files in a folder follows the same pattern; see Combine Multiple Excel Files into One in Python.

Header fields become columns on every row Each report's table has only Product, Units and Revenue. After tagging, every row also carries the branch, the period start date and the currency taken from the fields above its table, so rows from Leeds and York can be combined and still told apart. A B C 1 Product Units Revenue 2 Tea 10 120.00 3 Coffee 4 88.50 A B C D 1 branch Product Revenue period_start 2 York Tea 120.00 2026-01-01 3 York Coffee 88.50 2026-01-01 before after assign fields

Tagging every row repeats the same few values thousands of times, which is fine for a report of this size and makes filtering trivial. For very large combined datasets, or when the header block has many fields, store one row per report in a separate "runs" table — file name, branch, period, generated time — and give the data rows only a run identifier. Written as two sheets or two database tables, that keeps the data compact and makes it easy to answer questions about the runs themselves, such as which branches have not sent this quarter's report.

Common pitfalls

  • Fixed skiprows. A report that gains one line of header — a new "Printed by" field — shifts every column name. Find the table header by content.
  • Two fields on one row. Some layouts put the title in A1 and the period in the top-right corner of the same row. The scan pairs the first two non-empty cells, so it would read the period as the title's value; for such layouts, walk the row's cells two at a time and check each label against the fields you expect.
  • Merged label cells. openpyxl returns the value only in the top-left cell of a merge, and None elsewhere; dropping empty values handles that.
  • Formula-driven headers. If the period is a formula, open with data_only=True to get its cached result, as the scan does.

Conclusion

Report header blocks carry the context that tells files apart. Scan the top rows with openpyxl, pairing labels and values across empty cells and splitting "Label: value" text; stop at the table's column-header row and pass its index to pandas. Parse periods and dates explicitly, fail on missing required fields, and attach the fields to every row so combined data keeps its source.

Frequently asked questions

How do I read the values above the table in an Excel report? Scan the first rows with openpyxl, treating a text cell followed by a value as a label-value pair and a single "Label: value" cell as an inline pair. Stop when you reach the row that holds the table's column headers.

How does pandas skip the header block? Pass header= the zero-based index of the table's column-header row, which the scan returns. pandas then reads the table below it and ignores everything above.

What if the label and value are separated by empty cells? Ignore empty cells when pairing. Take the first non-empty cell in the row as the label and the next non-empty cell as the value, which handles merged cells and spacer columns.

Why attach header fields to every row? Once several reports are combined, the branch or period printed above each table is the only thing that tells rows apart. Adding them as columns keeps that information with the data.

More on messy spreadsheets

More on messy spreadsheets →