Checking an Incoming Workbook Against a Template by Code

A template is a promise: this sheet, these columns, these allowed values. The check is how you find out whether the file that arrived kept the promise. Without a check, a renamed header or a column inserted in the middle loads wrong data under right names, and the first symptom is a wrong report. With a check, the sender gets a list of problems within seconds and the database sees only files that fit. This article builds that check in Python, as a schema in data and a short loop of tests, with the error report designed for the people who must fix the file. It is the receiving half of controlling what goes into a spreadsheet and the second stage after the upload gate in checking an Excel upload before reading it.

Describe the template as data

Write the contract as a structure, separate from the code that checks it.

SCHEMA = {
    "sheet": "Count",
    "table": "Count",           # optional: read by table name if present
    "version": "2026.3",
    "columns": [
        {"name": "site",          "type": "str",  "required": True,  "allowed": ["North", "South", "East"]},
        {"name": "location_code", "type": "str",  "required": True,  "pattern": r"[A-Z]\d{3}"},
        {"name": "sku",           "type": "str",  "required": True,  "max_len": 12},
        {"name": "qty",           "type": "int",  "required": True,  "min": 0, "max": 100000},
        {"name": "counted_on",    "type": "date", "required": True},
        {"name": "counted_by",    "type": "str",  "required": False},
    ],
    "unique": [["location_code", "sku"]],
}

A schema in data has three uses. The checker reads it. The template generator reads it to build validation rules and headers. And the documentation reads it to produce the instructions sheet. One definition, three consumers, no drift (building Excel templates that import cleanly).

Stage one: structure

Find the data and check the layout before reading any value.

from openpyxl import load_workbook

def read_rows(path, schema):
    wb = load_workbook(path, read_only=False, data_only=True)
    if schema["sheet"] not in wb.sheetnames:
        raise StructureError(f"Missing sheet '{schema['sheet']}'. Found: {', '.join(wb.sheetnames[:6])}")
    ws = wb[schema["sheet"]]
    if schema.get("table") and schema["table"] in ws.tables:
        ref = ws.tables[schema["table"]].ref
        grid = [[c.value for c in row] for row in ws[ref]]
    else:
        grid = [list(r) for r in ws.iter_rows(values_only=True)]
    return grid

def check_header(grid, schema):
    header = [str(h).strip().lower() if h is not None else "" for h in grid[0]]
    expected = [c["name"] for c in schema["columns"]]
    missing = [h for h in expected if h not in header]
    extra = [h for h in header if h and h not in expected]
    if missing:
        raise StructureError(f"Missing columns: {', '.join(missing)}")
    if header[:len(expected)] != expected:
        raise StructureError(f"Columns are out of order. Expected: {', '.join(expected)}")
    return extra

Structure errors stop the run. If a column is missing or the order is wrong, no value-level check means anything. Extra columns can be tolerated (reported as a warning) or rejected, by policy; tolerating them is friendlier and requires the loader to ignore them by name and not position.

Check the version too. If the file carries a version cell (on the instructions sheet or in a named cell), compare it with schema["version"] and give a useful message: "This file uses template 2026.1; please download 2026.3." (versioning template layouts so old files still import covers how to support more than one).

read_only=False is used here because tables are only visible in normal mode. For large files, stream the sheet in read-only mode and give up the table lookup, and apply the size limits from zip bombs and oversized XLSX uploads.

Stage two: content

Build rows as dictionaries and check each cell against its column rule. Collect all errors.

import re
from datetime import date, datetime

def parse(value, col):
    t = col["type"]
    if value is None or (isinstance(value, str) and not value.strip()):
        return None
    if t == "str":
        return str(value).strip()
    if t == "int":
        if isinstance(value, bool): raise ValueError("not a whole number")
        if isinstance(value, float) and value.is_integer(): return int(value)
        if isinstance(value, int): return value
        if isinstance(value, str) and re.fullmatch(r"-?\d+", value.strip()): return int(value)
        raise ValueError("not a whole number")
    if t == "date":
        if isinstance(value, datetime): return value.date()
        if isinstance(value, date): return value
        raise ValueError("not a date (use a real date cell)")
    raise ValueError(f"unknown type {t}")

def check_rows(grid, schema):
    errors, good = [], []
    cols = schema["columns"]
    for i, raw in enumerate(grid[1:], start=2):            # row 2 = first data row
        if all(v is None or (isinstance(v, str) and not v.strip()) for v in raw):
            continue                                       # skip empty rows
        row, row_ok = {}, True
        for j, col in enumerate(cols):
            v = raw[j] if j < len(raw) else None
            try:
                p = parse(v, col)
            except ValueError as e:
                errors.append((i, col["name"], str(e), v)); row_ok = False; continue
            if p is None:
                if col["required"]:
                    errors.append((i, col["name"], "required", v)); row_ok = False
            else:
                if "allowed" in col and p not in col["allowed"]:
                    errors.append((i, col["name"], f"must be one of {', '.join(col['allowed'])}", v)); row_ok = False
                if "pattern" in col and not re.fullmatch(col["pattern"], p):
                    errors.append((i, col["name"], "wrong format", v)); row_ok = False
                if "min" in col and p < col["min"]:
                    errors.append((i, col["name"], f"below {col['min']}", v)); row_ok = False
                if "max" in col and p > col["max"]:
                    errors.append((i, col["name"], f"above {col['max']}", v)); row_ok = False
                if "max_len" in col and len(str(p)) > col["max_len"]:
                    errors.append((i, col["name"], f"longer than {col['max_len']}", v)); row_ok = False
            row[col["name"]] = p
        if row_ok:
            good.append((i, row))
    return errors, good

Dates need a real date cell. A date typed as text comes back as a string, and the check says so; guessing a text date format invites the day-month swap, so ask the sender to fix the cell (importing Excel dates correctly). Identifiers are strings: a numeric cell where text is required is an error, because the leading zeros may already be gone.

Uniqueness comes last, on the rows that passed:

from collections import defaultdict

def check_unique(good, schema):
    errs = []
    for keys in schema.get("unique", []):
        seen = defaultdict(list)
        for i, row in good:
            seen[tuple(row[k] for k in keys)].append(i)
        for key, rows in seen.items():
            if len(rows) > 1:
                for i in rows:
                    errs.append((i, "+".join(keys), f"duplicate of rows {', '.join(map(str, rows))}", key))
    return errs

Cross-field and cross-table rules belong here too: a location must belong to the site, a SKU must exist in the product master. For master data checks, load the master list into a set and test membership; for large masters, check in SQL against a staging table (staging Excel data in a table first).

The report

The report is the product of the check. Make it easy to act on.

def report(errors, limit=200):
    lines = [f"{len(errors)} problems found."]
    for i, col, why, val in errors[:limit]:
        lines.append(f"Row {i}, {col}: {why} (found {val!r})")
    if len(errors) > limit:
        lines.append(f"...and {len(errors) - limit} more.")
    return "\n".join(lines)

For the sample file with a bad date in row 12 and a missing quantity in row 47:

2 problems found.
Row 12, counted_on: not a date (use a real date cell) (found '3rd Oct')
Row 47, qty: required (found None)

Row numbers are the sheet's own, counting from 1 with the header as row 1, so the sender can go straight to them. Quote the offending value, truncated, and escape it if the report is shown in a web page or an email. Cap the list, and group repeated problems: "34 rows have an unknown site; first: row 5, 'Nth'". Offer the same report as a CSV for large files.

Decide: reject the file or the rows

There are two policies. All or nothing: any content error rejects the file, and nothing loads. It is simple and strict, and it keeps the database consistent with a version of the file the sender can correct and resend. Partial: good rows load, bad rows go back as a rejection file. It is friendlier when files are large and errors few, and it creates the risk of a half-loaded submission, so it needs a clear rule such as "a site's submission is complete only when it has no rejections".

Structural errors always reject the file. Whichever policy you choose, record the file's hash, the counts and the outcome, so that a resent file is recognized.

Test the checker

Keep fixtures that break each rule: a missing sheet, a renamed header, columns out of order, a text date, a negative quantity, a duplicate key, a blank required cell, an unknown site. Build them in the test suite with openpyxl, and assert the exact error list for each. One clean fixture must pass with zero errors, and a second fixture with extra columns and trailing empty rows must pass too. The schema is data, so tests can also generate a template from it and verify that the template passes its own check. That round trip catches drift between the generator and the checker.

Where the check runs

Run the check as close to the sender as you can. A web upload page can run it before accepting the file, so the person sees the error list while they are still on the page. A shared mailbox or folder job can run it on arrival and email the result back within minutes. A batch loader that finds out at 2 a.m. that the file was wrong has already lost the day. Fast feedback is the difference between a template people learn and one they resent.

Give senders a way to run the check themselves. A small command line tool, or an Excel macro-free approach such as a link to an upload page that only validates, lets them test a file before they submit it. Publish the schema in the same place as the template, so the rules and the checker never disagree.

Keep the check and the template in step. When a column is added to the template, the schema changes, the version bumps and the checker gains a rule in the same commit. A checker that lags behind the template either rejects good files or accepts bad ones, and either one erodes trust in the process.

When a template check is not enough

A template check establishes that the file has the right shape and plausible values. It does not establish that the quantities are true. For that, compare with an independent source (a control total from the site's own system, last week's figures) and flag large changes. It does not replace upload safety (size, macros, zip structure) and it does not remove the need for a person to look at an odd result. When the checks pile up into business rules that change monthly, move them from code to a table that the business can edit, or into the database as constraints. The openpyxl documentation covers reading cells and tables, and JSON Schema is a mature way to express a schema like the one above if you want a standard vocabulary.