Loading Excel Files With DuckDB and the Excel Extension

If you think in SQL, the most comfortable way to look at an Excel sheet is to query it. DuckDB can do that from the command line with a couple of statements, no server and no Python, and then write the result wherever it needs to go. This post shows how to read a sheet, keep it as text, shape it with SQL and send it on to a file or to PostgreSQL. It belongs to the cluster on getting Excel data into a database, and it is the lightest way to apply the stage-check-convert method described there.

Install the extension

DuckDB ships its Excel support as an extension. In the DuckDB shell:

INSTALL excel;
LOAD excel;

INSTALL downloads the extension once and LOAD activates it for the session. Recent versions can load a known core extension on first use without being asked, but writing both lines makes a script explicit and reproducible. The extension reads .xlsx files. It does not read the older .xls binary format or .xlsb, so convert those first (XLS versus XLSX versus XLSB).

Start the shell on a file database if you want the tables to persist (duckdb orders.duckdb), or with no argument for an in-memory session that disappears when you quit.

Read a sheet

SELECT * FROM read_xlsx('orders.xlsx', sheet = 'Orders') LIMIT 5;

By default the function reads the first sheet, uses the first row as the header, and infers a type for each column from the data. Passing sheet picks another one. The output is an ordinary result set, so you can join it, filter it, group it and insert it. A result might look like this, for a small sheet:

┌──────────┬──────────┬────────────┬────────────┐
│ Order No │ Customer │ Amount     │ Date       │
│ varchar  │ varchar  │ double     │ date       │
├──────────┼──────────┼────────────┼────────────┤
│ 007      │ Acme     │      19.99 │ 2024-01-01 │
│ 012      │ Globex   │        5.0 │ 2024-01-02 │
└──────────┴──────────┴────────────┴────────────┘

Two things in that result deserve a look. The Order No column came out as varchar with its zeros intact because the cells in the workbook are text cells. If they had been numeric cells with a padding format, DuckDB would read the number 7. And Amount is a double, with the usual caveat about money in floating point. Inferred types are a convenience for exploration, and an untrustworthy basis for loading.

Keep everything as text

For loading, turn inference off and take the cells as strings:

SELECT * FROM read_xlsx('orders.xlsx', sheet = 'Orders', all_varchar = true);

With all_varchar, every column is VARCHAR. A date cell is returned as the text of its stored number or its formatted value, depending on version, so look at a few rows before you write conversions. As in every route, the point is to decide the types yourself, in SQL, where you can see them and count the failures (importing Excel dates correctly into a date column).

Some other options are useful for messy files:

Check the extension page for the full list on your version, since the option set has grown between releases. range is the one that rescues the title-row problem without another tool: if the header sits on row 5 and the data ends on row 204, range = 'A5:D204' reads exactly that block (handling Excel files with title rows above the header). stop_at_empty is the equivalent for the bottom of a sheet whose length changes, and it assumes there is no blank row inside the data.

Stage into a table

Create a table from the query and add provenance columns in the same statement:

CREATE TABLE stage_orders AS
SELECT 42 AS batch_id,
       'orders.xlsx' AS source_file,
       'Orders' AS source_sheet,
       row_number() OVER () + 1 AS source_row,
       "Order No" AS order_no,
       "Customer" AS customer,
       "Amount" AS amount_eur,
       "Date" AS ordered
FROM read_xlsx('orders.xlsx', sheet = 'Orders', all_varchar = true);

The row_number() OVER () gives each row a position starting at 1, and adding 1 accounts for the header row. Without an ORDER BY, the window function numbers rows in the order the scan produced them. The extension reads sheets in order, so that matches the sheet in practice, but if you need a guarantee, treat the number as a convenience and verify a sample against the workbook. If you used range with a start row other than 1, add the offset. Quote column names that contain spaces or capital letters, as above.

Check the count against the sheet's data rows (validating row counts after an Excel import):

SELECT count(*) FROM stage_orders;

Then convert with ordinary SQL, such as TRY_CAST(amount_eur AS DECIMAL(12,2)), which returns NULL on failure, and a date expression for serial numbers (DATE '1899-12-30' + CAST(ordered AS INTEGER)). Find the failures with a query that selects rows where the source is not empty and the converted value is NULL.

Send it somewhere

DuckDB is a convenient transit point because it writes many formats. To produce Parquet, a typed, compressed file that analytics tools read directly:

COPY (
  SELECT order_no,
         customer,
         TRY_CAST(amount_eur AS DECIMAL(12,2)) AS amount_eur,
         DATE '1899-12-30' + TRY_CAST(ordered AS INTEGER) AS ordered_on
  FROM stage_orders
) TO 'orders.parquet' (FORMAT parquet);

For CSV, change the format to csv and add HEADER. To load into PostgreSQL, attach the database through the postgres extension and insert:

INSTALL postgres;
LOAD postgres;
ATTACH 'dbname=app user=loader host=localhost' AS pg (TYPE postgres);

INSERT INTO pg.public.stage_orders
SELECT * FROM stage_orders;

The attached database appears under the alias pg, and the statement runs a bulk transfer over the PostgreSQL protocol. The target table must exist with compatible columns. Use a connection string that does not contain the password in the command history: PostgreSQL reads PGPASSWORD and .pgpass for the attached connection as it does for other clients. Check the extension's page for the exact attach syntax on your version.

This route is fast and compact: one tool reads the Excel file, shapes the data with SQL and writes to the destination. The price is that the shape of the load is a script in SQL, with the logic of a pipeline (batch ids, reject tables, idempotency) written by hand. For a one-off load or an exploration it is excellent. For a production pipeline, keep it in a script under version control and test it with a sample workbook.

Exploring a workbook

DuckDB is also good for answering questions about a sheet without loading anything permanently:

SELECT "Customer", count(*) AS orders, sum(TRY_CAST("Amount" AS DOUBLE)) AS total
FROM read_xlsx('orders.xlsx', sheet = 'Orders', all_varchar = true)
GROUP BY 1
ORDER BY total DESC
LIMIT 10;

That gives the ten biggest customers by total without a staging table. Because the file is read on every query, repeated exploration on a large workbook re-parses the XML each time, so create a table or a Parquet file once and query that.

You can also check the structure of the file itself. DESCRIBE SELECT * FROM read_xlsx(...) shows the column names and the inferred types, which is a quick way to see where the extension's inference differs from your expectation. If a column you consider text is typed as double, a sheet formatted number cell is the cause.

Dates and types: check on a sample

The extension tries to return date-formatted cells as DATE or TIMESTAMP when it infers types, which is what you want during exploration. With all_varchar the value arrives as text, and the form (serial number or formatted string) depends on how the cell is stored and on the version. I have not verified both on one release for this article, so run SELECT on a workbook with one date column, a text-stored date and a serial-looking number, and read the results before you write conversions. Keep that sample workbook as a test file next to the script. It is the cheapest way to notice when an upgrade changes the output.

When DuckDB is not the right way to read an Excel file

The file is XLS or XLSB. The extension reads XLSX only. Convert first, or use a library that reads the older formats.

You need formulas or styles. The extension returns values. If you need the formula text or a cell's formatting, use openpyxl (reading and writing Excel files with openpyxl).

You need exact control of every cell's type. Staging as text gives that control, but cell-level decisions, such as using the number format to detect dates, are easier in a library.

The load is a production pipeline with a lot of logic. SQL scripts grow awkward when they need retries, logging and notifications. Use Python for orchestration and DuckDB for the heavy lifting.

The extension is documented on the DuckDB Excel extension page, and the copy statements in the DuckDB COPY statement reference. To look at a small extract as JSON, use the CSV to JSON converter.