Convert Excel column type "General" containing date value as "8/1/2017" to yyyy-MM-dd format in the targeted column type "String(255)"

[jbhatt1810]

Hello, I am trying to parse excel data into mysql table. One of the excel column has values like "8/1/2017" (date) and some of rows for that column has values like string,text etc. Due to this mix type of values in column, I cannot specify column to Date or any specific format hence currently it is as "General" type.

What I want to achieve is, if the column cell has date value like "8/1/2017" in mm/dd/yyyy format then convert that to "yyyy-MM-dd" format and insert into the table. If it is not the date in mm/dd/yyyy then simply ignore and insert the value as it is into the table. I tried multiple things in tMAP but it didnt work out. How do I achieve this? Please note, source table row2 and output "out1" both has column type string (255). See attached for screenshot of the job. Also, currently I am using tLogRow just to make sure the output before inserting to table.

[manodwhb]

@Janki Bhatt​ , you need to validate by using this expression in tMap output section filter.

TalendDate.isDate("8/1/2017","mm/dd/yyyy")

and check the below link

https://community.talend.com/s/feed/0D53p00007vCn60CAC

[jbhatt1810]

@Manohar B​ Yeah I read that link few days back and tried below but it doesnt work and throws me "The method formatDate(String, Date) in the type TalendDate is not applicable for the arguments (String, String)" error. See attached for error and updated job screenshot.

Expression I tried: TalendDate.isDate(row2.Tag4,"M/d/yyyy")?TalendDate.formatDate("yyyy-MM-dd", row2.Tag4): row2.Tag4

[manodwhb]

is 8/1/2017 the format of "mm/dd/yyyy"? I do not think.

[jbhatt1810]

No the format is "M/d/yyyy" per isDate.. I tried running just the isDate and it returns true for M/d/yyyy format