Acquire / file · All operations

Read a spreadsheet

Read Excel, ODS, Google Sheets.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today, with the excel extension.

Shape of the result
a new table, from no input table
Level
needs a DuckDB extension

The DuckDB step

A sheet whose first data cell is empty, read as text then typed

SQL
INSTALL excel; LOAD excel;
COPY (SELECT * FROM (VALUES (NULL, 1.0), ('north', 2.0)) t(region, total)) TO '<your_scratch_dir>/nullfirst.xlsx' (FORMAT xlsx, HEADER true);
CREATE TEMP TABLE result AS
SELECT region, TRY_CAST(total AS DOUBLE) AS total FROM read_xlsx('<your_scratch_dir>/nullfirst.xlsx', all_varchar = true);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 2 AND count(region) = 1 AND max(region) = 'north' AND sum(total) = 3 FROM result)) THEN 'as expected' ELSE error('the sheet did not read as one empty region, one north, totalling 3') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

  • The statement writes nullfirst.xlsx itself, in <your_scratch_dir>.
  • It needs the excel extension.
  • The statement checks its own result and raises an error when it is wrong: “the sheet did not read as one empty region, one north, totalling 3”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Read a sheet of a workbook

Read a sheet or range of an Excel workbook into a table.

Read an OpenDocument spreadsheet

Read a sheet of an ODS workbook into a table.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)
Apache Hop (2)
Apache NiFi (1)
  • ExcelReaderread 2026-09-27

    Read a Microsoft Excel workbook into records, one per row of each sheet

Azure Data Factory (1)
Duckle (1)
  • Excel (XLSX)read 2026-09-25

    read an .xlsx sheet with read_xlsx, then cast to the declared schema

EasyMorph (2)
KNIME Analytics Platform (4)
OpenRefine (3)
Pentaho Data Integration (1)
Polars (2)
  • read_excelread 2026-09-27

    Read an Excel worksheet into a frame

  • read_odsread 2026-09-27

    Read an OpenDocument spreadsheet into a frame

Power Query M (2)
  • Excel.CurrentWorkbookread 2026-09-27

    Return the tables, named ranges and dynamic arrays of the current Excel workbook, without its sheets

  • Excel.Workbookread 2026-09-27

    Read an Excel workbook binary into a table of its contents, sheets included, with optional header promotion

RapidMiner Studio (4)
Talend (1)
VisiData (5)

Background

DuckDB's excel extension reads and writes Excel (.xlsx) files, but does not support .xls files.[1] In pandas, read_excel reads an Excel file into a DataFrame and supports the xls, xlsx, xlsm, xlsb, odf, ods and odt file extensions.[2] In modern spreadsheet applications, several sheets are gathered together to form a workbook, held in one file.[3]

When to use it

In a spreadsheet, each cell may contain numeric or text data, or the result of a formula that calculates a value from other cells.[3] A spreadsheet cell's display format changes only the displayed value, not the cell's contents.[3] Check a few values read from a workbook against the workbook as it is displayed. (Meridian's view)

DuckDB's excel extension has to infer column types, because Excel does not enforce that all cells in a column are of the same type.[1] XLSX files only really support storing numbers or strings, the equivalent of DuckDB's VARCHAR and DOUBLE types.[1] When DuckDB's read_xlsx function is given no types, it infers each column's type from the first data row of the sheet.[1] In DuckDB, a first data row that is not representative of the rest of the sheet, for example one containing empty cells, can sometimes lead to issues.[1] DuckDB types empty cells as DOUBLE by default, unless the empty_as_varchar option is set to true.[1] With DuckDB's all_varchar option set to true, read_xlsx does no type inference and reads every cell as VARCHAR.[1] When a sheet's first rows are sparse or unrepresentative, read every cell as text and cast the columns yourself, so each type comes from a decision rather than from one row. (Meridian's view)

Without an explicit range, DuckDB's read_xlsx reads the rectangular region between the first row of consecutive non-empty cells and the first empty row spanning the same columns.[1] DuckDB's stop_at_empty option, which stops reading at an empty row, is true by default unless an explicit range is given.[1] A sheet laid out for people, with a title above the table or blank rows between blocks, is best read with an explicit range. (Meridian's view)

KNIME's Excel Reader uses the cached values of formula cells unless its option to reevaluate formulas is checked.[4] Reevaluating formulas in KNIME's Excel Reader can cause errors when a function is not implemented by the underlying Apache POI library.[4] KNIME's Excel Reader has options to skip the hidden rows and the hidden columns of a sheet.[4] Decide whether a sheet's hidden rows belong in the table before reading it. (Meridian's view)

The method

Office Open XML (OOXML) is a zipped file format based on Extensible Markup Language (XML), developed by Microsoft for spreadsheets, charts, presentations and word processing documents.[5] Ecma International standardised the initial version of OOXML as ECMA-376, and later versions were standardised as ISO/IEC 29500.[5] A .xlsx workbook is a .zip package, and renaming a minimal workbook's extension from .xlsx to .zip shows the parts inside.[6] In an OOXML spreadsheet, a separate XML file is created for each worksheet.[6]

OpenDocument Format (ODF), standardised as ISO/IEC 26300, is an open file format for documents including spreadsheets, using ZIP-compressed XML files.[7] The most common ODF filename extensions for spreadsheets are .ods and .fods.[7] OpenDocument is developed and maintained by a technical committee in the Organization for the Advancement of Structured Information Standards (OASIS) consortium.[7] OASIS approved OpenDocument as an OASIS standard on 1 May 2005.[7]

A spreadsheet cell can be referred to by its column, normally a letter, and its row, normally a number, as in C10, and a range of cells as in A1:A10.[3] DuckDB's read_xlsx takes its range option in spreadsheet notation, so A1:B2 reads the cells from A1 to B2.[1]

In Excel's default 1900 date system, a date is stored as a serial number of the days elapsed since January 1, 1900.[8] Earlier versions of Excel for Mac defaulted to a 1904 date system, and the same date's serial number is 1,462 days smaller in the 1904 system than in the 1900 system.[8] Excel incorrectly treats 1900 as a leap year, an assumption Lotus 1-2-3 made first and that Excel kept for compatibility with the Lotus 1-2-3 serial date system.[9] DuckDB's excel extension infers TIMESTAMP, TIME, DATE and BOOLEAN types when possible from the format applied to a cell.[1] When dates read from a workbook look shifted by years, check which date system the workbook uses before correcting them by hand. (Meridian's view)

Compared with the alternatives

In DuckDB, COPY from a .xlsx file into an existing table does no type inference, and casts each cell to the type of its target column.[1] Reading with COPY into a table you have already declared suits a workbook whose layout you know; read_xlsx suits a first look at one you do not. (Meridian's view)

Sources

  1. [1]Excel Extension – DuckDBdocumentation
  2. [2]pandas.read_excel — pandas 3.1.1 documentationdocumentation
  3. [3]Spreadsheet - Wikipediasecondary source
  4. [4]Excel Readerdocumentation
  5. [5]Office Open XML - Wikipediasecondary source
  6. [6]Structure of a SpreadsheetML document | Microsoft Learndocumentation
  7. [7]OpenDocument - Wikipediasecondary source
  8. [8]Date systems in Excel | Microsoft Supportdocumentation
  9. [9]Excel incorrectly assumes that the year 1900 is a leap year - Microsoft 365 Apps | Microsoft Learndocumentation

Used in

2 Protocols use this operation: