Guide

Advanced Data Transformation And CleaningDeep dive

Validate Excel Columns Before Import with pandas

Check an incoming workbook's sheet, headers, column order and required fields with pandas before a single row reaches your pipeline — with clear, actionable failure messages.

Most import failures are not subtle. The sheet was renamed, someone inserted a column, or a header reads Qty this month and Quantity last month. Catching those before any row is processed turns a confusing downstream error into a one-line message a submitter can act on. This guide, part of Validating Excel Data with Python, builds that structural gate.

Prerequisites

Bash

pip install pandas openpyxl

Check the sheet before reading it

pd.ExcelFile reads only the workbook's structure, so you can inspect sheet names without loading a single row — useful when the file is large and the sheet may not be there at all:

Python

import pandas as pd

def open_sheet(path, sheet="Orders"):
    book = pd.ExcelFile(path)
    if sheet not in book.sheet_names:
        close = [s for s in book.sheet_names if s.strip().lower() == sheet.lower()]
        if close:
            raise ValueError(
                f"sheet {sheet!r} not found, but {close[0]!r} looks like it "
                "— fix the tab name or update the importer"
            )
        raise ValueError(f"sheet {sheet!r} not found; file contains {book.sheet_names}")
    return book.parse(sheet, dtype=object)

df = open_sheet("submitted.xlsx")
print(df.shape)

Listing the sheets that are present turns "file is wrong" into "you sent the Q1 tab, we need Orders". The near-match branch handles the most common version of the problem — a tab called orders with a trailing space — without silently accepting it.

Normalise headers before comparing them

Header text arrives with stray spaces, non-breaking spaces, line breaks from wrapped cells and inconsistent case. Normalise first, then compare:

Python

import re
import pandas as pd

def normalise(name):
    text = str(name).replace("\xa0", " ")          # non-breaking space
    text = re.sub(r"\s+", " ", text).strip()       # collapse whitespace incl. newlines
    return text.lower().replace(" ", "_")

def normalise_headers(df):
    df = df.copy()
    df.columns = [normalise(c) for c in df.columns]
    unnamed = [c for c in df.columns if c.startswith("unnamed:")]
    if unnamed:
        df = df.drop(columns=unnamed)              # blank columns pandas auto-named
    return df

df = normalise_headers(df)
print(list(df.columns))

Dropping the unnamed: columns is worth doing early. They appear whenever the sheet has a stray value or formatting to the right of the real table, and they otherwise trip every "unexpected column" check you write afterwards.

Map aliases to canonical names

A column that is Qty in one submission and Quantity in the next is a naming problem, not a data problem. Keep the mapping in one dictionary:

Python

import pandas as pd

ALIASES = {
    "qty": "quantity",
    "quantity_ordered": "quantity",
    "unit_cost": "unit_price",
    "price": "unit_price",
    "order_no": "order_id",
    "order_number": "order_id",
    "date": "order_date",
}

def apply_aliases(df):
    used = {c: ALIASES[c] for c in df.columns if c in ALIASES}
    if used:
        print("renamed:", used)
    return df.rename(columns=ALIASES)

df = apply_aliases(df)

Logging which aliases fired matters more than it looks: when a report's numbers change unexpectedly, "this month the file used price rather than unit_price" is often the whole explanation. Keeping the map explicit also stops the tempting fuzzy-matching approach, which eventually maps discount_price onto unit_price and produces a report nobody can reconcile.

Assert the contract

With headers canonical, the actual check is small — and its message should say what to do:

Python

import pandas as pd

REQUIRED = {"order_id", "region", "order_date", "quantity", "unit_price"}
OPTIONAL = {"notes", "sales_rep"}

def check_columns(df, path):
    present = set(df.columns)
    missing = REQUIRED - present
    unexpected = present - REQUIRED - OPTIONAL

    problems = []
    if missing:
        problems.append(f"missing required column(s): {sorted(missing)}")
    if unexpected:
        problems.append(f"unexpected column(s), ignored: {sorted(unexpected)}")
    if df.empty:
        problems.append("no data rows below the header")

    if missing or df.empty:
        raise ValueError(f"{path} failed structural validation — " + "; ".join(problems))
    for note in problems:
        print("warning:", note)

    return df[sorted(REQUIRED | (present & OPTIONAL))]

df = check_columns(df, "submitted.xlsx")

Note the asymmetry: missing columns are fatal, unexpected ones are a warning. A submitter who adds a comments column has not broken anything, and rejecting their file teaches them that the process is brittle. Selecting the columns explicitly at the end also fixes the order for everything downstream, so no later code can depend on the submitter's layout.

When the header row is not row 1

Exports often carry a title and a timestamp above the real header. Find the header rather than hardcoding skiprows, so a file with one extra line does not fail:

Python

import pandas as pd

def find_header_row(path, sheet, required, max_scan=10):
    probe = pd.read_excel(path, sheet_name=sheet, header=None, nrows=max_scan, dtype=object)
    for i, row in probe.iterrows():
        cells = {normalise(v) for v in row if pd.notna(v)}
        if required <= cells:
            return i
    raise ValueError(
        f"no header row containing {sorted(required)} found in the first {max_scan} rows"
    )

header_row = find_header_row("export.xlsx", "Export", {"order_id", "quantity"})
df = pd.read_excel("export.xlsx", sheet_name="Export", header=header_row, dtype=object)
print("header found on sheet row", header_row + 1)

This is the same problem reading Excel files with pandas solves with skiprows, made adaptive: instead of asserting that the junk is two rows deep, you assert what the header must contain.

Putting the gate together

Python

def load_validated(path, sheet="Orders"):
    df = open_sheet(path, sheet)
    df = normalise_headers(df)
    df = apply_aliases(df)
    return check_columns(df, path)

try:
    orders = load_validated("submitted.xlsx")
except ValueError as exc:
    raise SystemExit(f"import rejected: {exc}")

print(f"structure OK — {len(orders)} row(s), columns {list(orders.columns)}")

Four small functions, each with one job, and a single entry point the rest of the pipeline calls. Row-level checks — types, ranges, duplicates — run after this gate, on data whose shape is already guaranteed.

Fatal, warning or silent?

Not every structural surprise deserves the same response, and deciding once saves an argument every month:

The right-hand column is the one teams get wrong most often, usually by being too strict. Rejecting a file because the submitter reordered two columns or left a blank column at the edge makes the import look fragile and trains people to work around it. Absorb everything that carries no information, warn about everything that might explain a future discrepancy, and stop only when the file genuinely cannot be processed.

Common pitfalls and gotchas

Symptom	Cause	Fix
`KeyError` deep inside the transform	Column checked too late, or not at all	Run the structural gate before anything else
Header comparison fails on identical-looking text	Non-breaking spaces or newlines in the header cell	Normalise with `\xa0` replacement and whitespace collapsing
`Unnamed: 7` columns appear	Stray value or formatting right of the table	Drop columns whose normalised name starts with `unnamed:`
Import breaks when a column is added	Code selected columns by position	Select by name after validating
Empty report, no error	Sheet had headers but no rows	Treat an empty frame as a structural failure
Wrong sheet silently read	`sheet_name=0` picked whatever was first	Name the sheet and verify it exists

Performance and scale notes

pd.ExcelFile(path) parses the workbook structure without materialising rows, so the sheet check is cheap even on a large file. Header discovery with nrows=10 reads only the top of the sheet. The expensive step is the full read, which is why the gate is arranged to fail before it: on a 200 MB submission with the wrong tab name, this ordering is the difference between a one-second rejection and a two-minute one. When you only need a subset of columns, pass usecols on the real read once the names are known, and keep the gate itself free of row-level work so its cost stays proportional to the header rather than to the data.

Conclusion

A structural gate is four questions asked in order: is the sheet there, do the headers normalise to names I know, are the required ones present, and is there any data. Answer them before reading rows, keep aliases in an explicit map, make missing columns fatal and unexpected ones a warning, and every downstream stage can assume a known shape.

Frequently asked questions

Should column order matter? Usually not. Validate by name and select the columns you need in your own order, so an extra column inserted at position two cannot break the import.

How do I handle a column that gets renamed every quarter? Keep an alias map from every known spelling to your canonical name, apply it after normalising case and whitespace, and log which alias was used.

Is it better to fail or to fill in a missing column? Fail. A silently added empty column produces a report full of zeros that nobody questions, which is worse than an import that stops with a clear message.

How do I check the header row when it is not the first row? Find it by scanning the first few rows for your required names, then re-read the sheet with header set to that row index.

Up to the parent guide:

Validating Excel Data with Python — where this gate sits in the wider validation layer.

Related guides:

Check Excel Data Types with pandas — the row-level checks that run next.
Find Duplicate Rows in Excel with Python — cross-row validation after the shape is known.
How to Read Excel with pandas, Step by Step — sheets, headers and skiprows in depth.
Read Specific Columns from Excel with pandas — usecols once the names are trusted.