CODEWITHSUNDEEP
exceldate-formattingimport-from-excelduckdb
ArtBajji
ArtBajji

Reputation: 960

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.

Upvotes: 1

Views: 435

Answers (2)

Mike D Sutton
Mike D Sutton

Reputation: 806

In case it helps anyone else, we had date/time values that were being imported as double values e.g: 45566.948181597225. What eventually worked for our data was to convert the values into seconds, and add the Excel date epoch:

SELECT *,
    '1899-12-30'::TIMESTAMP + INTERVAL(my_date_col * 86400) SECOND AS my_date_col
    FROM st_read("file.xlsx", layer="sheet_name");

Upvotes: 1

hawkfish
hawkfish

Reputation: 351

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

Upvotes: 4

Related Questions