Spatial extension reading Excel Files : HEADERS=FORCE applied randomly or only on the first sheet

[frankjar]

What happens?

the HEADERS=FORCE open options for the st_read() function works only for the first sheet of the Excel file and randomly for the next sheets. When the HEADERS=FORCE doesn't work, columns result are 'Field1, Field2...' not the first row in the sheet. Then the first row is processed as a data row (not as columns names).

To Reproduce

test.xlsx

This excel file contains 2 sheets : Sheet1 and Sheet2 (names changed by the user).

❯ duckdb
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D install spatial; load spatial;
D select * from st_read('test.xlsx',layer='Sheet1',open_options = ['HEADERS=FORCE']);
┌─────────┬─────────┐
│  COL1   │  COL2   │
│ varchar │ varchar │
├─────────┼─────────┤
│ A       │ A       │
│ B       │ B       │
└─────────┴─────────┘
D  select * from st_read('test.xlsx',layer='Sheet2',open_options = ['HEADERS=FORCE']);
┌─────────┬─────────┐
│ Field1  │ Field2  │
│ varchar │ varchar │
├─────────┼─────────┤
│ COL3    │ COL4    │
│ C       │ C       │
│ D       │ D       │
└─────────┴─────────┘

For Sheet1 : headers are used as columns names For Sheet2 : headers are not used, replaced by FieldX name

Thanks a lot

OS:

MacOS

DuckDB Version:

1.0.0

DuckDB Client:

CLI

Full Name:

Frank Jardillier

Affiliation:

compas

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?

• [X] Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• [X] Yes, I have

[Bangal22]

I have the same problem

[iligid]

Same with me. First sheet works, second not.