The excel extension provides functions to format numbers per Excel's formatting rules by wrapping the i18npool library, but as of DuckDB 1.2 also provides functionality to read and write Excel (.xlsx) files. However, .xls files are not supported.

Previously, reading and writing Excel files was handled through the spatial extension, which coincidentally included support for xlsx files through one of its dependencies, but this capability may be removed from the spatial extension in the future. Additionally, the excel extension is more efficient and provides more control over the import/export process. See the Excel Import and Excel Export pages for instructions.

Installing and Loading

The excel extension will be transparently autoloaded on first use from the official extension repository. If you would like to install and load it manually, run:

INSTALL excel;
LOAD excel;

Excel Scalar Functions

Examples

SELECT excel_text(1_234_567.897, 'h:mm AM/PM') AS timestamp;

SELECT excel_text(1_234_567.897, 'h AM/PM') AS timestamp;

Reading XLSX Files

Reading a .xlsx file is as simple as just SELECT:ing from it immediately, e.g.

SELECT * FROM 'test.xlsx';
----
┌────────┬────────┐
│   a    │   b    │
│ double │ double │
├────────┼────────┤
│    1.0 │    2.0 │
│    3.0 │    4.0 │
└────────┴────────┘

However, if you want to set additional options to control the import process, you can use the read_xlsx function instead. The following named parameters are supported.

SELECT * FROM read_xlsx('test.xlsx', header = 'true');
----
┌────────┬────────┐
│   a    │   b    │
│ double │ double │
├────────┼────────┤
│    1.0 │    2.0 │
│    3.0 │    4.0 │
└────────┴────────┘

Alternatively, the COPY statement with the XLSX format option can be used to import an Excel file into an existing table, in which case the types of the columns in the target table will be used to coerce the types of the cells in the Excel file.

CREATE TABLE test(a DOUBLE, b DOUBLE);
COPY test FROM 'test.xlsx' WITH (FORMAT 'xlsx', header 'true');
SELECT * FROM test;

Type and Range Inference

Because Excel itself only really stores numbers or strings in cells, and dont enforce that all cells in a column is of the same type, the excel extension has to do some guesswork to "infer" and decide the types of the columns when importing an Excel sheet. While almost all columns are inferred as either DOUBLE or VARCHAR, there are some caveats:

• TIMESTAMP, TIME, DATE and BOOLEAN types are inferred when possible based on the format applied to the cell.
• Text cells containing TRUE and FALSE are inferred as BOOLEAN.
• Empty cells are considered to be DOUBLE by default, unless the empty_as_varchar option is set to true, in which case they are typed as VARCHAR.

If the all_varchar option is set to true, none of the above applies and all cells are read as VARCHAR.

When no types are specified explicitly, (e.g. when using the read_xlsx function instead of COPY TO ... FROM '<file>.xlsx') the types of the resulting columns are inferred based on the first "data" row in the sheet, that is:

• If no explicit range is given
  • The first row after the header if a header is found or forced by the header option
  • The first non-empty row in the sheet if no header is found or forced
• If an explicit range is given
  • The second row of the range if a header is found in the first row or forced by the header option
  • The first row of the range if no header is found or forced

This can sometimes lead to issues if the first "data row" is not representative of the rest of the sheet (e.g. it contains empty cells) in which case the ignore_errors or empty_as_varchar options can be used to work around this.

However, when the COPY TO ... FROM '<file>.xlsx' syntax is used, no type inference is done and the types of the resulting columns are determined by the types of the columns in the table being copied to. All cells will simply be converted by casting from DOUBLE or VARCHAR to the target column type.

Writing XLSX Files

Writing .xlsx files is supported using the COPY statement with XLSX given as the format. The following additional parameters are supported.

Warning Many tools only support a maximum of 1,048,576 rows in a sheet, so increasing the sheet_row_limit may render the resulting file unreadable by other software.

These are passed as options to the COPY statement after the FORMAT, e.g.

CREATE TABLE test AS SELECT * FROM (VALUES (1, 2), (3, 4)) AS t(a, b);
COPY test TO 'test.xlsx' WITH (format 'xlsx', header 'true');

Type conversions

Because XLSX files only really support storing numbers or strings - the equivalent of VARCHAR and DOUBLE, the following type conversions are applied when writing XLSX files.

• Numeric types are cast to DOUBLE when writing to an XLSX file.
• Temporal types (TIMESTAMP, DATE, TIME, etc.) are converted to excel "serial" numbers, that is the number of days since 1900-01-01 for dates and the fraction of a day for times. These are then styled with a "number format" so that they appear as dates or times when opened in Excel.
• TIMESTAMP_TZ and TIME_TZ are cast to UTC TIMESTAMP and TIME respectively, with the timezone information being lost.
• BOOLEANs are converted to 1 and 0, with a "number format" applied to make them appear as TRUE and FALSE in Excel.
• All other types are cast to VARCHAR and then written as text cells.