Date Conversion

Six Stars

Date Conversion

Hi All,


I have a date(String data type) column in excel file , values are in 2 formats 'dd.MM.yyyy' and 'dd/MM/yyyy'.
I am trying to convert this string type to date using TalendDate.parseDate("dd/MM/yyyy",row2.date).
How am I supposed to use TalendDate.parseDate() for both the date formats within tMap at once?
Right now I am getting Unparseable date format exception since I am unable to handle this using 'dd/MM/yyyy' format. Please refer below for further details - 

 

Exception in component tMap_10 
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "21.08.2018"
at routines.TalendDate.parseDate(TalendDate.java:892)
at routines.TalendDate.parseDate(TalendDate.java:836)


Accepted Solutions
Employee

Re: Date Conversion

Hi,

 

    If you are sure that the date values will come only in above two formats, the easiest way will be to replace the character.

 

Relational.ISNULL(row1.input_date)? null:TalendDate.parseDate("dd/MM/yyyy",row1.input_date.replace(".","/")) 

 

   Could you please try this code in tmap and let us know whether it helped to resolve your issue. 

 

   If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome :-)

 

Warm Regards,

 

Nikhil Thampi

View solution in original post


All Replies
Twelve Stars

Re: Date Conversion

excel store date as date format and display it using local configuration.
wat you see in excel is not what Talend see.
if excel think your fiel is a date. he is going to format it a s date an store it as he want.
1-try to read date directly from excel.
2-read as String and print data (tLog) to check the format.
3- try to trim data before formating
good luck

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: Date Conversion

Hi fdenis,

I had already applied all the approaches you have mentioned. Is there no other way to parse the Date column

for both the formats as discussed above i.e., within tMap itself?

 

Please let me know if you need any additional information from my end.

 

Thanks in advance!

Twelve Stars

Re: Date Conversion

you can manually format fate using java Split function and retrieving the 3 parts of the date. it may help you to solve your problème.
you may use tjavaRow to complet this task.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: Date Conversion

Hi fdenis,

 

I still couldn't figure out the exact process. Could you please explain in a bit illustrative way?

 

Thanks in advance!

 

Employee

Re: Date Conversion

Hi,

 

    If you are sure that the date values will come only in above two formats, the easiest way will be to replace the character.

 

Relational.ISNULL(row1.input_date)? null:TalendDate.parseDate("dd/MM/yyyy",row1.input_date.replace(".","/")) 

 

   Could you please try this code in tmap and let us know whether it helped to resolve your issue. 

 

   If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome :-)

 

Warm Regards,

 

Nikhil Thampi

View solution in original post

Six Stars

Re: Date Conversion

Hi All,

 

Is there a way to parse the date from "dd.MM.yy" to "dd/MM/yyyy" ?

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 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog