Excel Extension fails to load all data even though stop_at_empty = false

[ZnqbuZ]

What happens?

When there is an empty column in the sheet, duckdb stops at that column.

To Reproduce

Code:

import duckdb

print(
    duckdb.sql(
        "SELECT * FROM read_xlsx('D:/test.xlsx', all_varchar=true, header=false, stop_at_empty=false, ignore_errors = true);"
    ).fetchall()
)

Content of D:/test.xlsx:

	A	B	C
1	X
2
3			X

Result:
[('X',), (None,)]

Expected:
[('X', None, None), (None, None, None), (None, None, X)], or at least [('X',), (None,), (None,)]

OS:

Windows x86_64

DuckDB Package Version:

1.3.2

Python Version:

3.13.7

Full Name:

Luna Yao

Affiliation:

Self

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have

[ZnqbuZ]

I wrote a piece of code to get the actual range, in case anyone needs it:

sheets: dict[int, str] = {}
ranges: dict[int, str] = {}
with zipfile.ZipFile(source) as z:
    ns = {"ns": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}
    with z.open("xl/workbook.xml") as f:
        from xml.etree.ElementTree import parse

        tree = parse(f)
        root = tree.getroot()
        for sheet in root.findall("ns:sheets/ns:sheet", ns):
            sheets[int(sheet.attrib["sheetId"])] = sheet.attrib["name"]
    for sheet_id in sheets:
        with z.open(f"xl/worksheets/sheet{sheet_id}.xml") as f:
            from xml.etree.ElementTree import iterparse

            has_dimension = False
            for event, elem in iterparse(f, events=("start",)):
                if elem.tag == f"{{{ns['ns']}}}dimension":
                    if not (sheet_range := elem.attrib["ref"]):
                        break
                    if ":" not in sheet_range:
                        sheet_range = f"{sheet_range}:{sheet_range}"
                    ranges[sheet_id] = sheet_range
                    has_dimension = True
                    break
                if elem.tag == f"{{{ns['ns']}}}sheetData":
                    break
            if not has_dimension:
                raise NotImplementedError()

[evertlammerts]

This seems a duckdb core issue, it reproduces in the CLI:

DuckDB v1.4.0 (Andium) b8a06e4a22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D SELECT * FROM read_xlsx('test.xlsx', all_varchar=true, header=false, stop_at_empty=false, ignore_errors = true);
┌─────────┐
│   A1    │
│ varchar │
├─────────┤
│ X       │
│ NULL    │
│ NULL    │
└─────────┘

@szarnyasg can you move this over?

[ZnqbuZ]

This seems a duckdb core issue, it reproduces in the CLI:

DuckDB v1.4.0 (Andium) b8a06e4a22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D SELECT * FROM read_xlsx('test.xlsx', all_varchar=true, header=false, stop_at_empty=false, ignore_errors = true);
┌─────────┐
│   A1    │
│ varchar │
├─────────┤
│ X       │
│ NULL    │
│ NULL    │
└─────────┘

@szarnyasg can you move this over?

It might actually be expected to get X, NULL, NULL according to the documentation - I'm not sure I understand it correctly, but it would still be better if duckdb can infer the full range.

duckdb-python returns X, NULL, so it's incorrect either way.