Problem with format date and time in Metadata Excel File

Seven Stars

Problem with format date and time in Metadata Excel File

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.

 

1.PNG2.PNG

 

 

 


Accepted Solutions
Seven Stars

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

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);


All Replies
Employee

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

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 :-)

Seven Stars

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

Here are screenshots of my job.

 

5.PNG4.PNG

Employee

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

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 :-)

 

Seven Stars

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

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. 

2.PNG

 

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

 

3.PNG

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

 

1.PNG

Employee

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

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 :-)

Seven Stars

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

Hi,

 

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

 

4.PNG
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,

Employee

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

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.

image.png

 

 

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 :-)

Seven Stars

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

Hi,

 

Here is the result of the job.

 

sortie.PNG

 

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,

Highlighted
Employee

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

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

Seven Stars

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

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);

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog