topic Re: [resolved] how to convert datetime from xml to timestamp ? in Talend Studio

[resolved] how to convert datetime from xml to timestamp ?

Anonymous — Thu, 15 Aug 2013 17:12:41 GMT

Hi,
Since the Talend does not have the timestamp mapping type. It maps the timestamp to date type. But this is not good to process a XML datetime of """yyyy-MM-ddThh:mm:ss" (a "T" inside). The run time error is below error even if I defined the parsing format "yyyy-MM-ddThh:mm:ss"
Exception in component tXMLMap_1_TXMLMAP_OUT
java.lang.RuntimeException: Unparseable date: "2013-06-28T20:01:30"
The "T" is a standard datetime in XML and we have to accept it (data from the customer).
Is there any way to solve this ?
Thanks !
James

Re: [resolved] how to convert datetime from xml to timestamp ?

Anonymous — Sat, 17 Aug 2013 10:22:50 GMT

Hi James
Try this date pattern for the datetime format like "2013-06-28T20:01:30"

"yyyy-MM-dd'T'HH:mm:ss"

Shong

Re: [resolved] how to convert datetime from xml to timestamp ?

Anonymous — Mon, 19 Aug 2013 14:27:20 GMT

Hi Shong,
That is exactly what I have tried. (In my post, I have a type error in "yyyy-MM-ddThh:mm:ss", actually, I used Ctrl-Sapce to chose the "yyyy-MM-dd'T'HH:mm:ss". So it used the right "yyyy-MM-dd'T'HH:mm:ss" format. ) but I kept getting the error.
The scenario is,
In tXMLMap, define a column with "document". Then import the xml schema XSD file into. The xsd defined a datatime field. Then I mapped it to an output and define its Date Pattern to "yyyy-MM-dd'T'HH:mm:ss". Then run it and error came. I used in this way because this is the only place I can import a XSD file. (Please tell me if there are other ways to map an output from a XSD file.)

I run in the Java Debug, I found it always uses the "dd-MM-yyyy" (from ParserUtils.java) instead of my setting "yyyy-MM-dd'T'HH:mm:ss".
Thanks !
James

Re: [resolved] how to convert datetime from xml to timestamp ?

Anonymous — Tue, 20 Aug 2013 02:18:44 GMT

Hi James

I run in the Java Debug, I found it always uses the "dd-MM-yyyy" (from ParserUtils.java) instead of my setting "yyyy-MM-dd'T'HH:mm:ss".

It sounds like a bug, where do the data come from? If there was a bug on tXMLMap, you can parse the data and convert it to a Date before or after tXMLMap component to solve this issue? Some of screenshots of the job will be helpful for me understand the problem.
Shong

Re: [resolved] how to convert datetime from xml to timestamp ?

Anonymous — Tue, 20 Aug 2013 15:33:47 GMT

Hi Shong,
The screenshots are below,
Could you tell me how to covert it before tXMLMap ? Because the xsd is imported from the tXMLMap (tXMLMap is the start point for the xsd process.)
Thanks !
James

Re: [resolved] how to convert datetime from xml to timestamp ?

Anonymous — Tue, 20 Aug 2013 15:38:57 GMT

Sorry, other three image is too large to be loaded. I am trying again.

Re: [resolved] how to convert datetime from xml to timestamp ?

Anonymous — Tue, 20 Aug 2013 16:00:37 GMT

The other two are still too large. I cut them again.

Re: [resolved] how to convert datetime from xml to timestamp ?

Anonymous — Tue, 20 Aug 2013 16:04:55 GMT

again

Re: [resolved] how to convert datetime from xml to timestamp ?

Anonymous — Wed, 21 Aug 2013 03:02:40 GMT

Hi Shong,
The screenshots are below,
Could you tell me how to covert it before tXMLMap ? Because the xsd is imported from the tXMLMap (tXMLMap is the start point for the xsd process.)
Thanks !
James

From your screenshot, I see the data comes from tFileInputXML, but I don't fully understand you on this "tXMLMap is the start point for the xsd process", can you please export the the job and send it to me via email?
Shong

Re: [resolved] how to convert datetime from xml to timestamp ?

Anonymous — Wed, 21 Aug 2013 15:23:42 GMT

I have sent out.
thanks !
James

Re: [resolved] how to convert datetime from xml to timestamp ?

Anonymous — Thu, 22 Aug 2013 03:05:46 GMT

Hi James
Thanks for your detailed information, I think I have found out the issue, you need to open the Tree schema editor, and change the data type of the date column to Date and set its date pattern. Please see my screenshot.
Shong

Re: [resolved] how to convert datetime from xml to timestamp ?

Anonymous — Thu, 22 Aug 2013 13:48:38 GMT

It worked. Thanks a lot !
But I have further questions for that schema. I will write the details in email.
Thanks !
James

Re: [resolved] how to convert datetime from xml to timestamp ?

_AnonymousUser — Mon, 01 Aug 2016 10:08:01 GMT

Hi James
Thanks for your detailed information, I think I have found out the issue, you need to open the Tree schema editor, and change the data type of the date column to Date and set its date pattern. Please see my screenshot.
Shong

Hi Shong / James,
I did changes as mentioned in the screen shot, but still its not working for me.
Can you help me out.
Thanks,
Kyasanur

Re: [resolved] how to convert datetime from xml to timestamp ?

Anonymous — Sat, 16 May 2020 21:28:17 GMT

Hi,

Tried changing the pattern in the tree schema, still it did not work. Kindly help.

Thanks!