topic Re: [resolved] cannot convert string to date in tMap in Talend Studio

[resolved] cannot convert string to date in tMap

soujanyam — Sat, 16 Nov 2024 11:23:42 GMT

Hi everyone.
I've the csv file in which one column has date (which is defined as Number in csv file) but in tFileInpuDelimited it read as string in which some of the vales are like 20140424 and some are like 2014-04-25 in the same column.I've to parse this data into output component( tOracleOutput)as date column.So I tried to convert the string to date by using
TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd",row1.column1)) and changed the output column as date with format yyyy-MM-dd.
Then I'm getting error as
Unparseable date: "20140424".
How could I parse this into output component?
Any help would be greatly appreciated.
Thank you in advance.

Re: [resolved] cannot convert string to date in tMap

Anonymous — Mon, 08 Dec 2014 10:43:33 GMT

use below code.

TalendDate.parseDate("yyyyMMdd",row1.column1)

Re: [resolved] cannot convert string to date in tMap

soujanyam — Mon, 08 Dec 2014 10:53:59 GMT

Thanks for your prompt reply umesh.
I tried even that also. But it didn't works. Would you suggest more ideas?
Can I write if else condition in tMap to take any value either yyyyMMdd format or yyyy-MM-dd format and then to insert into output column as date with yyyy-MM-dd format? If so How should I implement this?
Any help would be appreciated.
Thank you.

Re: [resolved] cannot convert string to date in tMap

Anonymous — Mon, 08 Dec 2014 11:00:23 GMT

output will not ask you for specific format, it will required only Date object, If you show me input string from your file then I can suggest you better.

Re: [resolved] cannot convert string to date in tMap

soujanyam — Mon, 08 Dec 2014 11:19:29 GMT

Thanks for your reply. The input column is as below.
Date
2014-12-21
20140214
2014-12-13
2014-12-23
20141201
The output should be in yyyy-MM-dd fromat only whatever may be the input format.

Re: [resolved] cannot convert string to date in tMap

Anonymous — Mon, 08 Dec 2014 12:02:47 GMT

So you have two formats in single column. then you can use below logic which help you.


yourcolumn!=null && yourcolumn!=" "? 
TalendDate.parseDate("yyyyMMdd', yourcolumn.replaceAll("-", "")):null

this will solve your problem.

Re: [resolved] cannot convert string to date in tMap

soujanyam — Mon, 08 Dec 2014 12:31:03 GMT

Thanks for your prompt reply.
Sorry Umesh I forgor to tell you onemore thing.
I also had -1,-2 values in that same column.That above problem is solved with TalendDate.parseDate("yyyyMMdd",row1.Column1).
Now the problem is with -1,-2 values. I've to place 0000-00-00 in output column when I get those values from input file.
How could I do this?
Thanks you

Re: [resolved] cannot convert string to date in tMap

Anonymous — Mon, 08 Dec 2014 13:19:30 GMT

You can do in the same way, you have to check the length of column if it is less than expected then replace with, desire string.

Re: [resolved] cannot convert string to date in tMap

soujanyam — Tue, 09 Dec 2014 04:17:57 GMT

Thanks for your reply Umesh. But I didn't get you. Where to check the length of column?

Re: [resolved] cannot convert string to date in tMap

Anonymous — Tue, 09 Dec 2014 07:03:12 GMT

check the length of the column in same code.

yourcolumn!=null && yourcolumn!=" " && yourcolumn.length>68? 
TalendDate.parseDate("yyyyMMdd', yourcolumn.replaceAll("-", "")):TalendDate.parseDate("yyyyMMdd', "000000")

Re: [resolved] cannot convert string to date in tMap

soujanyam — Tue, 09 Dec 2014 12:08:30 GMT

Thank you but if I had different data formats in input column how to parse it.
When input has only two formats "20140224" or "2014-02-24" by using TalendDate.parseDate("yyyyMMdd",row1.Column1), data is parsing to the output.
When date includes "02/24/2014" with above formats,
I used in tMap as
TalendDate.isDate(row1.Column1,"MM/dd/yyyy")?TalendDate.parseDate("yyyy-MM-dd",Column1):TalendDate.parseDate("yyyyMMdd",row1.Column1))
then could not parse the data.Getting error as
Caused by: java.text.ParseException: Unparseable date: "02/24/14"
What could I do?
Thank you

Re: [resolved] cannot convert string to date in tMap

Anonymous — Tue, 09 Dec 2014 12:14:51 GMT

First get a list of formats then do above logic for replace and then do the parsing that will help. use each parsing according to source format.
if there are multiple formats then create routine and write all the check and validation to handle date conversion, and then return date object form routine function. this is the best way for you.

Re: [resolved] cannot convert string to date in tMap

soujanyam — Tue, 09 Dec 2014 12:47:43 GMT

Ya I've checked the source data format. It has the above three formats only. So how could I handle?
What should I modify in the function which I've used
TalendDate.isDate(row1.Column1,"MM/dd/yyyy")?TalendDate.parseDate("yyyy-MM-dd",row1.Column1):TalendDate.parseDate("yyyyMMdd",row1.Column1))
Please provide the solution to me. I'm eagerly waiting for the solution.
Thank you.

Re: [resolved] cannot convert string to date in tMap

Anonymous — Tue, 09 Dec 2014 13:30:17 GMT

try this.

yourcolumn!=null && yourcolumn!=" " && yourcolumn.length>68? yourcolumn.contains("-")?
TalendDate.parseDate("yyyyMMdd', yourcolumn.replaceAll("-", "")):TalendDate.parseDate("MM/dd/yyyy', yourcolumn):TalendDate.parseDate("yyyyMMdd', "000000")

Re: [resolved] cannot convert string to date in tMap

Anonymous — Thu, 19 Nov 2015 09:01:55 GMT

Hi Umesh,
I have situation that, I have source table with 2 columns as follows..
START_DT NAME
01/12/2015 PANKAJ
02/27/2010 SURAJ
START_DT has string datatype in source table but i just want it in DATE datatype without changing its values format.
how i can achieve that? give solution..
thanx in advance.

Regards,
Shri_Kul1
Talend User.

Re: [resolved] cannot convert string to date in tMap

Anonymous — Thu, 19 Nov 2015 09:10:49 GMT

Hi Shri_Kul1,
You can use built-in function TalendDate.parseDate(string date_pattern, string string_of_date) to convert String to Date in tMap.

Best regards
Sabrina

Re: [resolved] cannot convert string to date in tMap

Anonymous — Thu, 19 Nov 2015 09:35:59 GMT

Hey Hi,
could you please give me example with that logic you mentioned.
Regards,
Shri_Kul1

Re: [resolved] cannot convert string to date in tMap

Anonymous — Thu, 19 Nov 2015 09:48:55 GMT

Hey ,
Thanks for your valuable reply , its working .... Thanks a Lot.
Regards,
Shri_Kul1
Talend User.

Re: [resolved] cannot convert string to date in tMap

Anonymous — Thu, 19 Nov 2015 09:55:15 GMT

Hi Shri_Kul1,
Please take a look at my screenshot.
Feel free to let us know if it is what you are looking for.
Best regards
Sabrina

Re: [resolved] cannot convert string to date in tMap

Anonymous — Thu, 19 Nov 2015 11:19:57 GMT

Hi,
Exactly, i have done as posted in screen shot.
Thanks Sabrina for your valuable feedback.

Regards,
Shri_Kul1
Talend User.