Search code examples
exceldate-formattingimport-from-excelduckdb

Duckdb : Date values are read as numbers while reading from excel file. How to read them as dates?

I am using Jupyter notebook 7.0.8
DuckDB version v0.10.2

import duckdb as d

con = duckdb.connect("mydb.db")

con.sql('INSTALL spatial')
con.sql('LOAD spatial')

con.sql("""SELECT my_date_col FROM st_read("my_test_excel.xlsx", layer="Sheet 1")""")

Output:
MY_DATE_COL
int32
45298
45298

I read an excel file, "my_test_excel.xlsx" from my laptop computer using duckDB's spatial function st_read. The file contains a date column with date value "07-01-2024 00:00:00".

The output displays the date in the general format of excel as numbers instead of dates. Is there a way to read the dates from excel file as dates.

I am planning to store these values into duck db.

Solution

  • A simple fix for now is to just add the Excel epoch date to the column using the REPLACE star expression:

    SELECT * REPLACE('1899-12-30'::DATE + my_date_col AS my_date_col)
FROM ...