Five Stars

Get Internal Excel Date/Time Number from tFileInputExcel in Talend

Hi,

 

Is there a way, we can read the internal excel (.xlsx) numeric representation of dates with times using the tFileInputExcel Component, or any other component?

 

My problem is, the excel files can have different format, and it is not feasible to go and change the formats to a standard format in hundreds of excel files every time.If I can get the numeric representation, I can easily convert it to any format I want, and the format in excel will not matter at all.

 

Does any solution exists to achieve this?

3 REPLIES
Forteen Stars TRF
Forteen Stars

Re: Get Internal Excel Date Number from tFileInputExcel in Talend

Hi,

Are you using Date datatype to declare field associated field your Excel date cells?

This link may help you http://bekwam.blogspot.fr/2011/03/excel-2007-dates-in-talend-open-studio.html.


TRF
Five Stars

Re: Get Internal Excel Date Number from tFileInputExcel in Talend

Thank you for the prompt reply and the blog link.

Although, my problem is also with the time.

For Example:

My Excel cell is formatted as(dd-MM-yyyy): 10-04-2017

but the real value (visible in formula bar) is : 10-04-2017 10:06:14

 

The internal excel representation is: 42835.4209997505

 

The default behaviour is good enough for dates, but can I do some thing for the time as well?

 

 

And, for your question, No I am reading them as strings because, if I read it as a date in meta-data, I will need to specify the format of the excel cell. (I do not wish to do that)

 

Five Stars

Re: Get Internal Excel Date Number from tFileInputExcel in Talend

Addition to last, if I read the row with the real value (visible in formula bar) is : 10-04-2017 10:06:14 but formatted as(dd-MM-yyyy): 10-04-2017, then in the tLogRow I get only 4/10/17.

 

While, I would atleast want to see 4/10/17 10:06:14, or the internal excel representation with fraction part, or any other way, such that I can get the time part as well.