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

Activity

1. ZnqbuZ commented on Sep 17, 2025

   

   ZnqbuZ

   on Sep 17, 2025

   Author

   More actions

   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()
   
2. evertlammerts commented on Sep 17, 2025

   

   evertlammerts

   on Sep 17, 2025

   Member

   More actions

   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?
3. ZnqbuZ commented on Sep 17, 2025

   

   ZnqbuZ

   on Sep 17, 2025

   Author

   More actions

   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.
4. evertlammerts

   added

   reproduced

   on Sep 18, 2025
5. szarnyasg

   added

   reproduced

   and removed

   reproduced

   on Oct 2, 2025