topic Re: [resolved] unparseable date in mysql to salesforce in Talend Studio

[resolved] unparseable date in mysql to salesforce

Anonymous — Sat, 16 Nov 2024 13:03:47 GMT

I am going from mysql to salesforce and I am trying to insert records that have a date value in one of the fields. In mysql, the format is "yyyy-mm-dd hh:mm:ss" and I know the format that salesforce accepts in a datetime data type is "yyyy-mm-ddThh:mm:ss.000Z".
I am just doing a simple mapping in the tmap without any expressions. I get the following error when I run my job:

Exception in component tSalesforceBulkExec_1
java.lang.RuntimeException: Unparseable date: "2010-10-06T16:53:27.000Z"
	at routines.system.ParserUtils.parseTo_Date(ParserUtils.java:164)

Thanks for the help in advance.

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Wed, 02 Mar 2011 08:57:48 GMT

Hi
Do you change the date pattern on tMap? please upload a screenshot of tMap.
Best regards
Shong

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Thu, 03 Mar 2011 19:13:01 GMT

I have attached my tmap.
I will also add that I am able to write successfully to other date data type fields on other objects in my project. Here I am writing to a custom field of type date/time(salesforce) on the Contact object. The other successful writes were on custom objects and custom fields. Don't know why I am getting this error only here. Hopefully this will help debug the issue.
Thanks.

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Thu, 03 Mar 2011 19:16:49 GMT

Oops. Image was too big. Here it is...

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Thu, 03 Mar 2011 20:10:51 GMT

Because you have two different date-formats (left side: yyyy-mm-dd hh:mm:ss / right side: yyyy-MM-dd'T'HH:mm:ss'.000Z'), you have to format the date of the left side to the format of the right side "yyyy-MM-dd'T'HH:mm:ss'.000Z'".
Try to replace on the right side "salesforce_flattened_user.member_since" with "TalendDate.formatDate("yyyy-MM-dd'T'HH:mm:ss'.000Z'", salesforce_flattened_user.member_since)".
For different date related routines you can use the expression builder. In the expression builder are the routines and their usage described.

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Thu, 03 Mar 2011 21:01:58 GMT

Thanks for the suggestion.
I tried that and I got the following error in the tMap > Type mismatch: cannot convert from String to Date
Both fields are date data types so I'm not sure why it's thinking its a String. Is the output of the function a String?
The the thing I don't get also is that when I run the job, I get the original unParasable date error but it still writes to Salesforce. Not sure why that happens either. And like I said in my original post, I am able to write to a date data type field with other objects just fine. It's just this one on the Contact object on Salesforce I am having trouble with.
I will also mention that the unParasable error occurs on the tSalesforceBulkExec component.

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Fri, 04 Mar 2011 02:50:08 GMT

Hello
The return result of the function is a string, however the data type is Date on schema, that's why you get cannot convert from String to Date. Don't use this method to convert a date to string. What's the date pattern defined in your contact object? As you said, the data are inserted into SalesForce even there is unParasable error occurs on the tSalesforceBulkExec, try to change the date pattern in right side to yyyy-mm-dd hh:mm:ss
Best regards
Shong

Re: [resolved] unparseable date in mysql to salesforce

_AnonymousUser — Tue, 06 Sep 2011 07:05:53 GMT

Hi
In Java I am fetching datetime value from mysql and want to insert it in salesforce database.But due to datetime format is different it's not getting inserted properly in salesforce.I can not use simpledateformat also for conversion. I need a help for this.I am sharing code part also.
ResultSet? tradesResultSet = statement.executeQuery("SELECT FROM mt4_trades limit 10"); // Fetch records from MYSQL
while (tradesResultSet.next()) {
java.sql.Timestamp mysqlTimestamp = tradesResultSet.getTimestamp("Open_Time?"); // Get MYSQL time java.util.Date dbSqlDateConverted = new java.util.Date(mysqlTimestamp.getTime()); // conver to JAVA date hm.put("Open_Time?", dbSqlDateConverted); // Put in map
java.util.Date d = (java.util.Date) hm.get("Open_Time?"); // get date
SObj.setField("Open_Time?c",d); // set in Salesforce field.
}

Thanks, Prasad.

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Mon, 09 Mar 2015 12:09:15 GMT

Hi,
I am using date format as "yyyy-MM-dd'T'HH:mm:ss" and i am still getting parsing error on selecting parallel execution.
Could anyone let me know the resolution.
Thanks,
Sanket

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Mon, 09 Mar 2015 12:50:35 GMT

If you share your input string then we can suggest you date Format to parse. but still you can see more formats and example on same link.

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Tue, 10 Mar 2015 03:31:03 GMT

Hi,
I am using TalendDate.getCurrentDate() mapped to a variable as input string and on the right side i am setting the format as "yyyy-MM-dd'T'HH:mm:ss" which fails with parsing error on selecting parallel execution.
But the same gets executed if parallel execution is not selected.
Thanks,
Sanket

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Tue, 10 Mar 2015 08:45:42 GMT

Hi,
I also found out a case where the input is a date column from a table.
The values in the date column are not in sync..i.e few values are with timestamp and few values are without timestamp(just the date part) and hence it throws parsing error on selection of parallel execution.
On de selecting parallel execution the job executes without any error.
Any way by which such cases can be handled using parallel execution ?
Thanks,
Sanket

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Wed, 11 Mar 2015 12:06:05 GMT

Hi Sanket
This error is not caused by parallel execution, but the different date formats in the column, you need to check the length of the string which need to be parsed to a date, and use different date pattern to parse it.
BR
Shong

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Wed, 25 Mar 2015 06:47:12 GMT

Hi,
Thanks for your reply.
Yes, the issue was due to different date formats.
I changed it to single common date format for all the date columns and hence it worked.
Thanks,
Sanket

Re: [resolved] unparseable date in mysql to salesforce

Anonymous — Wed, 25 Mar 2015 07:37:13 GMT

great, thanks for your feedback.
BR
Shong