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

[Anonymous]

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

[Anonymous]

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

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

Shong

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

again

[Anonymous]

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

[Anonymous]

I have sent out.
thanks !
James