Welcome Guest! Log in

Topic-icon Solved Lecture d'une date à partir d'un fichier Excel

More
27 Jul 2016 18:41 #1 by Khadija Elyo
Hello Everyone,

I am using a Microsoft Excel Metadata to get dates from an Excel file in this format "DD/MM/YYYY".
The metadata return a code instead of the date.
Is there any way to read the value in the presented format?

Thank you

Please Log in or Create an account to join the conversation.

More
28 Jul 2016 18:59 #2 by Cyril Dussud
Replied by Cyril Dussud on topic Lecture d'une date à partir d'un fichier Excel
Hi,

Excel stores the dates as numbers.
The formatting that you can see in the Excel file is only for display purposes and everything is stored as number in the background.
It is so normal that the value returned by the driver is a number.

When using the Excel metadata in a mapping, you could use sql functions to convert data :
to_number(<column>)
to_date(<column>)
to_timestamp(<column>)
to_time(<column>)
...

Please Log in or Create an account to join the conversation.

More
29 Jul 2016 17:27 #3 by Khadija Elyo
Replied by Khadija Elyo on topic Lecture d'une date à partir d'un fichier Excel
Hi,
I've tried with to_date() function but it does not work for me.
for exemple to extract the day i tried: to_date(FIELD,'DD')
but it doesn't work :(

Please Log in or Create an account to join the conversation.

More
01 Aug 2016 10:27 #4 by Khadija Elyo
Replied by Khadija Elyo on topic Lecture d'une date à partir d'un fichier Excel
Hello Everyone,

Thanks for replying, it works for me now.
I've used the next formula:
to_date('1900-00-00','YYYY-MM-DD') + FIELD::integer
It works nice :)

Please Log in or Create an account to join the conversation.

More
01 Aug 2016 10:59 #5 by Thomas BLETON
Replied by Thomas BLETON on topic Lecture d'une date à partir d'un fichier Excel
Great, thanks for sharing your solution ;)

Please Log in or Create an account to join the conversation.

More
01 Aug 2016 11:03 #6 by Khadija Elyo
Replied by Khadija Elyo on topic Lecture d'une date à partir d'un fichier Excel
Welcome :cheer:

Please Log in or Create an account to join the conversation.

More
19 Sep 2016 16:14 #7 by Nikolay
more precisely :

DATE('1900-01-01')+(INT(EXCELDATE)-2) days

cause Excel considers 1900/01/01 as 1-st day (not a 0 day) and that 1900 year is leap (but it is not)

Please Log in or Create an account to join the conversation.