Importing an Excel Sheet Into PostgreSQL With Python

The shortest path from a sheet to a PostgreSQL table is four lines of pandas, and it works for one clean file on one afternoon. This post takes the slightly longer path that keeps working: it reads the sheet without letting pandas guess, copies the rows into a staging table, and casts them in SQL. Each step exists to prevent a specific failure I will name as it comes up. It belongs to the cluster on getting Excel data into a database, which explains the stage, check and convert method this article applies to PostgreSQL.

Read the sheet as text

Install the pieces once:

pip install pandas openpyxl "psycopg[binary]" sqlalchemy

Then read the sheet and keep every cell as a string:

import pandas as pd

df = pd.read_excel("orders.xlsx", sheet_name="Orders", dtype=str, keep_default_na=False)
print(df)

For a sheet with an id that has a leading zero, an amount and a serial date, the result is:

  Order No Customer Amount (EUR)   Date
0      007     Acme         19.99  45292
1      012  Globex          5.00  45293

Two options matter. dtype=str stops pandas from turning 007 into 7 and the amount column into floats. keep_default_na=False keeps strings such as NA and N/A as strings instead of converting them into missing values; empty cells become empty strings. If a country code column contains NA for Namibia, the default would silently erase it. The date shows as 45292 because that is the stored number, and the dates cluster explains how to turn it into a real date (importing Excel dates correctly into a date column).

Check what you got before you go further: the number of rows, the column names and a few values. If the sheet has title rows above the header, tell pandas where the header is (handling Excel files with title rows above the header).

Create the staging table

The stage mirrors the sheet as text and records where each row came from:

CREATE TABLE IF NOT EXISTS stage_orders (
    batch_id     bigint  NOT NULL,
    source_file  text    NOT NULL,
    source_sheet text    NOT NULL,
    source_row   integer NOT NULL,
    order_no     text,
    customer     text,
    amount_eur   text,
    ordered      text
);

Do not add constraints beyond NOT NULL on the provenance columns. The stage must accept whatever the sheet contains, bad values included, or the failure you want to analyze happens before you can look at it. Keep the final table, with its types and constraints, separate.

Copy the rows in

psycopg 3 exposes the PostgreSQL COPY protocol, which streams rows to the server in one command and is much faster than issuing one INSERT per row:

import psycopg

BATCH_ID = 42
FILE, SHEET = "orders.xlsx", "Orders"

rows = [
    (BATCH_ID, FILE, SHEET, i + 2, r["Order No"], r["Customer"], r["Amount (EUR)"], r["Date"])
    for i, r in df.iterrows()
]

with psycopg.connect("dbname=app user=loader") as conn:
    with conn.cursor() as cur:
        with cur.copy(
            "COPY stage_orders (batch_id, source_file, source_sheet, source_row, "
            "order_no, customer, amount_eur, ordered) FROM STDIN"
        ) as copy:
            for row in rows:
                copy.write_row(row)
    print("staged", len(rows), "rows")

staged 2 rows

The row number is i + 2: the dataframe index starts at zero, the header occupies sheet row 1, and the first data row is sheet row 2. If you skipped rows with skiprows, add the number you skipped. The with psycopg.connect(...) block commits when it exits normally and rolls back on an exception, so a failed copy leaves the stage empty rather than half-filled.

Make sure the batch id is unique per load. A simple source is a load_batches table with an identity column, inserted before the copy:

CREATE TABLE IF NOT EXISTS load_batches (
    batch_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source_file text NOT NULL,
    file_sha256 text NOT NULL,
    loaded_at  timestamptz NOT NULL DEFAULT now()
);

Computing the SHA-256 of the file and refusing to load a hash that already exists is the cheapest guard against loading the same file twice.

When to use to_sql instead

pandas can write a dataframe to a table in one call through SQLAlchemy. It is acceptable for small files and for the stage:

from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg://loader@localhost/app")
df.to_sql("stage_orders_raw", engine, if_exists="append", index=False, method="multi", chunksize=2000)

The URL scheme matters. With SQLAlchemy 2, postgresql:// selects the older psycopg2 driver, which you may not have installed, and the error says the module is missing. For psycopg 3 write postgresql+psycopg://. The method="multi" option sends many rows in one INSERT statement, which is faster than the default one row at a time, though still slower than COPY for large loads.

Know what to_sql does with types. It inspects each column's pandas dtype: strings become TEXT, integers BIGINT, floats DOUBLE PRECISION, datetimes TIMESTAMP. If you did not read with dtype=str, the amounts column becomes a double, and money in a double is the wrong type for the final table. Either load into the pre-created text stage as above, or give to_sql an explicit mapping with dtype={"amount_eur": sqlalchemy.types.Numeric(12, 2)}. With if_exists="replace", pandas drops and recreates the table, discarding your constraints and indexes, so use append into tables you created.

Convert in SQL

With the data staged and checked (validating row counts after an Excel import), convert into the typed table:

INSERT INTO orders (order_no, customer, amount_eur, ordered_on)
SELECT order_no,
       customer,
       amount_eur::numeric(12,2),
       DATE '1899-12-30' + ordered::integer
FROM stage_orders
WHERE batch_id = 42
ON CONFLICT (order_no) DO UPDATE
SET customer = EXCLUDED.customer,
    amount_eur = EXCLUDED.amount_eur,
    ordered_on = EXCLUDED.ordered_on;

For the two sample rows this inserts order 007 for Acme on 2024-01-01 with 19.99 and order 012 for Globex on 2024-01-02 with 5.00. The id keeps its zeros because the target column is text. The date arithmetic works for serial numbers after February 1900 (converting Excel serial dates to real dates in SQL). A malformed amount raises invalid input syntax for type numeric, which stops the statement and rolls it back. If you prefer to load the good rows and report the bad, flag rows in a view with a regular-expression test first and insert only those that pass, sending the rest to a rejects table with the reason.

Run the staging copy and the conversion in a single transaction if the stage is temporary, or keep them separate if you want to keep the staged text for audit. The second is my default, because the first time a total looks wrong you will want to see the original text.

Credentials and connection details

Do not put passwords in the script. Use a .pgpass file, environment variables (PGPASSWORD, PGHOST) or a secret manager, and let psycopg read them. Give the loader a database role that can insert into the staging and target tables and nothing else. If the workbook came from outside your organization, treat it as untrusted input: the parameters of the COPY go through the driver safely, but a script that builds SQL by pasting cell values into strings is an injection risk. Pass values as parameters or through COPY, never through f-strings.

Large files

pandas reads the whole sheet into memory. For a file with a few hundred thousand rows that is fine; beyond that, streaming helps. openpyxl's read-only mode iterates rows without building the full sheet (reading large Excel files in read-only mode), and you can write each chunk to the COPY as it arrives. Another route is to convert the sheet to CSV once and use COPY ... FROM STDIN WITH (FORMAT csv, HEADER true) on the file, which streams from disk. Whichever you choose, time it on your own data: reading dominates, and the database is rarely the bottleneck.

When this Python import is more than the job needs

The file is small and one-off. For a 50-row lookup, DuckDB or a spreadsheet's "save as CSV" plus \copy in psql is faster to do than writing a script (loading Excel files with DuckDB).

The source is already clean. If the sender gives you one sheet of text-formatted ids and ISO dates, the staging step adds little. Keep the checks and drop the ceremony.

Another team owns the pipeline. If your company has an ETL tool that already does this, extend it rather than writing a parallel script.

The data is huge. Past a million rows, ask for CSV or a database dump at the source. Excel is the wrong container.

The pandas reader is documented in the read_excel reference, and the copy API in the psycopg 3 documentation on COPY. To look at a small extract as JSON, use the CSV to JSON converter.