Welcome Guest! Log in
×

Notice

The forum is in read only mode.
The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

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

More
27 Jul 2016 18:41 #1 by Khadija Elyo
Lecture d'une date à partir d'un fichier Excel was created 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
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>)
...
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 :(
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 :)
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 ;)
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:
More
19 Sep 2016 16:14 #7 by Nikolay
Replied by Nikolay on topic Lecture d'une date à partir d'un fichier Excel
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)