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

Stream Excel Rows into a Database with openpyxl

Load a large Excel sheet into a database without ever holding it in memory — read rows with openpyxl's read-only mode, convert and validate each row, insert in batches inside a transaction, divert bad rows to a rejects table and make the load safe to rerun.
Large files & performanceDatabases, APIs & sourcesopenpyxl

pandas is the natural way to read an Excel file, but it builds the whole sheet in memory before anything is written. For a large export going straight into a database — a year of transactions, a full product catalogue — that is unnecessary: each row only needs to be read, checked and inserted. openpyxl's read-only mode yields rows one at a time, and the database's executemany inserts them in batches. Memory stays flat however long the sheet is. This guide, part of Working with Large Excel Files in Python, builds a streaming loader with validation, a rejects table and a safe rerun, using SQLite so every example runs as written.

A streaming load, one batch at a time openpyxl read-only mode yields rows as tuples. Each batch of five thousand rows is converted and validated, good rows are inserted with executemany and bad rows go to a rejects table, and the whole load commits in one transaction before the workbook is closed. Open read-only load_workbook Take a batch islice(rows, 5000) Convert + check per row executemany good → table Commit one transaction

Prerequisites

Bash
pip install openpyxl pandas

sqlite3 ships with Python; the same pattern works with any DB-API driver, such as psycopg for PostgreSQL. A sample export — small here so it runs quickly, with two bad rows mixed in:

Python
import datetime as dt
import sqlite3
from itertools import islice

from openpyxl import Workbook, load_workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet("Orders")
ws.append(["Order ID", "Date", "Region", "Product", "Qty", "Price"])
for i in range(1, 12001):
    ws.append([i, dt.datetime(2026, 1, 1) + dt.timedelta(minutes=i), "North" if i % 2 else "South",
               f"SKU-{i % 50:03d}", i % 20 + 1, round(1 + (i % 97) / 10, 2)])
ws.append([12001, "not a date", "East", "SKU-001", 3, 4.5])
ws.append([12002, dt.datetime(2026, 2, 1), "West", "SKU-002", -5, 2.0])
wb.save("orders_export.xlsx")

Writing the sample with write_only=True is the same idea in reverse: rows go to disk as they are appended.

Step 1 — Open read-only and map the header

Read-only mode parses the sheet lazily as you iterate. Take the header row first and look columns up by name, so a reordered export does not load prices into the quantity column:

Python
wb = load_workbook("orders_export.xlsx", read_only=True, data_only=True)
ws = wb["Orders"]
rows = ws.iter_rows(values_only=True)
header = [str(h).strip() for h in next(rows)]
COLUMNS = {"Order ID": "order_id", "Date": "order_date", "Region": "region",
           "Product": "product", "Qty": "qty", "Price": "price"}
missing = set(COLUMNS) - set(header)
if missing:
    raise ValueError(f"missing columns: {sorted(missing)}")
index = {target: header.index(source) for source, target in COLUMNS.items()}
print(index)

data_only=True returns the cached results of any formulas instead of the formula text. values_only=True yields plain tuples rather than cell objects, which is both faster and lighter. Read-only mode itself is covered in Speed Up openpyxl with Read-Only Mode.

Step 2 — Convert and validate each row

Each row is converted to the database's types and checked. A row that fails is not an exception to stop on; it goes to a rejects list with the reason:

Python
def convert(row, line):
    rec = {name: row[i] for name, i in index.items()}
    problems = []
    if not isinstance(rec["order_date"], dt.datetime):
        problems.append("date is not a date")
    if not isinstance(rec["qty"], (int, float)) or rec["qty"] <= 0:
        problems.append("qty must be positive")
    if problems:
        return None, (line, "; ".join(problems), repr(row)[:200])
    rec["order_date"] = rec["order_date"].isoformat(sep=" ")
    rec["qty"] = int(rec["qty"])
    return tuple(rec[c] for c in ["order_id", "order_date", "region", "product", "qty", "price"]), None

Keeping the sheet line number — the Excel row the record came from — makes every reject traceable to a cell someone can open. Dates are stored as ISO text, which SQLite compares and sorts correctly; a server database would take the datetime directly.

Step 3 — Insert in batches inside one transaction

islice takes the next batch from the row iterator without materialising the rest. Each batch is converted, the good rows are inserted with one executemany call, and the rejects are collected. The with con: block wraps the whole load in a transaction:

Python
con = sqlite3.connect("orders.db")
con.executescript("""
    DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS rejects;
    CREATE TABLE orders (order_id INTEGER PRIMARY KEY, order_date TEXT NOT NULL,
                         region TEXT, product TEXT, qty INTEGER, price REAL);
    CREATE TABLE rejects (sheet_line INTEGER, problem TEXT, raw TEXT);
""")

BATCH = 5000
line = 1                                  # the header was line 1
loaded = rejected = 0
with con:
    while batch := list(islice(rows, BATCH)):
        good, bad = [], []
        for row in batch:
            line += 1
            if all(v is None for v in row):
                continue                  # skip blank rows
            rec, rej = convert(row, line)
            (good if rec else bad).append(rec or rej)
        con.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?)", good)
        con.executemany("INSERT INTO rejects VALUES (?, ?, ?)", bad)
        loaded += len(good)
        rejected += len(bad)
        print(f"line {line}: {loaded} loaded, {rejected} rejected")
wb.close()
print(con.execute("SELECT sheet_line, problem FROM rejects").fetchall())
# [(12002, 'date is not a date'), (12003, 'qty must be positive')]

If anything raises mid-load — a disk full, a constraint violation — the transaction rolls back and the table is left as it was, not half-filled. Closing the workbook releases the file handle that read-only mode keeps open while iterating.

Loading 200,000 rows into SQLite Measured on an 8.6 MB workbook with 200,000 rows. Streaming with openpyxl read-only mode and executemany peaked at 73 MB and took 5.4 seconds. Reading the sheet with pandas and writing it with to_sql peaked at 245 MB and took 6.9 seconds. A bare interpreter with sqlite3 and openpyxl imported used 48 MB. stream + executemany 73 MB read_excel + to_sql 245 MB peak process memory; 5.4 s versus 6.9 s

Step 4 — Check the load against the sheet

Streaming has no DataFrame to inspect afterwards, so reconcile with counts: every non-blank data line is either loaded or rejected, and the table holds exactly the loaded rows:

Python
data_lines = line - 1
count = con.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
print(data_lines, loaded, rejected, count)       # 12002 12000 2 12000
assert count == loaded and loaded + rejected == data_lines

On the full 200,000-row test file the same loader finished in 5.4 seconds with a peak of 73 MB, against 245 MB for read_excel followed by to_sql. The speed is similar; the memory is not, and it stays flat as the sheet grows. Reconciling against totals supplied by the source system is covered in Reconcile Excel Totals Against Control Totals with Python.

Step 5 — Make reruns safe

A scheduled load will eventually run twice on the same file. With order_id as the primary key, a second run fails on the first duplicate and rolls back — safe, but noisy. Two patterns make reruns clean. Replace the target atomically by loading into a staging table and swapping at the end; or record each loaded file and skip it next time:

Python
import os

con.execute("CREATE TABLE IF NOT EXISTS loads (file TEXT, mtime REAL, rows INTEGER, PRIMARY KEY (file, mtime))")
key = ("orders_export.xlsx", os.path.getmtime("orders_export.xlsx"))
if con.execute("SELECT 1 FROM loads WHERE file = ? AND mtime = ?", key).fetchone():
    print("already loaded, skipping")
else:
    with con:
        con.execute("INSERT INTO loads VALUES (?, ?, ?)", (*key, loaded))
    print("recorded load")

In a real job the loads insert goes inside the same transaction as the data, so a file is marked as loaded only if its rows committed. For sheets that are edited and reloaded, an upsert keyed on the order ID updates changed rows instead; see Upsert Excel Rows into a Database Table with Python.

Two ways to make a load safe to rerun Loading into a staging table and swapping it for the target replaces the data completely on every run and suits full extracts. Recording each loaded file with its modification time skips files already loaded and suits folders that receive new files, but needs an upsert if a file can change. Staging table + swap target always matches the file readers never see a half load reloads everything each run suits full extracts Load ledger each file loaded once cheap for many small files edited files need an upsert suits incoming folders

Which pattern fits depends on what the file is. A full extract that replaces yesterday's is best loaded into staging and swapped; a folder that receives a new file every day is best tracked with a ledger. Either way, the load becomes something you can run again after a failure without thinking about what the previous attempt left behind.

Common pitfalls

  • Trusting the sheet's dimensions. Some exporters write a wrong used range; read-only mode may then yield thousands of empty rows. Skip rows whose values are all None, as the loader does.
  • Converting the whole sheet first. list(ws.iter_rows()) defeats streaming by materialising every row. Iterate, or take batches with islice.
  • Committing per row. One transaction per row is dramatically slower. Commit once, or once per large batch if the load must be resumable.

Conclusion

To load a big sheet into a database without loading it into memory, open it with openpyxl's read-only mode, map the header by name, convert and validate each row, and insert batches with executemany inside one transaction, sending bad rows to a rejects table with their sheet line numbers. Reconcile loaded plus rejected against the lines read, and record each load so reruns are safe.

Frequently asked questions

How do I load a large Excel file into a database without running out of memory? Open it with openpyxl's load_workbook(read_only=True), iterate the rows with iter_rows(values_only=True), and insert them in batches of a few thousand with executemany. Only one batch is in memory at a time.

Is streaming slower than pandas read_excel plus to_sql? Not necessarily. In our test on 200,000 rows, streaming into SQLite took 5.4 seconds with a 73 MB peak, while reading with pandas and calling to_sql took 6.9 seconds with a 245 MB peak.

What batch size should I use? A few thousand rows per executemany call is a good default. Much smaller batches pay per-call overhead; much larger ones hold more rows in memory without making inserts noticeably faster.

How do I make the load safe to run twice? Load into a staging table and replace the target in one transaction at the end, or use the file's name and modification time as a load ID and skip files that are already recorded.

More on large files & performance

More on large files & performance →