Filesystem: Read _multiple_ spreadsheets from workbook files

[amotl]

🎯 Functional Correctness | 🟠 Major | ⚡ Quick win

🧩 Analysis chain

🏁 Script executed:

#!/bin/bash
set -euo pipefail

echo "== readers.py around read_spreadsheet =="
sed -n '250,330p' omniload/source/filesystem/format/readers.py

echo
echo "== search for spreadsheet readers and related helpers =="
rg -n "read_spreadsheet|read_excel|read_ods|sheet_name|sheet_id" omniload/source/filesystem -S

echo
echo "== polars version hints =="
rg -n "polars" pyproject.toml requirements* uv.lock poetry.lock setup.cfg setup.py -S || true

Repository: panodata/omniload

Length of output: 6871

---

🌐 Web query:

Polars Python read_excel read_ods multi-sheet return dict sheet_id=0 multiple sheet names rows named=True

💡 Result:

In Polars, the functions pl.read_excel and pl.read_ods allow you to read multiple sheets into a dictionary by configuring the sheet selection parameters [1][2][3]. When reading multiple sheets, both functions return a dictionary where the keys are the sheet names and the values are the corresponding DataFrames [1][4][3]. Key behaviors for sheet selection: - To load all available sheets, you can set sheet_id=0 [5][3][6]. - If you provide a sequence of sheet IDs (e.g., sheet_id=[1][2]) or a sequence of sheet names (e.g., sheet_name=["Sheet1", "Sheet2"]), the function will return a dictionary containing only those specified sheets [5][3][6]. - The sheet_id and sheet_name parameters cannot be used together [5][3]. - Note that sheet_id indexing typically starts from 1 [3]. Regarding row and header configuration: - While Polars does not have a direct parameter named named=True, you can control header parsing using the has_header parameter, which defaults to True [4][3]. - If your data lacks a header row, setting has_header=False will cause Polars to auto-generate column names in the format column_x [4]. Important considerations: - All sheets read in a single function call will share the same global read options (such as has_header, schema_overrides, etc.) [5][3]. If you require different settings for different sheets, you must perform separate function calls for each sheet [5][3]. - It is generally recommended to use the default "calamine" engine for Excel files for better performance [3].

Citations:

• 1: https://docs.pola.rs/api/python/stable/reference/api/polars.read_excel.html
• 2: https://docs.pola.rs/api/python/stable/reference/api/polars.read_ods.html
• 3: https://github.com/pola-rs/polars/blob/py-1.42.1/py-polars/src/polars/io/spreadsheet/functions.py
• 4: https://github.com/pola-rs/polars/blob/main/py-polars/src/polars/io/spreadsheet/functions.py
• 5: https://docs.pola.rs/api/python/dev/reference/api/polars.read_excel.html
• 6: https://docs.pola.rs/api/python/version/1/reference/api/polars.read_excel.html

---

🏁 Script executed:

#!/bin/bash
set -euo pipefail

python3 - <<'PY'
import sys, json, textwrap, os
print("Python available:", sys.version)
PY

Repository: panodata/omniload

Length of output: 223

---

Handle Polars multi-sheet results before .rows(). pl.read_excel/pl.read_ods can return dict[str, DataFrame] for sheet_id=0 or multiple sheet names, so this path still raises AttributeError on supported inputs. Fan out the frames here or reject multi-sheet selectors explicitly; add a test for the chosen behavior.

🤖 Prompt for AI Agents

Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

In `@omniload/source/filesystem/format/readers.py` around lines 288 - 304, The
read_spreadsheet function must handle readers returning a dict of sheet names to
DataFrames before calling rows(). Choose and implement either flattening each
sheet’s rows into the iterator or explicitly rejecting multi-sheet selectors,
while preserving single-DataFrame behavior; add a test covering the selected
multi-sheet behavior.

Originally posted by @coderabbitai[bot] in #222 (comment)

[amotl]

Proposal

Effectively, in dlt jargon, a workbook file should be treated as a database, and its included worksheets as tables. Importing a single / the first worksheet remains an edge case: The standard path should be streamlined around the plural form of a workbook / worksheet to database / table mapping.

In this spirit, it would be sweet to receive a first-class SpreadsheetSource dlt component that implements this across the board. It should bundle access to XLSX and ODS as a minimum and possibly also others like discontinued Lotus 1-2-3; Google Sheets, anyone?

[amotl]

Outlook

Off topic: In theory, connecting to any other Excel or Google Sheets alternative that provides a file format or an API of some sort can make sense?

Calligra Sheets, Collabora Calc, CryptPad Sheets, Framacalc, Grist, ONLYOFFICE Sheets, PlanMaker (FreeOffice), Row Zero, Rows