Get Internal Excel Date/Time Number from tFileInputExcel in Talend

[Anonymous]

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?

[TRF]

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.

[Anonymous]

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)

[Anonymous]

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.

[Anonymous]

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.