Problem with format date and time in Metadata Excel File

[INESBK]

Hi,

I would like to read data from an excel file. Amongst the columns of this file, there are Date and Time columns that are filled using expression in the excel file.
Using talend and when I add a metadata File I get a preview at the beginning of these expressions as shown in Figure 1. And then a weird preview of the data formats in the Date and Time column as show in Figure2. 

How can I solve this problem especially that I need the date column format  be "dd/MM/yyyy" and the time column be "HH:mm:ss" ?

Thanks in advance for your help.

[Anonymous]

Hi,

   Could you please share the job screenshots and the component details to the post?

Warm Regards,
Nikhil Thampi

Please appreciate our members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[INESBK]

Here are screenshots of my job.

3.PNG
4.PNG

[Anonymous]

Hi,

    The data will be picked based on the base format for the excel column. Since the base format of the column is in different format (for example like below), you will have to first parse the date in that format and then convert it later to the format of your choice.

Mon Jan 01 09:10:13 IST 2018

    I would suggest to first parse the data as-is using a String variable and do a data type conversion within Talend using tConvertType component.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[INESBK]

Hi,

Thanks for your replay.

 I used the tConvertType component to convert an input string to date ("EEE MMM dd HH:mm:ss zzzz yyyy") as shown in figure 1. 

Than i used tmap compnent to convert this format in simple format (yyyy-MM-dd) as show in figure 2.

But Nothing is displayed and no error as is shown in figure 3.

[Anonymous]

Hi,

    Could you please print the output immediately after reading from excel sheet and verify the results?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[INESBK]

Hi,

Here is an example of the output of the excel file.

I would like to change the format of the input date into "yyyy-MM-dd"
I would like to change the format of the input time into "HH:mm:ss"
Already if you have noticed the format of the time (column 2) is strange and I would like to take from this format just the time. 

Best regards,

[Anonymous]

Hi,

Could you please add tLogrow here and print the results for verification. Once you identify the stage correctly, we can check the next steps from that point.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[INESBK]

Hi,

Here is the result of the job.

The first line is the date and time in string format (on the same format as they were read in the metadata repositry). The empty second and third lines correspond to the t_logrow4 and tlogrow1 .

Best regards,

[Anonymous]

Thanks for the details.

The expressions in the excel sheet seems to create issues and it is not able to identify the time part of the data.

I am not fully sure about it but let's check with @xdshi for her advice.

Warm Regards,

Nikhil Thampi