Converting Excel to SQL Insert Statements Safely and Fast

A colleague sends a workbook of 300 products and asks for the SQL to put them in the database. You can generate INSERT statements from it in twenty lines of Python, and you can also generate statements that fail on the first apostrophe or, worse, run something you did not intend. This article shows the careful version. If the sheet is going to a PostgreSQL table and is large, importing an Excel sheet into PostgreSQL with Python is the faster route; generated SQL is for seed data, fixtures and scripts someone else will review and run.

Read the sheet with the types you want

Statements are only as good as the values going in. Read identifiers as text, parse dates, and decide what a blank means.

import pandas as pd

df = pd.read_excel(
    "products.xlsx",
    sheet_name="Products",
    dtype={"sku": "string", "name": "string"},
    parse_dates=["launched_on"],
)
df = df.dropna(how="all")

Suppose the first rows are:

The apostrophe in the first name and the comma in the second are the two cases a naive script gets wrong. The blank launch date has to become NULL.

Quote with a function, never with string formatting

The wrong way builds the statement with an f-string and a hope:

# Do not do this
f"INSERT INTO products VALUES ('{sku}', '{name}', {price});"

It breaks on O'Brien Widget and executes whatever a hostile cell contains. The workbook is input from outside your code, and a cell holding '); DROP TABLE products; -- is a cell like any other. Use a quoting function. The simplest portable one doubles single quotes, which is the SQL standard escape:

def lit(v):
    if v is None or (isinstance(v, float) and v != v) or v is pd.NA or v is pd.NaT:
        return "NULL"
    if isinstance(v, bool):
        return "TRUE" if v else "FALSE"
    if isinstance(v, (int, float)):
        return repr(v)
    if hasattr(v, "isoformat"):
        return "'" + v.isoformat()[:10] + "'"
    return "'" + str(v).replace("'", "''") + "'"

That covers PostgreSQL and SQLite. MySQL also treats backslash as an escape character in strings by default, so a value ending in a backslash breaks the literal there; for MySQL, prefer a driver's escaping function or run with NO_BACKSLASH_ESCAPES. PostgreSQL with standard_conforming_strings on (the default since 9.1) treats backslash as an ordinary character, which is why doubling quotes is enough. If a driver is available, let it quote: psycopg's sql.Literal and mogrify do this properly.

from psycopg import sql

sql.Literal("O'Brien Widget").as_string()

The result is 'O''Brien Widget'.

Write multi-row statements in batches

One INSERT per row is the slowest way to load, because each statement is parsed and, in autocommit mode, committed alone. Group the rows.

cols = ["sku", "name", "price", "launched_on"]

def batches(rows, size=500):
    for i in range(0, len(rows), size):
        yield rows[i:i + size]

records = list(df[cols].itertuples(index=False, name=None))
with open("products.sql", "w", encoding="utf-8", newline="\n") as f:
    f.write("BEGIN;\n")
    for chunk in batches(records):
        values = ",\n  ".join("(" + ", ".join(lit(v) for v in r) + ")" for r in chunk)
        f.write(f"INSERT INTO products ({', '.join(cols)}) VALUES\n  {values};\n")
    f.write("COMMIT;\n")

For the two sample rows the file contains:

BEGIN;
INSERT INTO products (sku, name, price, launched_on) VALUES
  ('00417', 'O''Brien Widget', 12.5, '2025-03-14'),
  ('00418', 'Cable, 2 m', 4.99, NULL);
COMMIT;

The transaction wrapper makes the load all or nothing, so a failure at row 400 leaves no half-loaded table. A few hundred rows per statement is a sensible size; SQL Server limits a VALUES list to 1,000 rows and PostgreSQL has no such limit but a very long statement is hard to read and debug. Write the file as UTF-8 with Unix line endings, and state the encoding to whoever runs it.

NULL, empty strings and zero

Excel does not distinguish an empty cell from a cell with an empty string in the way a database does. Pick a rule and apply it everywhere. The usual rule: a blank cell is NULL. A cell holding 0 is 0. A cell holding text such as "n/a" or "TBC" is text, and you should decide whether that belongs in a numeric column at all. Converting it to NULL hides information; converting it to a flag column keeps it. Whatever you choose, count the nulls per column and print the counts, so the person reviewing the script sees what happened.

Create the table too, from a declared schema

A script that only inserts depends on a table that someone created by hand. Ship the DDL beside it, with types chosen on purpose.

CREATE TABLE products (
  sku         text PRIMARY KEY,
  name        text NOT NULL,
  price       numeric(12,2) NOT NULL,
  launched_on date
);

numeric(12,2) stores prices exactly, where real would store 12.5 and 4.99 as binary approximations. A primary key on sku makes a duplicate row in the workbook fail loudly rather than load twice. For the thinking behind choosing types, see designing tables from a messy Excel sheet.

Let the database do the loading

For anything past a few thousand rows, generated SQL is the wrong mechanism. Two better options:

import psycopg

with psycopg.connect("dbname=shop") as conn, conn.cursor() as cur:
    cur.executemany(
        "INSERT INTO products (sku, name, price, launched_on) VALUES (%s, %s, %s, %s)",
        records,
    )

executemany sends values as parameters, so there is no quoting problem to get wrong, and psycopg 3 pipelines the statements. Faster still is COPY, which streams rows into the table:

with psycopg.connect("dbname=shop") as conn, conn.cursor() as cur:
    with cur.copy("COPY products (sku, name, price, launched_on) FROM STDIN") as copy:
        for r in records:
            copy.write_row(r)

Both are in the psycopg documentation, and the PostgreSQL INSERT reference lists the VALUES syntax used above.

Check the load

After running the script, compare counts and one total with the sheet.

SELECT count(*) AS rows, sum(price) AS total_price, count(*) FILTER (WHERE launched_on IS NULL) AS no_date
FROM products;

If the sheet had 300 data rows, a total price of 3,412.70 and 12 blank dates, the query must return exactly those figures (validating row counts after an Excel import).

Make the script safe to rerun

Someone will run the file twice. A second run of plain INSERT statements fails on the primary key, or worse, succeeds on a table without one and doubles every row. Decide which behavior you want and write it into the script. For seed data that should converge on the workbook's content, use an upsert: INSERT ... ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price on PostgreSQL and SQLite, or ON DUPLICATE KEY UPDATE on MySQL. For a one-time load into an empty table, keep the plain insert and add a comment at the top that says the table must be empty, then check for that at the start with a SELECT count(*) guard in the run instructions. A script that states its own preconditions saves the person running it from guessing.

When INSERT statements are the wrong output

Generated statements are the wrong choice for large loads, because COPY or a bulk loader is ten to a hundred times faster. They are wrong when the data is sensitive and the script will sit in a repository or a ticket, since the values are now in plain text in a file that travels. And they are wrong when the load repeats every week: keep the workbook as the source and run a loader that reads it, rather than regenerating and reviewing SQL each time. For a repeatable job see staging Excel data in a table first.