topic Re: Problem with format date and time in Metadata Excel File in Talend Studio

Problem with format date and time in Metadata Excel File

INESBK — Tue, 18 Dec 2018 10:53:08 GMT

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.

Re: Problem with format date and time in Metadata Excel File

Anonymous — Tue, 18 Dec 2018 12:22:58 GMT

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 🙂

Re: Problem with format date and time in Metadata Excel File

INESBK — Tue, 18 Dec 2018 13:01:56 GMT

Here are screenshots of my job.

3.PNG
4.PNG

Re: Problem with format date and time in Metadata Excel File

Anonymous — Tue, 18 Dec 2018 18:46:30 GMT

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 🙂

Re: Problem with format date and time in Metadata Excel File

INESBK — Wed, 19 Dec 2018 09:04:24 GMT

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.

Re: Problem with format date and time in Metadata Excel File

Anonymous — Wed, 19 Dec 2018 09:23:08 GMT

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 🙂

Re: Problem with format date and time in Metadata Excel File

INESBK — Wed, 19 Dec 2018 09:36:47 GMT

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,

Re: Problem with format date and time in Metadata Excel File

Anonymous — Wed, 19 Dec 2018 11:30:40 GMT

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 🙂

Re: Problem with format date and time in Metadata Excel File

INESBK — Wed, 19 Dec 2018 12:49:18 GMT

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,

Re: Problem with format date and time in Metadata Excel File

Anonymous — Thu, 20 Dec 2018 06:32:10 GMT

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

Re: Problem with format date and time in Metadata Excel File

INESBK — Mon, 14 Jan 2019 17:23:51 GMT

Hi,

I solved the problem using a routine to convert the date with code.

here is the solution :

SimpleDateFormat sdf=new SimpleDateFormat ("EEE MMM dd HH:mm:ss Z yyyy", Locale.ENGLISH);

return sdf.parse(d);