Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Load an Excel File into a SQL Database with pandas

Take a spreadsheet people filled in and get it into a table safely: normalise the columns, pin the types, stage the rows, validate them, then merge in one transaction that either succeeds or leaves nothing behind.

Reading a spreadsheet is easy. Putting one into a database that other people query is where the care is needed, because a workbook that people fill in contains things a schema does not expect: a blank row left at the bottom, a "Total" line, an ID typed with a leading apostrophe, a date entered two different ways, and the same key twice because someone pasted a block in again.

The pattern that survives all of that is the same one used for any untrusted import: normalise, pin the types, stage, validate, then merge in a single transaction. This guide is part of Moving Data Between Excel and Databases.

What an uploaded workbook contains that a table does not want A submitted spreadsheet typically carries a title row above the headers, trailing blank rows, a total line, identifiers stored as text with leading zeros, dates in mixed formats and duplicated keys. Each is removed or coerced at a specific step before the rows are allowed near the live table. Six things in a submitted workbook, and where each is dealt with a title above the headers columns come back as Unnamed: 0 — fix with skiprows blank and total rows dropna on the key column and a filter on the label mixed date formats to_datetime with errors= "coerce", then count NaT IDs with leading zeros dtype={"order_id": str} at the read, not after duplicate keys counted in the staging table, reported by value a re-submitted file delete the period first, so the load is repeatable

Prerequisites

Bash
pip install pandas openpyxl sqlalchemy

The examples use SQLite so they run with no server. Everything transfers to Postgres, MySQL or SQL Server by changing the URL — and by remembering that those databases enforce constraints SQLite will happily ignore, which is an argument for testing the load against the real engine before it runs unattended.

Step 1: Read the file on your terms

Two arguments do most of the defensive work: dtype pins the columns whose type pandas would otherwise guess, and usecols limits the read to the columns you actually accept, so an extra column someone added on the right cannot reach the table.

Python
import pandas as pd

RAW = pd.read_excel(
    "submissions/july-actuals.xlsx",
    sheet_name="Actuals",
    skiprows=2,                       # the file has a title and a blank line first
    usecols="A:E",                    # ignore the working columns to the right
    dtype={"Order Ref": str, "Cost Centre": str},
    na_values=["", "-", "n/a", "N/A", "TBC"],
)
print(RAW.dtypes)
print(f"{len(RAW):,} raw rows")

na_values is the argument people find late. Spreadsheets are full of human placeholders — a dash, n/a, TBC — and without this every one of them makes its column object, which then fails to insert into a numeric field with an error that names the type rather than the cell.

Step 2: Normalise the shape

Database columns and spreadsheet headings rarely match. Do the renaming and cleaning in one visible place rather than scattering it through the load:

Python
COLUMNS = {
    "Order Ref": "order_id",
    "Sales Area": "region",
    "Booking Date": "order_date",
    "Net Value": "amount",
    "Cost Centre": "cost_centre",
}


def normalise(raw):
    df = raw.rename(columns=COLUMNS)

    missing = set(COLUMNS.values()) - set(df.columns)
    if missing:
        raise ValueError(f"upload is missing column(s): {sorted(missing)}")

    df = df.dropna(subset=["order_id"])                       # blank trailing rows
    df = df[~df["order_id"].astype(str).str.lower()
              .isin({"total", "subtotal", "grand total"})]    # summary lines

    df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
    df["amount"] = pd.to_numeric(df["amount"], errors="coerce").round(2)
    df["region"] = df["region"].str.strip().str.title()
    df["order_id"] = df["order_id"].str.strip()

    df["period"] = df["order_date"].dt.strftime("%Y-%m")
    return df.reset_index(drop=True)


df = normalise(RAW)

errors="coerce" turns anything unparseable into NaT or NaN rather than raising, which is what you want here: the goal is to collect every bad value and report them together, not to stop at the first one. Rounding the amount at this point rather than at the end avoids a float that ends in .00000000001 being stored in a NUMERIC(12,2) column and rounding somewhere you cannot see.

Step 3: Refuse the file if it is not loadable

Now count what the coercions found, and stop before anything is written:

Python
def check(df):
    problems = []

    bad_dates = int(df["order_date"].isna().sum())
    if bad_dates:
        sample = df.loc[df["order_date"].isna(), "order_id"].head(5).tolist()
        problems.append(f"{bad_dates} unparseable date(s), e.g. order {sample}")

    bad_amounts = int(df["amount"].isna().sum())
    if bad_amounts:
        problems.append(f"{bad_amounts} non-numeric amount(s)")

    dupes = df["order_id"][df["order_id"].duplicated()].unique().tolist()
    if dupes:
        problems.append(f"{len(dupes)} duplicated order id(s): {dupes[:5]}")

    if df["period"].nunique() > 1:
        problems.append(f"file spans several periods: {sorted(df['period'].unique())}")

    if problems:
        raise ValueError("upload rejected:\n  - " + "\n  - ".join(problems))
    return df
Two rejection messages for the same broken upload A message that stops at the first error and names only the exception type sends the submitter back for another attempt per problem. A message that lists every problem with example order references lets them fix all four in one pass and resubmit once. stops at the first problem ValueError: could not convert string which row? which column? which value? fix one thing, resubmit, discover the next — four round trips for four mistakes collects every problem first upload rejected: - 3 unparseable dates, e.g. order A-1042 - 2 duplicated ids: A-1007, A-1019 every problem, with rows they can find — one fix, one resubmission

Reporting every problem at once, with example values, is the difference between one email to the submitter and five. Naming the offending order IDs matters more than the count — the person fixing the file needs to find the rows, and "three unparseable dates" sends them scrolling. The same reasoning drives Validate Excel Columns Before Import with pandas.

Step 4: Stage, then merge in one transaction

Write to a staging table first. It costs one extra statement and it means the live table never sees a partially loaded or later-rejected file:

Python
from sqlalchemy import create_engine, text

engine = create_engine("sqlite:///sales.db")

DTYPES = None      # let SQLAlchemy infer for SQLite; pin explicitly on other engines


def load(df, table="sales_actuals"):
    period = df["period"].iat[0]
    staging = f"{table}__staging"

    df.to_sql(staging, engine, if_exists="replace", index=False,
              chunksize=5_000, method="multi", dtype=DTYPES)

    with engine.begin() as conn:                       # commits, or rolls back entirely
        staged = conn.execute(text(f"SELECT COUNT(*) FROM {staging}")).scalar()
        if staged != len(df):
            raise RuntimeError(f"staged {staged} rows, expected {len(df)}")

        removed = conn.execute(
            text(f"DELETE FROM {table} WHERE period = :p"), {"p": period}).rowcount
        conn.execute(text(
            f"INSERT INTO {table} (order_id, region, order_date, amount, "
            f"cost_centre, period) "
            f"SELECT order_id, region, order_date, amount, cost_centre, period "
            f"FROM {staging}"))
        conn.execute(text(f"DROP TABLE {staging}"))

    return {"period": period, "replaced": removed, "inserted": len(df)}


print(load(check(df)))

Three details carry the weight. engine.begin() gives one transaction for the delete, the insert and the drop, so an error at any point leaves the live table untouched. Deleting the period before inserting makes the load idempotent — running the same file twice produces the same table, which is exactly what you need when a retry fires after an ambiguous failure. And naming the columns in the INSERT ... SELECT rather than relying on SELECT * protects the load from a column-order change in either table.

What a failure looks like with and without a transaction Without a transaction, the delete commits and the insert fails, leaving the live table missing an entire period until someone restores it. Inside engine.begin, the same failure rolls the delete back, so readers see the previous load and the submitter gets an error naming the problem. autocommit per statement DELETE — committed INSERT — fails ✗ the period is now missing entirely readers see an empty month; recovery needs a restore or a re-run nobody has scheduled inside engine.begin() DELETE — pending INSERT — fails ✗ both are rolled back together the previous load is still there, and the error goes back to whoever submitted the file A load either happened or it did not — there is no useful state in between

Step 5: Speed the insert up when the file is large

to_sql inserts row by row unless you tell it otherwise. Two arguments change the profile substantially:

Python
df.to_sql("sales_staging", engine, if_exists="replace", index=False,
          chunksize=10_000,        # rows per round trip
          method="multi")          # one multi-row INSERT per chunk

method="multi" builds a single statement with many value tuples, which cuts network round trips dramatically on a remote database — typically several times faster for wide files. Keep chunksize bounded, though: some drivers have a parameter limit per statement (SQL Server's is 2,100), and a chunk of 10,000 rows across 8 columns exceeds it. If the load runs into the hundreds of thousands of rows regularly, the database's own bulk loader — COPY in Postgres, LOAD DATA in MySQL, bcp for SQL Server — beats anything pandas can do, and pandas' job becomes writing a clean CSV for it.

Common pitfalls and gotchas

SymptomCauseFix
IDs lost their leading zerospandas inferred an integer columndtype={"order_id": str} at the read
Every column is objectHuman placeholders like - and TBCList them in na_values
Columns named Unnamed: 0A title row above the headersskiprows, or header=
Rows doubled after a rerunAppend without deleting firstDelete the period, then insert, in one transaction
A month vanished from the tableDelete committed, insert failedWrap both in engine.begin()
to_sql takes minutesRow-by-row insertschunksize plus method="multi"
Too many parameters errorChunk exceeds the driver's limitLower chunksize
Amounts differ by a centFloat rounding at the boundaryRound before writing; store NUMERIC

Performance and scale notes

Reading the workbook is usually the slow half: openpyxl parses XML, so a 200,000-row upload takes tens of seconds regardless of what happens afterwards. If the same file is loaded repeatedly, convert it once to CSV or Parquet and load from that — Convert Excel to CSV with Python covers the conversion, and it typically cuts the read to a fraction of the time.

On the database side, the expensive part of a large load is index maintenance. For a bulk refresh of a big table it is often faster to drop the non-clustered indexes, load, and rebuild them — but only if the table is not being read during the load, which is another reason for the staging pattern.

Conclusion

Treat an uploaded workbook as untrusted input. Pin the types at the read, normalise the columns in one visible mapping, coerce dates and numbers with errors="coerce" so problems are collected rather than thrown one at a time, and reject the file with a message that names the offending rows. Then stage it, verify the count, and merge with a delete-then-insert inside a single transaction so the load is both atomic and repeatable. That is what makes a spreadsheet import something you can leave running.

Frequently asked questions

Should I use if_exists="append" straight onto the live table? Only for a table nobody reads while the load runs. Otherwise append into a staging table, validate, and move the rows across inside one transaction so a bad file is never visible.

How do I make re-running the same file safe? Delete the period or batch you are about to load, then insert. That makes the load idempotent — a retry after a half-finished run produces the same table rather than duplicate rows.

Why did my text IDs turn into numbers? pandas infers types per column, so 00123 becomes 123. Pass dtype={"order_id": str} to read_excel, before any conversion has happened.

to_sql is slow — what are the options? Pass chunksize and method="multi" to batch the inserts, and drop non-essential indexes during a bulk load. For very large files, most databases have a native bulk loader that beats any row-by-row path.

Up to the parent guide:

Related guides: