Python Doc & Data Automation

Combine All Sheets in an Excel Workbook

The regional workbook has twelve monthly sheets, a Notes sheet, a Lookup sheet feeding dropdowns and a hidden _calc sheet. The obvious one-liner produces a dictionary rather than a table, and concatenating it goes wrong in several ways at once:

>>> frames = pd.read_excel("in/sales-2026.xlsx", sheet_name=None)
>>> combined = pd.concat(frames.values(), ignore_index=True)
>>> combined.shape
(14210, 27)          # expected ~11,800 rows and 9 columns

Twenty-seven columns because one sheet spells a header differently and the notes sheet contributes free text; extra rows because totals rows and the lookup sheet came along; and no way to tell which month a row came from.

Root Cause

sheet_name=None returns every worksheet in the file, including sheets that are not data: notes, lookup tables feeding data validation, hidden calculation sheets, and any sheet an analyst left behind. pd.concat then aligns frames by column name, so Order Date and order_date become two columns with half the values missing in each, and a sheet with a title row above the header contributes columns named Unnamed: 1. Sheet identity is lost entirely — after concatenation there is nothing to say a row came from Mar — and rows that are not data, such as a bold Total line at the bottom of each month, are indistinguishable from real records. The fix is to decide which sheets are data, normalise their headers to a common schema, and tag each row with its source before combining.

Minimal Diagnostic

Inspect the workbook's structure before reading any data: which sheets exist, their visibility, dimensions, header row and column names.

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook

SOURCE = Path("in/sales-2026.xlsx")

def workbook_report(path: Path, probe_rows: int = 6) -> pd.DataFrame:
    wb = load_workbook(path, read_only=True)
    rows = []
    try:
        for ws in wb.worksheets:
            first_cells = []
            for row in ws.iter_rows(min_row=1, max_row=probe_rows, values_only=True):
                filled = [str(c).strip() for c in row if c is not None and str(c).strip()]
                first_cells.append(filled)
            header_row = next((i for i, cells in enumerate(first_cells) if len(cells) >= 3), None)
            rows.append({
                "sheet": ws.title,
                "state": ws.sheet_state,                      # visible / hidden / veryHidden
                "rows": ws.max_row,
                "cols": ws.max_column,
                "header_row": None if header_row is None else header_row + 1,
                "header_preview": ", ".join(first_cells[header_row][:4]) if header_row is not None else "",
            })
    finally:
        wb.close()
    return pd.DataFrame(rows)

if __name__ == "__main__":
    print(workbook_report(SOURCE).to_string(index=False))

  sheet     state  rows  cols  header_row                          header_preview
    Jan   visible   988     9           2  Order ID, Order Date, Customer, Region
    Feb   visible   902     9           2  Order ID, Order Date, Customer, Region
    Mar   visible  1043     9           2  Order ID, order date, Customer, Region
    ...
  Notes   visible    14     2           1        Sheet, Purpose
 Lookup   visible    41     3           1         region, manager
  _calc    hidden   200    12           1                    tmp, x

Every month sheet has a title row above its header (header on row 2), Mar spells one header differently, and three sheets are not data at all — one of them hidden.

What goes wrong when concatenating every sheet Non-data sheets such as notes, lookups and hidden calculation sheets are included because sheet_name None returns everything, and are excluded by a sheet filter. Header spelling differences create duplicate columns, fixed by normalising headers to a common schema. Title rows above the header produce Unnamed columns, fixed by detecting the header row per sheet. Row provenance is lost, fixed by adding the sheet name as a column before combining. Non-data sheets notes, lookups, hidden sheets; filter by name and state Header variants 'Order Date' vs 'order date' become two columns Title rows header is on row 2; Unnamed columns appear Lost provenance no way to tell which month a row came from

Fix: Select Sheets, Normalise Headers, Tag Rows, Then Combine

Read only the sheets that are data, find each one's header row, map its columns onto a declared schema, and add the sheet name before concatenating. Changed lines carry comments.

# pip install "pandas>=2.2" openpyxl
import re
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook

SOURCE = Path("in/sales-2026.xlsx")
MONTHS = ("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")
SCHEMA = {                                                      # changed: canonical name per header variant
    "order id": "order_id", "order no": "order_id",
    "order date": "order_date", "date": "order_date",
    "customer": "customer", "customer name": "customer",
    "region": "region", "qty": "qty", "quantity": "qty",
    "amount": "amount", "net amount": "amount", "revenue": "amount",
}
REQUIRED = {"order_id", "order_date", "customer", "region", "amount"}

def data_sheets(path: Path) -> list[str]:
    wb = load_workbook(path, read_only=True)
    try:
        return [ws.title for ws in wb.worksheets
                if ws.sheet_state == "visible"                   # changed: skip hidden sheets
                and ws.title.strip().lower()[:3] in MONTHS        # changed: only month sheets
                and ws.max_row > 2]
    finally:
        wb.close()

def find_header_row(path: Path, sheet: str, probe: int = 8) -> int:
    preview = pd.read_excel(path, sheet_name=sheet, header=None, nrows=probe, dtype="string")
    for i, row in preview.iterrows():
        labels = [str(v).strip().lower() for v in row if pd.notna(v)]
        if sum(label in SCHEMA for label in labels) >= 3:        # changed: the row that looks like a header
            return int(i)
    raise ValueError(f"{sheet}: no header row found in the first {probe} rows")

def read_sheet(path: Path, sheet: str) -> pd.DataFrame:
    header_row = find_header_row(path, sheet)
    frame = pd.read_excel(path, sheet_name=sheet, header=header_row, dtype={"Order ID": "string"})
    renamed = {}
    for col in frame.columns:
        key = re.sub(r"\s+", " ", str(col)).strip().lower()
        if key in SCHEMA:
            renamed[col] = SCHEMA[key]                           # changed: map onto the canonical schema
    frame = frame.rename(columns=renamed)
    missing = REQUIRED - set(frame.columns)
    if missing:
        raise ValueError(f"{sheet}: missing columns {sorted(missing)} (headers: {list(frame.columns)[:8]})")
    frame = frame[list(REQUIRED | (set(frame.columns) & {"qty"}))]
    frame = frame[frame["order_id"].notna()]                     # changed: drop total and blank rows
    return frame.assign(source_sheet=sheet)                      # changed: keep provenance

def combine(path: Path) -> pd.DataFrame:
    sheets = data_sheets(path)
    frames = [read_sheet(path, sheet) for sheet in sheets]
    combined = pd.concat(frames, ignore_index=True)
    combined["order_date"] = pd.to_datetime(combined["order_date"], errors="coerce")
    return combined

if __name__ == "__main__":
    sales = combine(SOURCE)
    print(sales.shape, sales["source_sheet"].nunique(), "sheets")
    print(sales.groupby("source_sheet", sort=False).size().to_string())

(11812, 7) 12 sheets
Jan     986
Feb     900
Mar    1041
...

Raising when a sheet lacks a required column — rather than letting concat fill it with NaN — is what turns a renamed header into an immediate, specific error. The order_id.notna() filter removes the bold total row at the bottom of each sheet, which has an amount but no order id; check your own workbooks for what distinguishes those rows before copying the rule.

Per-sheet reading pipeline First the workbook is inspected and only visible month sheets with data are selected. For each sheet the header row is found by looking for a row whose labels match the schema. Columns are renamed onto canonical names and missing required columns raise an error naming the sheet. Rows without an order id, such as totals and blanks, are dropped. Each frame is tagged with its sheet name before all frames are concatenated and dates are parsed once. Select sheets visible, month-named, with rows Find header row the row whose labels match the schema Map to schema rename variants; raise on missing required columns Filter rows drop totals and blank rows by key column Tag and combine add source_sheet, concat, parse dates once

Variant Fix 1: Many Workbooks, One per Branch

The same pattern extends to a folder of workbooks, adding the file as a second provenance column:

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd

def combine_folder(folder: Path) -> tuple[pd.DataFrame, list[str]]:
    frames, problems = [], []
    for path in sorted(folder.glob("*.xlsx")):
        if path.name.startswith("~$"):
            continue                                         # Excel lock files
        try:
            frame = combine(path)
        except Exception as exc:
            problems.append(f"{path.name}: {exc}")
            continue
        frames.append(frame.assign(source_file=path.stem))
    combined = pd.concat(frames, ignore_index=True) if frames else pd.DataFrame()
    return combined, problems

Collecting problems instead of raising lets one malformed branch workbook be reported without stopping the other eleven — with the file name in the message, so the right person can be asked. Where files also differ in structure, keep a per-file schema override next to the shared SCHEMA, rather than making the shared mapping ever more permissive.

Variant Fix 2: Sheets with Different Columns on Purpose

Some workbooks genuinely differ per sheet — a Services sheet with hours where Products has qty. Concatenating them is right, but the result should make the difference explicit rather than leaving silent NaN columns:

# pip install "pandas>=2.2"
import pandas as pd

def combine_heterogeneous(frames: dict[str, pd.DataFrame]) -> tuple[pd.DataFrame, pd.DataFrame]:
    all_columns = sorted({c for f in frames.values() for c in f.columns})
    coverage = pd.DataFrame(
        {name: [c in f.columns for c in all_columns] for name, f in frames.items()},
        index=all_columns,
    )
    combined = pd.concat(
        [f.assign(source_sheet=name) for name, f in frames.items()],
        ignore_index=True, sort=False,
    )
    return combined, coverage

The coverage table — columns against sheets — is worth printing in the job's log every run. When a supplier adds a column to one sheet only, it appears as a new row in the coverage table rather than as mysterious missing values in a report three weeks later.

Column coverage across sheets The month sheets Jan, Feb and Mar all provide order id, order date, customer, region and amount. The qty column is present in Jan and Feb but missing in Mar, which is visible immediately in the coverage matrix and would otherwise appear only as missing values after concatenation. Column Jan Feb Mar order_id yes yes yes order_date yes yes yes customer yes yes yes amount yes yes yes qty yes yes missing

Verification

Reconcile the combined frame against the workbook: row counts per sheet, totals per sheet, and no rows from non-data sheets.

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook

def verify_combined(path: Path, combined: pd.DataFrame) -> None:
    expected_sheets = set(data_sheets(path))
    assert set(combined["source_sheet"]) == expected_sheets, \
        f"sheets in result differ: {set(combined['source_sheet']) ^ expected_sheets}"
    wb = load_workbook(path, read_only=True)
    try:
        for sheet in expected_sheets:
            ws = wb[sheet]
            header_row = find_header_row(path, sheet) + 1
            filled = sum(1 for row in ws.iter_rows(min_row=header_row + 1, values_only=True)
                         if row and row[0] not in (None, ""))
            got = int((combined["source_sheet"] == sheet).sum())
            assert abs(filled - got) <= 1, f"{sheet}: {got} rows combined, {filled} data rows in the sheet"
    finally:
        wb.close()
    assert combined["order_id"].notna().all(), "rows without an order id survived the filter"
    assert combined["amount"].notna().all(), "rows with no amount survived the filter"
    duplicates = combined[combined.duplicated(subset=["order_id"], keep=False)]
    if not duplicates.empty:
        print(f"note: {len(duplicates)} row(s) share an order id across sheets, e.g. "
              f"{duplicates['order_id'].iloc[0]} in {duplicates['source_sheet'].unique()[:3]}")
    print(f"verified {len(combined):,} rows from {len(expected_sheets)} sheets")

The tolerance of one row absorbs a total row that the openpyxl count includes and the filter removed. Duplicate order ids across sheets are reported rather than asserted against: in a monthly workbook they usually mean an order was restated in a later month, which is a business question, not a bug — but one that has to reach a person before the numbers are used, as it affects any sum.

FAQ

How do I read only some sheets? Pass a list: pd.read_excel(path, sheet_name=["Jan", "Feb"]) returns a dictionary with those keys, and sheet_name=0 reads only the first.

Is reading all sheets slow? Reading a workbook parses the whole file regardless, so reading several sheets costs little more than one. The expensive part is very large sheets.

Why do some sheets come back with Unnamed: 0 columns? Their header row is not row 1. Detect it per sheet, as the fix does, or pass header= and skiprows= per sheet.

Can I keep the sheet order?data_sheets returns them in workbook order, and pd.concat preserves input order, so the result follows the tabs — useful when sheets are months.

Part of Merging Multiple Spreadsheets.

/html>