W3cubDocs

/DuckDB

Excel Import

Installing the Extension

To read data from an Excel file, install and load the spatial extension. This is only needed once per DuckDB connection.

INSTALL spatial;
LOAD spatial;

Importing Excel Sheets

Use the st_read function in the FROM clause of a query:

SELECT * FROM st_read('test_excel.xlsx');

The layer parameter allows specifying the name of the Excel worksheet:

SELECT * FROM st_read('test_excel.xlsx', layer = 'Sheet1');

Creating a New Table

To create a new table using the result from a query, use CREATE TABLE ... AS from a SELECT statement:

CREATE TABLE new_tbl AS
    SELECT * FROM st_read('test_excel.xlsx', layer = 'Sheet1');

Loading to an Existing Table

To load data into an existing table from a query, use INSERT INTO from a SELECT statement:

INSERT INTO tbl
    SELECT * FROM st_read('test_excel.xlsx', layer = 'Sheet1');

Several configuration options are also available for the underlying GDAL library that is doing the XLSX parsing. You can pass them via the open_options parameter of the st_read function as a list of 'KEY=VALUE' strings.

Importing a Sheet with/without a Header

The option HEADERS has three possible values:

FORCE: treat the first row as a header
DISABLE treat the first row as a row of data
AUTO attempt auto-detection (default)

For example, to treat the first row as a header, run:

SELECT *
FROM st_read(
    'test_excel.xlsx',
    layer = 'Sheet1',
    open_options = ['HEADERS=FORCE']
);

Detecting Types

The option FIELD_TYPE defines how field types should be treated:

STRING: all fields should be loaded as strings (VARCHAR type)
AUTO: field types should be auto-detected (default)

For example, to treat the first row as a header and use auto-detection for types, run:

SELECT *
FROM st_read(
    'test_excel.xlsx',
    layer = 'Sheet1',
    open_options = ['HEADERS=FORCE', 'FIELD_TYPES=AUTO']
);

To treat the fields as strings:

SELECT *
FROM st_read(
    'test_excel.xlsx',
    layer = 'Sheet1',
    open_options = ['FIELD_TYPES=STRING']
);