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

Moving Data Between Excel and Databases

Excel as the interface, the database as the source of truth: query to workbook with SQLAlchemy, load a spreadsheet into a table safely, keep types intact in both directions, and refresh on a schedule.

Most reporting sits between two worlds. The numbers live in a database — a warehouse, a Postgres instance behind an application, a SQL Server the finance system writes to — and the people who need them work in Excel. The job is not to choose between the two but to move data across the boundary reliably, in both directions, without losing types, duplicating rows or hard-coding a password into a script.

This topic within Advanced Data Transformation and Cleaning covers that boundary: connecting from Python, exporting query results into a formatted workbook, loading a spreadsheet back into a table without damaging what is already there, keeping dates and identifiers intact through the round trip, and running the whole thing on a schedule.

The round trip between a database and a workbook Python reads from the database with SQLAlchemy and writes a formatted workbook for readers. In the other direction it reads a spreadsheet people have filled in, validates it, loads it into a staging table and only then merges it into the live table. database the source of truth Python SQLAlchemy + pandas workbook the interface read_sql to_excel read_excel to_sql Out to readers, back from contributors The return leg is the risky one: a spreadsheet arrives with edited types, blank rows and duplicated keys, so it is staged and validated before anything touches the live table.

Connect once, in one place

Every database Python talks to is reachable through a SQLAlchemy URL, which means the only thing that changes between SQLite, Postgres, MySQL and SQL Server is a string:

Python
from sqlalchemy import create_engine

# sqlite:///reports.db
# postgresql+psycopg://user:pw@host:5432/sales
# mysql+pymysql://user:pw@host:3306/sales
# mssql+pyodbc://user:pw@host/sales?driver=ODBC+Driver+18+for+SQL+Server
engine = create_engine(os.environ["REPORT_DB_URL"], pool_pre_ping=True)

Two habits are worth adopting at this line rather than later. Read the URL from the environment, never from the source or a committed config file — it contains a password, and a database URL in version control is the most common credential leak in reporting code. And set pool_pre_ping=True, because a scheduled job holds its engine across a long idle period and a stale connection otherwise surfaces as a confusing error on the first query of the morning.

The engine is created once per process. Creating one inside a loop opens a new pool per iteration and exhausts the server's connection limit long before the report finishes.

Export a query into a workbook people will read

The naive export — pd.read_sql(...).to_excel(...) — produces a correct, unreadable grid. The version worth scheduling adds the formatting that makes the file usable, which is where this topic meets Formatting and Charting Excel Reports with Python:

Python
import pandas as pd
from sqlalchemy import text

QUERY = text("""
    SELECT region, order_date, SUM(amount) AS amount
    FROM orders
    WHERE order_date >= :start AND order_date < :end
    GROUP BY region, order_date
    ORDER BY region, order_date
""")

with engine.connect() as conn:
    df = pd.read_sql(QUERY, conn, params={"start": "2026-07-01", "end": "2026-08-01"})

with pd.ExcelWriter("regional.xlsx", engine="openpyxl",
                    datetime_format="yyyy-mm-dd") as writer:
    df.to_excel(writer, sheet_name="Detail", index=False)
    (df.groupby("region", as_index=False)["amount"].sum()
       .to_excel(writer, sheet_name="Summary", index=False))

The :start and :end placeholders with a params dictionary are not stylistic. A query assembled with an f-string from a value that came out of a spreadsheet cell is an injection waiting to happen, and bound parameters also let the database cache the plan. datetime_format on the writer saves a formatting pass: without it, dates land as serial numbers or as text depending on the engine. Export SQL Query Results to Excel with Python covers multi-sheet exports, column widths and chunked reads for large results.

Stage the return leg

Loading a spreadsheet into a database is the direction that goes wrong, because a workbook filled in by people contains everything a database schema does not expect: a blank row someone left at the bottom, a total row, an ID typed as text, a date in two formats, the same key twice. Writing that straight into a live table with if_exists="append" publishes all of it.

Loading a spreadsheet through a staging table The workbook is read into a DataFrame and written to a staging table. Validation runs against the staging table — row counts, required columns, duplicate keys, type checks. Only if every check passes does a single transaction merge the rows into the live table; otherwise the staging table is dropped and the live table is untouched. upload.xlsx filled in by people staging table everything, as typed checks keys · types · counts live table one transaction pass Nothing reaches the live table until every check has passed on failure: drop staging, report which rows the live table never saw the bad file
Python
def load_via_staging(df, engine, table="sales_actuals"):
    staging = f"{table}__staging"
    df.to_sql(staging, engine, if_exists="replace", index=False, chunksize=5_000)

    with engine.begin() as conn:                 # one transaction, commits or rolls back
        dupes = conn.execute(text(
            f"SELECT COUNT(*) FROM (SELECT order_id FROM {staging} "
            f"GROUP BY order_id HAVING COUNT(*) > 1) d")).scalar()
        if dupes:
            raise ValueError(f"{dupes} duplicate order_id value(s) in the upload")

        conn.execute(text(f"DELETE FROM {table} WHERE period = :p"), {"p": "2026-07"})
        conn.execute(text(f"INSERT INTO {table} SELECT * FROM {staging}"))
        conn.execute(text(f"DROP TABLE {staging}"))

engine.begin() is the important call: it opens a transaction that commits on a clean exit and rolls back on any exception, so a failure halfway through the delete-and-insert leaves the live table exactly as it was. The delete-then-insert pattern also makes the load idempotent — re-running the same file for the same period produces the same table rather than a doubled one, which matters as soon as anything retries. Load an Excel File into a SQL Database with pandas works through the type mapping and the validation in detail.

Decide where the work happens

The single biggest performance decision at this boundary is not which library you use but which side does the aggregation. A database can group ten million rows without sending any of them anywhere; pandas can only group rows it has already received, which means transferring them across the network, materialising them in memory, and then discarding almost all of them.

Aggregating in SQL versus aggregating in pandas Grouping in the database transfers only the summary rows, so the report holds a few hundred rows in memory. Selecting everything and grouping in pandas transfers every detail row across the network first, then discards almost all of them — the same answer for far more time and memory. GROUP BY in the database 10,000,000 rows in the table group 220 summary rows cross the network seconds, and a few megabytes indexes on the filter and group columns do the work you would otherwise pay for twice SELECT * then groupby in pandas 10,000,000 rows in the table all of it 10,000,000 rows cross the network minutes, and gigabytes of memory to produce the identical 220 rows — and to fail on the day the table doubles Push filters and grouping down; keep shaping and formatting in Python

The useful division is simple. Anything that reduces rows — filters, joins against reference tables, GROUP BY, DISTINCT — belongs in SQL, where indexes exist and nothing has to be transferred. Anything that reshapes what is left — pivoting for presentation, deriving display columns, formatting, ordering the sheets — belongs in pandas, where the code is easier to read and the result is going into a workbook anyway.

The exception is worth naming, because it is the reason people end up on the wrong side: a query that is awkward to express in SQL, run once a month over a small table, is not worth an hour of window-function debugging. The rule is about magnitude, not principle. When the transfer is a few thousand rows, do whatever is clearest; when it is millions, the database is not optional.

Give the report its own credentials

A reporting job needs to read a handful of tables and, at most, write to one. It does not need the application's account. Creating a dedicated read-only role costs five minutes and removes an entire category of accident — a mistyped statement in a script that only has SELECT cannot delete anything:

Sql
CREATE ROLE report_reader LOGIN PASSWORD 'set-from-a-secret-store';
GRANT CONNECT ON DATABASE sales TO report_reader;
GRANT USAGE ON SCHEMA public TO report_reader;
GRANT SELECT ON orders, customers, products TO report_reader;
-- and nothing else: no INSERT, no UPDATE, no DDL

Where the job also loads data back, give it a second role with write access to its own staging schema only, and keep the two connection strings separate in the environment. That way the export half of the pipeline physically cannot modify anything, and the load half is scoped to the tables it owns.

Two operational habits go with this. Set a statement timeout so a runaway report query cannot hold locks or saturate the server for an hour — most databases accept it per session, and a reporting connection is exactly the place for it. And name the connection, through the application name in the URL or a SET application_name, so that when a DBA sees a heavy query at 06:00 they can tell which report it belongs to instead of guessing.

Python
engine = create_engine(
    os.environ["REPORT_DB_URL"],
    pool_pre_ping=True,
    connect_args={"application_name": "monthly-regional-report",
                  "options": "-c statement_timeout=120000"},   # 2 minutes
)

Both settings turn a report that can quietly become an incident into one that fails on its own terms, in a way that names itself in the server's logs.

Protect the types across the boundary

Excel and SQL disagree about types in ways that bite quietly rather than loudly. Four cases account for nearly all of it:

What it isWhat goes wrongWhat to do
An identifier like 00123Excel drops the leading zeros; pandas reads it as an integerRead with dtype={"order_id": str} and store as text
An ID column with one blankBecomes float64, so 1 displays as 1.0Use pandas' nullable Int64, or read as str
A dateArrives as a serial number, a string or a Timestamp depending on the pathCoerce with pd.to_datetime(..., errors="coerce") and check for NaT
A currency amountRounding differences between float and NUMERICRound explicitly before writing; store as NUMERIC, not FLOAT

The fix is always to be explicit at the read, because a wrong type detected at the write is already a corrupted DataFrame. This is the same discipline that Check Excel Data Types with pandas applies to spreadsheets that never touch a database.

Reconcile before anyone else does

The failure that damages a reporting pipeline most is not a crash — it is a workbook that arrives on time, opens cleanly, and disagrees with the system it came from. By the time somebody notices, the number has usually been quoted in a meeting.

Two cheap checks catch nearly all of it, and both belong in the job rather than in a spreadsheet somebody maintains on the side. The first is a count-and-sum reconciliation against the source, run immediately after the extract:

Python
CONTROL = text("""
    SELECT COUNT(*) AS rows, COALESCE(SUM(amount), 0) AS total
    FROM orders WHERE order_date >= :start AND order_date < :end
""")

with engine.connect() as conn:
    control = conn.execute(CONTROL, params).mappings().one()

if len(df) != control["rows"]:
    raise ValueError(f"extract has {len(df):,} rows, source has {control['rows']:,}")
if abs(df["amount"].sum() - float(control["total"])) > 0.01:
    raise ValueError(f"extract totals {df['amount'].sum():,.2f}, "
                     f"source totals {float(control['total']):,.2f}")

Running the control query in the same connection and the same transaction as the extract is what makes it meaningful — against a busy table, a second connection can legitimately see different rows, and a check that fails at random gets switched off within a fortnight.

The second is a comparison against the previous run. A month that is 3% up on the last one is unremarkable; one that is 60% down usually means a filter changed, a join lost rows, or the source loaded late. Keeping the control totals from each run in a small table or JSON file makes that check a subtraction:

Python
previous = read_state().get("total")
if previous and abs(df["amount"].sum() - previous) / previous > 0.4:
    log.warning("total moved %.0f%% against the previous run — check the source",
                (df["amount"].sum() - previous) / previous * 100)

Whether a large movement should stop the job or merely warn depends on the report. For a month-end pack that goes to the board, stopping and asking a human is right. For a daily operational extract where genuine swings happen, a warning in the log and a note on the summary sheet is enough. What is never right is having no opinion at all, because then the first person to see an implausible number is the person least equipped to explain it.

Pull in what has no database at all

Not every source is a table. Rates, holidays, product metadata and half the reference data a report needs live behind an HTTP API, and the pattern is the same: fetch, normalise into a DataFrame, then either write it to Excel or store it alongside the query results.

Python
import requests

resp = requests.get("https://api.example.com/v1/fx", timeout=30,
                    headers={"Authorization": f"Bearer {os.environ['FX_TOKEN']}"})
resp.raise_for_status()
rates = pd.json_normalize(resp.json()["rates"])

raise_for_status() and an explicit timeout are the two lines people leave out and then debug at 03:00: without the timeout a hung API hangs the report forever, and without the status check an HTML error page is parsed as data. Fetch API Data into Excel with Python requests covers pagination, retries and flattening nested JSON into columns.

Refresh on a schedule, not on request

Once the export works, the last step is making it happen without anyone asking. That is scheduling work rather than database work, with one addition specific to this topic: a refreshed extract should be written atomically, so a reader who opens the file mid-refresh never sees a half-written workbook.

Python
tmp = target.with_suffix(".tmp.xlsx")
export(df, tmp)
os.replace(tmp, target)        # atomic on the same filesystem

Keep the schedule itself boring. A refresh that runs a few minutes after the source system's own load window finishes will be right almost every morning and wrong on the one day the load is late — so where the source publishes a completion marker, wait for it rather than for a clock. Where it does not, check the freshness of the newest row before publishing, and skip the run rather than republish yesterday's numbers under today's timestamp.

Add a small "generated at" cell on the summary sheet while you are there. A report that does not say when it was produced gets treated as current forever, which is how a Monday morning decision ends up being made on Thursday's numbers. Refresh an Excel Report from a Database on a Schedule covers incremental refreshes and the freshness stamp.

Key takeaways

  • One engine, one URL, from the environment. SQLAlchemy gives every database the same interface; the password belongs in an environment variable and pool_pre_ping=True keeps an overnight job's connection usable.
  • Bind parameters, never f-strings. Values that came from a spreadsheet must not be able to change the meaning of a statement.
  • Stage the inbound direction. Load to a staging table, validate there, then merge inside engine.begin() so a bad file cannot reach readers.
  • Make loads idempotent. Delete the period and re-insert, so a retry produces the same table rather than duplicated rows.
  • Be explicit about types at the read. Identifiers as str, nullable integers as Int64, dates coerced with a NaT check, money rounded before it is written.
  • Write extracts atomically and stamp them. A temporary file plus os.replace, and a visible "generated at" so nobody mistakes a stale workbook for a current one.

Frequently asked questions

Do I need SQLAlchemy, or can pandas talk to the database directly? pandas accepts a raw DBAPI connection for reading, but to_sql officially supports SQLAlchemy connectables and SQLite connections only. Since SQLAlchemy also gives you one URL format for every database and safe parameter binding, use it for anything beyond a throwaway script.

How do I stop a spreadsheet load from corrupting a live table? Load into a staging table first, validate it there, then move the rows across in one transaction. Writing straight into the reporting table means a bad file is visible to readers before anyone notices.

Why do my IDs come back as 1.0 instead of 1? A column with any blank cell becomes float64 in pandas, because NaN cannot live in an int column. Read the column with dtype=str, or use pandas' nullable Int64 type.

Is it safe to build the SQL with an f-string? No. Use bound parameters — SQLAlchemy's text() with :name placeholders — so a value taken from a spreadsheet cell cannot change the meaning of the statement.

How large can an export be before Excel is the wrong answer? Excel's hard limit is 1,048,576 rows per sheet, but the practical limit is far lower: a workbook past a few hundred thousand rows is slow to open and slower to filter. Beyond that, export CSV or Parquet and keep Excel for the summary.