topic Re: Get Internal Excel Date/Time Number from tFileInputExcel in Talend in Talend Studio

Get Internal Excel Date/Time Number from tFileInputExcel in Talend

Anonymous — Tue, 20 Feb 2018 07:53:19 GMT

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?

Re: Get Internal Excel Date/Time Number from tFileInputExcel in Talend

TRF — Tue, 20 Feb 2018 08:05:05 GMT

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.

Re: Get Internal Excel Date/Time Number from tFileInputExcel in Talend

Anonymous — Tue, 20 Feb 2018 08:33:20 GMT

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)

Re: Get Internal Excel Date/Time Number from tFileInputExcel in Talend

Anonymous — Tue, 20 Feb 2018 08:39:06 GMT

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.

Re: Get Internal Excel Date/Time Number from tFileInputExcel in Talend

Anonymous — Thu, 26 Jul 2018 19:48:52 GMT

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.