Generate SQL INSERT Statements from an Excel Sheet

Sometimes the right way to get spreadsheet data into a database is not to connect to it at all. Production databases are often locked down: changes go through a change request, a database administrator reviews a script, and the script is run by someone with the access. What the business user has is an Excel sheet — a list of new product codes, a price change, a set of reference values — and what the DBA wants is a .sql file they can read, test and run. Writing that file by hand, or with a spreadsheet formula that concatenates strings, breaks on the first product called O'Brien's Ale or the first blank cell. This page generates the script from the sheet with pandas, with each value turned into a correct SQL literal: text quoted with embedded quotes escaped, blanks as NULL, dates as ISO literals, numbers unquoted. Rows are batched into multi-row INSERT statements inside a single transaction, an upsert variant is produced for PostgreSQL, and the script is proved by running it against a scratch SQLite database and comparing every value with the sheet. It belongs to Moving Data Between Excel and Databases.

Prerequisites

pip install pandas openpyxl

The sheet holds five new products. It has been chosen to contain the values that break naive scripts: a name with apostrophes, a name with a semicolon, a name containing -- (the start of an SQL comment), a missing launch date, a missing price and a true/false column.

Step 1 — One function for every SQL literal

import math
from datetime import date, datetime
import pandas as pd

def sql_literal(value):
    """Return the SQL text for one value: quoted strings, NULL, ISO dates, numbers, booleans."""
    if value is None or (isinstance(value, float) and math.isnan(value)) or value is pd.NaT:
        return "NULL"
    if isinstance(value, bool):
        return "TRUE" if value else "FALSE"
    if isinstance(value, (int, float)):
        return repr(value) if isinstance(value, int) else format(value, ".15g")
    if isinstance(value, (pd.Timestamp, datetime)):
        return f"'{value:%Y-%m-%d %H:%M:%S}'" if (value.hour or value.minute or value.second) else f"'{value:%Y-%m-%d}'"
    if isinstance(value, date):
        return f"'{value:%Y-%m-%d}'"
    text = str(value).replace("'", "''")                  # the only escape standard SQL needs
    return f"'{text}'"

for v in ["O'Brien's Ale", "Gin -- small batch", None, float("nan"), 3.45, True, pd.Timestamp("2026-10-01")]:
    print(f"{v!r:28} -> {sql_literal(v)}")

Every value in the sheet passes through one function that knows how to write it as SQL. Blanks — None, NaN or pandas' missing timestamp NaT — become NULL, never an empty string or the text nan. Booleans become TRUE and FALSE. Numbers are written without quotes and with enough digits to round-trip exactly. Dates become ISO literals such as '2026-10-01', which every major database reads unambiguously, with the time added only when there is one. Text is wrapped in single quotes with every embedded single quote doubled: O'Brien's becomes 'O''Brien''s'. That doubling is the whole of the escaping that standard SQL requires, and once a value is properly quoted, characters such as ; and -- inside it are just characters — they cannot end the statement or start a comment.

Step 2 — Batched INSERTs in one transaction

df = pd.read_excel("new_products.xlsx")
TABLE = "products"
COLUMNS = list(df.columns)

def insert_script(df, table, batch=500):
    head = f"INSERT INTO {table} ({', '.join(COLUMNS)}) VALUES"
    lines = ["-- generated from new_products.xlsx", f"-- {len(df)} rows", "BEGIN;"]
    rows = [f"  ({', '.join(sql_literal(v) for v in rec)})" for rec in df.astype(object).itertuples(index=False)]
    for i in range(0, len(rows), batch):
        lines.append(head)
        lines.append(",\n".join(rows[i:i + batch]) + ";")
    lines.append("COMMIT;")
    return "\n".join(lines) + "\n"

script = insert_script(df, TABLE)
from pathlib import Path
Path("insert_products.sql").write_text(script, encoding="utf-8")
print(script)

The script starts with comments saying where it came from and how many rows it holds, wraps everything in BEGIN and COMMIT so it either loads completely or not at all, and writes rows as multi-row INSERT statements — one statement per batch of up to 500 rows rather than one per row, which is much faster to run and easier to read. For the five products here the whole script is a dozen lines a reviewer can check by eye. Converting the DataFrame to object before iterating makes each value a plain Python object, so integers, floats and timestamps reach sql_literal as themselves. The column list is written explicitly in every statement, so the script still does the right thing if the table's columns are in a different order.

Step 3 — Prove it by running it

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as con:
    con.execute("""CREATE TABLE products (product_code TEXT PRIMARY KEY, name TEXT NOT NULL, launch_date TEXT,
                                          price REAL, abv REAL, is_seasonal INTEGER)""")
    con.executescript(script)
    back = pd.read_sql("SELECT * FROM products ORDER BY product_code", con)

expected = df.sort_values("product_code").reset_index(drop=True)
expected["launch_date"] = expected["launch_date"].dt.strftime("%Y-%m-%d")
expected["is_seasonal"] = expected["is_seasonal"].astype(int)
pd.testing.assert_frame_equal(back, expected, check_dtype=False)
print(back.to_string(index=False))
print("round trip OK:", len(back), "rows")

A generated script should be tested before it goes to anyone, and the test is to run it. A scratch SQLite database in memory, with a table shaped like the target, executes the script exactly as written, and reading the table back gives values to compare with the sheet. assert_frame_equal checks every cell: the apostrophes in O'Brien's Ale survived, the text with -- is intact, the missing date and price are NULL, and the numbers match. SQLite has no boolean or date type, so they come back as integers and text, which the comparison allows for; on PostgreSQL the same script loads them into proper BOOLEAN and DATE columns.

Column names need care too

Values are not the only thing that can break a script. Spreadsheet headings often become column names, and headings such as Order, Date, Group or Unit price are either reserved words in SQL or contain spaces. Standard SQL quotes identifiers with double quotes — "Order", "Unit price" — and PostgreSQL, SQLite and Oracle follow it, while SQL Server traditionally uses square brackets and MySQL backticks. The safest approach is to map the sheet's headings to the table's real column names explicitly, in a dictionary reviewed once, and to quote identifiers only when the target database requires it. Quoted identifiers in PostgreSQL are also case-sensitive, which is a common source of column does not exist errors when a script quotes a name the table defines in lower case.

Step 4 — An upsert variant for PostgreSQL

def upsert_script(df, table, key):
    body = insert_script(df, table).rstrip().removesuffix("COMMIT;").rstrip().rstrip(";")
    updates = ", ".join(f"{c} = EXCLUDED.{c}" for c in COLUMNS if c != key)
    return f"{body}\nON CONFLICT ({key}) DO UPDATE SET {updates};\nCOMMIT;\n"

print(upsert_script(df, TABLE, "product_code").splitlines()[-2])

When the sheet may contain rows that already exist — a price list resent with changes — a plain INSERT fails on the first duplicate key. PostgreSQL's ON CONFLICT … DO UPDATE turns the statement into an upsert: new rows are inserted and existing ones updated from the incoming values, referred to as EXCLUDED. SQLite accepts the same syntax; SQL Server uses MERGE instead, which needs a different template. This variant assumes a single batch — for several batches, each statement gets its own ON CONFLICT clause. The script is still a file for review, not something run blindly: the comment lines at the top, the row count and a successful run against a scratch database are what make it reviewable.

Common pitfalls

• Building values with string concatenation in Excel. Quotes, blanks and dates break; generate the literals in code.
• Not doubling single quotes. O'Brien ends the string early; replace ' with ''.
• Writing nan or empty strings for blanks. Use NULL.
• One statement per row with no transaction. Batch rows and wrap the script in BEGIN/COMMIT.
• Running generated SQL from your own code. For a live connection, use parameterised queries instead; scripts are for handing over.

Conclusion

To turn an Excel sheet into a SQL script, convert every value with one function — NULL for blanks, unquoted numbers, TRUE/FALSE, ISO date literals and single-quoted text with embedded quotes doubled — write the rows as batched multi-row INSERT statements inside a transaction, and add an ON CONFLICT upsert where rows may already exist. Before handing the file over, run it against a scratch database and compare every value with the sheet.

Frequently asked questions

How do I generate INSERT statements from Excel with Python? Read the sheet with pandas, turn each value into an SQL literal, and write INSERT INTO table (columns) VALUES (…), (…); statements to a .sql file.

How do I escape apostrophes in SQL strings? Double them inside single quotes: O'Brien becomes 'O''Brien'.

How should blank Excel cells appear in SQL? As NULL, not as an empty string or the text "nan".

Should I use generated SQL strings to load data from Python? No; when connecting directly, use parameterised queries. Generated scripts are for review and execution by someone else.

Related

• Up one level: Moving Data Between Excel and Databases — Excel and databases.
• Load an Excel File into a SQL Database with pandas — loading directly.
• Load Excel into SQLite with a Defined Schema — table design.
• Prevent Formula Injection in Excel Exports — escaping in the other direction.