Get Internal Excel Date/Time Number from tFileInputExcel in Talend

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?

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.

Five Stars

Re: Get Internal Excel Date Number from tFileInputExcel in Talend

Bringing this back up. I have the exact same problem and cannot find a solution. Some column are formated in short date, other in time. And we need them to be exported in SQL in datetime, so when in short date, we lose the time, and formated in time, we have no date.