Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Query Excel Files with SQL Using DuckDB

Run SQL directly against .xlsx files with DuckDB's excel extension — read a range below a report header, stop at the first empty row, handle text in numeric columns, combine several workbooks, join Excel with pandas DataFrames and write query results back to a workbook.
Databases, APIs & sourcesLarge files & performance

Some questions about Excel data are easiest to ask in SQL: totals by region joined to a targets table, the top ten products per month, rows in this month's export that were not in last month's. With DuckDB you can ask them directly against the .xlsx files, without loading each one into pandas first and without a database server. This guide, part of Moving Data Between Excel and Databases, uses DuckDB's read_xlsx on report-style workbooks — a title above the table, a stray "n/a" in a number column — and covers the options that make those files behave.

From Excel files to SQL results and back Load the excel extension, read each workbook's table range with read_xlsx, combine the files with UNION ALL BY NAME, join them with pandas DataFrames or other files in SQL, and write the result back to Excel with COPY or to pandas with df(). LOAD excel once per connection read_xlsx range, stop_at_empty Combine files UNION ALL BY NAME SQL + joins pandas, CSV, Parquet Results out COPY TO xlsx, df()

Prerequisites

Bash
pip install duckdb pandas openpyxl

The excel extension is downloaded the first time you install it and cached afterwards; on a machine without internet access, install it once on a connected machine and copy the extension directory. Two monthly exports with a title line above the table:

Python
import datetime as dt

import duckdb
import pandas as pd
from openpyxl import Workbook

data = {
    "2026-01": [["North", "Tea", 120, dt.date(2026, 1, 5)], ["South", "Tea", 80, dt.date(2026, 1, 9)]],
    "2026-02": [["North", "Coffee", 95, dt.date(2026, 2, 2)], ["East", "Tea", "n/a", dt.date(2026, 2, 3)]],
}
for month, rows in data.items():
    wb = Workbook()
    ws = wb.active
    ws.title = "Sales"
    ws.append([f"Sales report {month}"])
    ws.append([])
    ws.append(["region", "product", "revenue", "date"])
    for row in rows:
        ws.append(row)
    wb.save(f"sales_{month}.xlsx")

con = duckdb.connect()
con.sql("INSTALL excel")
con.sql("LOAD excel")

Step 1 — Read the table, not the title

By default read_xlsx takes the first row of the sheet as the header and stops at the first empty row. For a report with a title in A1 and a blank row under it, that returns an empty table with a column called "Sales report 2026-01". Point it at the table instead:

Python
jan = con.sql("""
    SELECT * FROM read_xlsx('sales_2026-01.xlsx', sheet = 'Sales',
                            range = 'A3:D100000', stop_at_empty = true)
""")
print(jan.df())
print(con.sql("DESCRIBE SELECT * FROM jan").df()[["column_name", "column_type"]].values.tolist())
# [['region', 'VARCHAR'], ['product', 'VARCHAR'], ['revenue', 'DOUBLE'], ['date', 'DATE']]

The range starts at the header row. Give it a generous end row and add stop_at_empty = true: without that flag, DuckDB returns every row of the range, blank ones included — ask for A3:D and you get over a million empty rows. Column types are inferred from the cells, so the dates arrive as real DATE values and the revenue as DOUBLE. Saving a relation in a Python variable, as jan here, lets later SQL refer to it by name. Relations are lazy — the file is read again each time a query uses one — so for a workbook you query many times, materialise it once with CREATE TABLE.

Step 2 — Handle text in numeric columns

The February file has "n/a" typed into the revenue column. Type inference picks DOUBLE for the column and then fails on that cell:

Python
try:
    con.sql("SELECT * FROM read_xlsx('sales_2026-02.xlsx', range = 'A3:D100000', stop_at_empty = true)").df()
except duckdb.Error as exc:
    print(str(exc)[:99])
# Invalid Input Error: read_xlsx: Failed to parse cell 'C5': Could not convert string 'n/a' to DOUBLE

Two options change that. ignore_errors = true keeps the inferred types and turns cells that do not fit into NULL. all_varchar = true reads every cell as text, which never fails — but dates then arrive as Excel serial numbers such as 46055, not as dates:

Python
feb = con.sql("""SELECT * FROM read_xlsx('sales_2026-02.xlsx', range = 'A3:D100000',
                 stop_at_empty = true, ignore_errors = true)""")
print(feb.df()["revenue"].tolist())                    # [95.0, nan]
raw = con.sql("""SELECT revenue, date FROM read_xlsx('sales_2026-02.xlsx', range = 'A3:D100000',
                 stop_at_empty = true, all_varchar = true)""").df()
print(raw.values.tolist())                             # [['95', '46055'], ['n/a', '46056']]

ignore_errors is the practical default for reporting; count the NULLs it produces so the "n/a" does not disappear unnoticed. Use all_varchar when you want to inspect exactly what was typed, and convert columns yourself. Placeholder text in general is covered in Replace Placeholder Text with NaN in pandas.

read_xlsx options for report-style workbooks range skips title rows and selects the table. stop_at_empty ends the read at the first blank row. ignore_errors turns cells that do not match the inferred type into NULL. all_varchar reads every cell as text, including dates as serial numbers. sheet chooses the worksheet by name. What it solves range = 'A3:D100000' title rows above the table stop_at_empty = true empty rows to the end of the range ignore_errors = true 'n/a' in a number column → NULL all_varchar = true everything as text; dates as serials sheet = 'Sales' not the first worksheet

Step 3 — Combine several workbooks

read_xlsx reads one file per call; it rejects a list of paths, and in our test a wildcard pattern such as 'sales_*.xlsx' returned the rows of a single file rather than all of them. Build one query that unions a read per file, adding the file name as a column:

Python
from pathlib import Path

files = sorted(Path(".").glob("sales_*.xlsx"))
parts = [f"""SELECT *, '{p.name}' AS source FROM read_xlsx('{p.as_posix()}', sheet = 'Sales',
             range = 'A3:D100000', stop_at_empty = true, ignore_errors = true)""" for p in files]
sales = con.sql("\nUNION ALL BY NAME\n".join(parts))
print(sales.df()[["region", "revenue", "source"]].values.tolist())

UNION ALL BY NAME matches columns by name rather than position, so files whose columns are in a different order — or where one month has an extra column — still line up, with NULL for columns a file lacks. File names come from your own glob, so building the SQL string is safe here; never paste untrusted text into SQL.

Step 4 — Query and join with plain SQL

With the combined relation named sales, the questions are ordinary SQL — the same SQL you would run against a database table, so analysts who know SQL but not pandas can read and review the logic. DuckDB can also read pandas DataFrames in the same Python scope by their variable name, which is handy for a small targets table kept in code or another sheet:

Python
targets = pd.DataFrame({"region": ["North", "South", "East"], "goal": [200, 100, 50]})
summary = con.sql("""
    SELECT s.region,
           sum(s.revenue)          AS revenue,
           any_value(t.goal)       AS goal,
           count(*) FILTER (WHERE s.revenue IS NULL) AS missing_values
    FROM sales AS s
    LEFT JOIN targets AS t USING (region)
    GROUP BY s.region
    ORDER BY s.region
""").df()
print(summary.values.tolist())

The FILTER clause counts the cells ignore_errors turned into NULL, so East's missing revenue is visible in the result instead of silently lowering its total. The same query could join a CSV with read_csv or a Parquet file with read_parquet, which is where DuckDB shines compared with loading everything into pandas: each source is read directly, and only the result becomes a DataFrame.

Step 5 — Write the result back to Excel

The excel extension can also write. COPY … TO with FORMAT xlsx creates a workbook from any query:

Python
con.sql("""
    COPY (SELECT region, revenue, goal FROM summary ORDER BY region)
    TO 'summary.xlsx' (FORMAT xlsx, HEADER true, SHEET 'Summary')
""")
print(pd.read_excel("summary.xlsx", sheet_name="Summary").values.tolist())

The output is a plain data sheet — no formatting, widths or number formats. For a finished report, fetch the result with .df() and write it with pandas and openpyxl or XlsxWriter, as in Export SQL Query Results to Excel with Python.

DuckDB SQL or pandas for Excel data DuckDB suits set-based questions across several files and formats, keeps intermediate data out of Python memory and expresses joins and filters in SQL. pandas suits cell-level cleaning, reshaping and row-by-row logic, and gives finer control over how Excel files are read and written. DuckDB read_xlsx SQL joins across files and formats only the result enters Python fewer options for messy sheets one file per read_xlsx call pandas read_excel fine control over parsing reshaping and cleaning everything loaded into memory direct link to openpyxl styling

Common pitfalls

  • Forgetting stop_at_empty with a range. Without it, the empty rows of the range come back as NULL rows and inflate count(*).
  • Reading dates with all_varchar. They become serial-number strings. Keep inferred types for date columns, or convert with DATE '1899-12-30' + CAST(col AS INTEGER).
  • Guessing the sheet. Without sheet, DuckDB reads the first worksheet, which in many exports is a cover page or a pivot. Name the sheet explicitly so a reordered workbook fails loudly instead of returning the wrong table.
  • Legacy .xls files. read_xlsx reads only the newer .xlsx format; convert .xls first, as in Convert XLS to XLSX with Python.

Conclusion

DuckDB turns Excel files into SQL tables. Load the excel extension, read each workbook with read_xlsx using range and stop_at_empty to skip report headers, choose ignore_errors or all_varchar for text in numeric columns, and combine files with UNION ALL BY NAME. Join with pandas DataFrames or other file formats in plain SQL, count the values the reader turned into NULL, and write results back with COPY … (FORMAT xlsx) or through pandas for formatted reports.

Frequently asked questions

Can DuckDB read Excel files? Yes. The excel extension provides read_xlsx, which reads a sheet or a range of an .xlsx file as a table you can query with SQL. Install it once with INSTALL excel and load it with LOAD excel.

How do I skip the title rows above the table? Pass range with the table's top-left corner, such as range='A3:D100000', together with stop_at_empty=true so reading stops at the first blank row instead of returning empty rows up to the end of the range.

Why does read_xlsx fail with "Could not convert string to DOUBLE"? It infers each column's type from the data and a later cell does not fit — typically text such as "n/a" in a numeric column. Pass ignore_errors=true to turn such cells into NULL, or all_varchar=true to read everything as text.

Can read_xlsx read several files at once? It takes one file per call. Build a query that combines one read_xlsx per file with UNION ALL BY NAME, which also lines up columns by name when the files differ in order.

More on databases, apis & sources

More on databases, apis & sources →