How can I re-write column names when reading from an Excel file?

[tboddyspargo]

We would like to be able to apply some "column name normalization" logic to the headers in an Excel sheet that are quite similar to the logic applied by read_csv_auto (e.g. spaces become underscores). However, I'm not sure that st_read or GDAL provide any options for achieving this.

With CSV files, I can do: SELECT * FROM read_csv_auto('s3://bucket/path.csv', names=['custom_name1', 'custom_name2']) to set my own column names to use. Without that, the columns names would also be normalized according to read_csv_auto's own logic (which I like).

However, with SELECT * FROM st_read('s3://bucket/path.xlsx', layer='sheet 1', open_options=['HEADERS=force']), no normalization logic is applied and I don't see an obvious way for me to provide my own custom column names.

Any suggestions for how to enforce some column name normalization logic would be greatly appreciated!

[Maxxen]

No, this is not possible in the underlying GDAL driver. But you could provide your own column names by simply aliasing the table expression:

SELECT * FROM st_read('./google_sheets.xlsx', max_batch_size = 100) 
AS  my_table(custom_name1,custom_name2,custom_name3);

┌──────────────┬──────────────┬──────────────┐
│ custom_name1 │ custom_name2 │ custom_name3 │
│    double    │   varchar    │   varchar    │
├──────────────┼──────────────┼──────────────┤
│        123.0 │ 500,60       │ ABC          │
│        456.0 │ 300,40       │ some text    │
└──────────────┴──────────────┴──────────────┘

[Maxxen]

Hello!

As of DuckDB 1.2.0 the duckdb excel extension provides support for reading and writing xlsx files with greater efficiency and many more options. Therefore we're going to deprecate this feature in the spatial extension in the future. I would recommend that you try out the excel extension, and if you run into any problems please open an issue over at the duckdb-excel repository.