Loading Multiple Excel Sheets Into Separate Tables

A workbook often holds several tables: customers on one tab, orders on another, a lookup list on a third. Loading it means deciding what each sheet becomes. The simple answer is one table per sheet, and the simple code is a loop. The details that matter are the ones around the loop: names that are legal, sheets that should be skipped, sheets that were renamed since last month, and the check that every sheet arrived. This post builds the loop and those guards. It belongs to the cluster on getting Excel data into a database, and each sheet goes through the same stage-check-convert path described there.

Read all sheets at once

read_excel takes a sheet_name argument. A string or number reads one sheet. None reads them all and returns a dictionary:

import pandas as pd

sheets = pd.read_excel("company.xlsx", sheet_name=None, dtype=str, keep_default_na=False)
for name, frame in sheets.items():
    print(repr(name), frame.shape)

'Customers' (120, 4)
'Orders' (845, 6)
'Lookup Lists' (30, 2)
'Scratch' (3, 1)

Each key is a sheet name exactly as written in the workbook, including spaces, and each value is a dataframe read with its first row as the header. The dtype=str option keeps every cell as text, as in the other articles of the cluster. The whole workbook is read into memory in one call, so for very large files read the sheets one at a time by name.

Notice the last line. Scratch is a sheet somebody used for notes, and a loader that loads "all sheets" would create a table for it. That is the first guard to add.

Map sheets to tables explicitly

Never derive the set of tables from whatever the workbook happens to contain. Keep a mapping in code or configuration:

EXPECTED = {
    "Customers":    "stage_customers",
    "Orders":       "stage_orders",
    "Lookup Lists": "stage_lookup",
}

missing = set(EXPECTED) - set(sheets)
unknown = set(sheets) - set(EXPECTED)
if missing:
    raise SystemExit(f"workbook is missing sheets: {sorted(missing)}")
if unknown:
    print(f"ignoring unexpected sheets: {sorted(unknown)}")

A missing sheet is an error, because it means the sender changed the file and the load would be incomplete. An unexpected sheet is a notice, since extra tabs are common and harmless, but print it so a new sheet that should be loaded does not go unnoticed. The mapping also solves naming: the table name stage_lookup is chosen by you, not copied from Lookup Lists.

If you must derive names, sanitize them. Sheet names can contain spaces, accents, punctuation and mixed case, and can be up to 31 characters. A function that lowercases, replaces runs of anything other than letters and digits with an underscore and trims the ends is enough:

import re

def table_name(sheet, prefix="stage_"):
    slug = re.sub(r"[^a-z0-9]+", "_", sheet.lower()).strip("_")
    return prefix + (slug or "sheet")

table_name("Lookup Lists") returns stage_lookup_lists. Two sheets can collide after sanitizing (Q1 Sales and Q1-Sales), so check for duplicates. And never put a sheet name into an SQL statement by string formatting unless it has passed through a function like this: a sheet named x; drop table orders is legal in Excel.

Skip hidden sheets on purpose

pandas reads hidden sheets. It does not tell you which ones are hidden. openpyxl does:

from openpyxl import load_workbook

wb = load_workbook("company.xlsx", read_only=True)
visible = [ws.title for ws in wb.worksheets if ws.sheet_state == "visible"]
hidden = [ws.title for ws in wb.worksheets if ws.sheet_state != "visible"]
print("visible:", visible)
print("hidden:", hidden)

visible: ['Customers', 'Orders', 'Lookup Lists']
hidden: ['Scratch']

sheet_state is visible, hidden or veryHidden. Whether hidden sheets are in scope is a question for the owner of the data. Some workbooks keep reference data on a hidden sheet on purpose, and others keep abandoned drafts. Put the answer in the mapping: the expected sheets are listed by name, and everything else is ignored. If a sheet in your mapping turns out to be hidden, log it, because it may mean the sender intended to remove it.

Write each sheet to its own table

With the mapping in place, loop and stage. Each table gets the provenance columns that make a value traceable:

import psycopg

BATCH_ID = 42
FILE = "company.xlsx"

with psycopg.connect("dbname=app user=loader") as conn:
    for sheet, table in EXPECTED.items():
        frame = sheets[sheet]
        cols = list(frame.columns)
        with conn.cursor() as cur:
            col_sql = ", ".join(f'"{c}"' for c in cols)
            copy_sql = (
                f'COPY {table} (batch_id, source_file, source_sheet, source_row, {col_sql}) FROM STDIN'
            )
            with cur.copy(copy_sql) as copy:
                for i, row in enumerate(frame.itertuples(index=False), start=2):
                    copy.write_row((BATCH_ID, FILE, sheet, i, *row))
        print(f"{sheet}: staged {len(frame)} rows into {table}")

Customers: staged 120 rows into stage_customers
Orders: staged 845 rows into stage_orders
Lookup Lists: staged 30 rows into stage_lookup

The table names come from the mapping and the column names come from the workbook's header cells, quoted with double quotes. If the headers are untrusted, map them to known column names too, as you did for sheets, and fail on an unexpected header. Each table must already exist with matching columns. The loop is wrapped in one connection and one transaction, so a failure on any sheet rolls back all of them, and the database never holds half a workbook. If a partial load is acceptable, commit per sheet and record which sheets succeeded.

Check each count against the sheet's data rows (validating row counts after an Excel import). The counts printed above are the numbers to compare with what the sender expects.

When the sheets have the same layout

A workbook with twelve sheets named Jan to Dec, each holding the same columns, is not twelve tables. It is one table split across tabs by a person. Load it into a single table with a column that records the sheet:

frames = []
for name, frame in sheets.items():
    if name in {"Jan", "Feb", "Mar"}:
        frame = frame.copy()
        frame["month"] = name
        frames.append(frame)
all_months = pd.concat(frames, ignore_index=True)

concat stacks the frames and aligns columns by name. If one month has an extra column or a differently spelled header, the result has a column full of blanks for the other months, which is the signal to look. Compare the header lists of the sheets before you concatenate, and fail when they differ. The sheet name itself is data (here a month), so keep it as a column, and convert it into a real date in SQL.

When sheets are related

Sometimes the sheets are the pieces of a small relational model: customers, orders that refer to customers, and a lookup list for statuses. Loading them as separate stage tables is correct, and relating them happens afterwards in SQL. Load in dependency order into the final tables: lookups first, then customers, then orders. Declare the foreign keys on the final tables, not on the stage, so that a broken reference shows up as a rejected row with a reason, not as a failed load of the stage. A query for orders whose customer is not in the customers stage finds the orphans before the final insert:

SELECT o.source_row, o.customer_id
FROM stage_orders o
LEFT JOIN stage_customers c ON c.customer_id = o.customer_id AND c.batch_id = o.batch_id
WHERE o.batch_id = 42 AND c.customer_id IS NULL;

Every row returned is an order that refers to a customer missing from the workbook. That is a data quality finding about the file, and it is better to report it than to drop the rows.

Repeatability

A workbook loaded each month needs the same treatment as any recurring load: record the file hash and a batch id, and make the final inserts idempotent. For snapshot workbooks where each file replaces the previous one, delete the old batch's rows and insert the new ones in one transaction. For workbooks that add new rows each time, key the rows and upsert. The cluster guide describes both.

When one table per sheet is the wrong design

The sheets are partitions of one table. Months, regions or years on separate tabs belong in one table with a column. See the section above.

The workbook is a report. A summary sheet with totals, charts and notes is derived from the data sheets. Load the data sheets and recompute the summary in SQL (translating Excel formulas into SQL).

The structure changes every file. If the sender adds and renames sheets freely, a mapping-based loader fails every month. Agree on a template or ask for separate files per table.

The workbook is huge. Reading all sheets into memory at once is wasteful. Read and load one sheet at a time, in read-only mode (reading large Excel files in read-only mode).

The behavior of sheet_name=None is described in the read_excel documentation, and sheet visibility in the openpyxl worksheet reference. To check a small extract as JSON, use the CSV to JSON converter.