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

One Star

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

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

Accepted Solutions
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Community Manager

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

Hi James
Try this date pattern for the datetime format like "2013-06-28T20:01:30"
"yyyy-MM-dd'T'HH:mm:ss"

Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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
One Star

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

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

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

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

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

again
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

I have sent out.
thanks !
James
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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

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

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