topic DateFormat conversion in tXMLMap - Document type in Talend Studio

DateFormat conversion in tXMLMap - Document type

Anonymous — Wed, 22 May 2019 13:25:53 GMT

I have a XML file that needs to be converted to CSV. I am using tfileinputXML --> tXMLMap --> tFileoutput

But

When I try to map the date in output it is showing as :

ShipDate

########

I tried to convert the date to string and pass the value as string "TalendDate.formatDate("YYYY/MM/dd",[row2.Shipment:/Shipment/Header/ShipmentHeader/ShipDate]) "

. But I am getting date as

ShipDate

0019/09/07

The input file xml has the date: <ShipDate>2018-03-14</ShipDate>.

Please let me know if there is a work around.

Thanks

Re: DateFormat conversion in tXMLMap - Document type

akumar2301 — Wed, 22 May 2019 13:57:40 GMT

What if you map it as String not as Date ?

Re: DateFormat conversion in tXMLMap - Document type

Anonymous — Wed, 22 May 2019 14:08:48 GMT

It is throwing an error cannot convert from Date to string.

Re: DateFormat conversion in tXMLMap - Document type

Anonymous — Wed, 22 May 2019 14:51:23 GMT

Hi,

I would suggest to parse the data as string from XML file (and making the output also as string) and check the results in a tlogrow. I believe the data is not coming correctly for the column or the format is changing for some records of the input XML.

Once you are absolutely sure that parsing of XML is perfect and there are not format issues, then you can plan for the date conversion. Either you can do it in same step by converting the column to date or you can do the date conversion in a followup step so that you can check the changes happening in each step.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: DateFormat conversion in tXMLMap - Document type

Anonymous — Wed, 22 May 2019 14:54:25 GMT

Hi Nikhil,

I am not able to parse the column as string. It is throwing an error saying cannot convert from date to string.

Thanks

Re: DateFormat conversion in tXMLMap - Document type

Anonymous — Wed, 22 May 2019 15:31:06 GMT

Hi,

Could you please share a sample XML record for testing?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: DateFormat conversion in tXMLMap - Document type

Anonymous — Wed, 22 May 2019 15:32:28 GMT

<?xml version="1.0" encoding="utf-8"?>
<Shipments xmlns="http://www.spscommerce.com/RSX">
<Shipment>
<Header>
<ShipmentHeader>
<TradingPartnerId>SPSALLTESTID</TradingPartnerId>
<ShipmentIdentification>Sh123546-1</ShipmentIdentification>
<ShipDate>2018-03-14</ShipDate>
<TsetPurposeCode>00</TsetPurposeCode>
<ShipNoticeDate>2018-03-12</ShipNoticeDate>
<ShipNoticeTime>14:22:14</ShipNoticeTime>
<ASNStructureCode>0001</ASNStructureCode>
<BillOfLadingNumber>123546879</BillOfLadingNumber>
<CarrierProNumber>123546879</CarrierProNumber>
<AppointmentNumber>12354</AppointmentNumber>
<CurrentScheduledDeliveryDate>2018-03-15</CurrentScheduledDeliveryDate>
<CurrentScheduledDeliveryTime>16:22:14</CurrentScheduledDeliveryTime>
</ShipmentHeader>
<Dates>
<DateTimeQualifier>002</DateTimeQualifier>
<Date>2018-03-15</Date>
<Time>16:22:14</Time>
</Dates>
<References>
<ReferenceQual>AN</ReferenceQual>
<ReferenceID>5987-1</ReferenceID>
<Description>Customer Contract Number "Typically return whatever References are received on PO"</Description>
</References>
<Notes>
<NoteCode>GEN</NoteCode>
<Note>This will only be sent when you have notes</Note>
</Notes>
<Contacts>
<ContactTypeCode>IC</ContactTypeCode>
<ContactName>SPS Commerce Buyer</ContactName>
<PrimaryPhone>612-435-9400</PrimaryPhone>
<PrimaryFax>612-435-9468</PrimaryFax>
<PrimaryEmail>info@spscommerce.com</PrimaryEmail>
</Contacts>
<Address>
<AddressTypeCode>SF</AddressTypeCode>
<LocationCodeQualifier>91</LocationCodeQualifier>
<AddressLocationNumber>123546</AddressLocationNumber>
<AddressName>Warehouse</AddressName>
<Address1>1 Address Way</Address1>
<Address2>Suite A</Address2>
<City>Minneapolis</City>
<State>MN</State>
<PostalCode>55402</PostalCode>
<Country>USA</Country>
</Address>
<Address>
<AddressTypeCode>ST</AddressTypeCode>
<AddressName>SPS Commerce</AddressName>
<Address1>333 S 7th Street</Address1>
<Address2>Suite 1000</Address2>
<City>Minneapolis</City>
<State>MN</State>
<PostalCode>55402</PostalCode>
<Country>USA</Country>
</Address>
<CarrierInformation>
<StatusCode>CL</StatusCode>
<CarrierTransMethodCode>P</CarrierTransMethodCode>
<CarrierAlphaCode>UPSN</CarrierAlphaCode>
<CarrierRouting>UPS Ground</CarrierRouting>
</CarrierInformation>
<QuantityAndWeight>
<PackingMedium>CTN</PackingMedium>
<PackingMaterial>25</PackingMaterial>
<LadingQuantity>1</LadingQuantity>
<WeightQualifier>G</WeightQualifier>
<Weight>5</Weight>
<WeightUOM>LB</WeightUOM>
</QuantityAndWeight>
<FOBRelatedInstruction>
<FOBPayCode>CC</FOBPayCode>
<FOBLocationQualifier>DE</FOBLocationQualifier>
<FOBLocationDescription>FOB Destination</FOBLocationDescription>
</FOBRelatedInstruction>
<QuantityTotals>
<QuantityTotalsQualifier>SQT</QuantityTotalsQualifier>
<Quantity>1</Quantity>
<QuantityUOM>EA</QuantityUOM>
</QuantityTotals>
</Header>
<OrderLevel>
<OrderHeader>
<PurchaseOrderNumber>PO584616-1</PurchaseOrderNumber>
<ReleaseNumber>456</ReleaseNumber>
<PurchaseOrderDate>2016-03-12</PurchaseOrderDate>
<Department>SPS</Department>
<Vendor>VEND6174</Vendor>
<CustomerOrderNumber>98745612</CustomerOrderNumber>
</OrderHeader>
<QuantityAndWeight>
<PackingMedium>CTN</PackingMedium>
<PackingMaterial>25</PackingMaterial>
<LadingQuantity>1</LadingQuantity>
<WeightQualifier>G</WeightQualifier>
<Weight>5</Weight>
<WeightUOM>LB</WeightUOM>
</QuantityAndWeight>
<PackLevel>
<Pack>
<PackLevelType>P</PackLevelType>
<ShippingSerialID>00007774760000006929</ShippingSerialID>
<CarrierPackageID>00007774760000006929</CarrierPackageID>
</Pack>
<PhysicalDetails>
<PackQualifier>OU</PackQualifier>
<PackValue>1</PackValue>
<PackSize>1</PackSize>
<PackUOM>EA</PackUOM>
<PackingMedium>CTN</PackingMedium>
<PackingMaterial>25</PackingMaterial>
</PhysicalDetails>
<ItemLevel>
<ShipmentLine>
<LineSequenceNumber>1</LineSequenceNumber>
<BuyerPartNumber>SPSBuyerItem1</BuyerPartNumber>
<VendorPartNumber>SPSSupplierHD-20</VendorPartNumber>
<ConsumerPackageCode>859563003863</ConsumerPackageCode>
<GTIN>10859563003863</GTIN>
<UPCCaseCode>496099966745</UPCCaseCode>
<ProductID>
<PartNumberQual>MG</PartNumberQual>
<PartNumber>9565452</PartNumber>
</ProductID>
<OrderQty>1</OrderQty>
<OrderQtyUOM>EA</OrderQtyUOM>
<PurchasePrice>20</PurchasePrice>
<ItemStatusCode>IA</ItemStatusCode>
<ShipQty>1</ShipQty>
<ShipQtyUOM>EA</ShipQtyUOM>
<ProductSizeCode>S-800</ProductSizeCode>
<ProductSizeDescription>Small</ProductSizeDescription>
<ProductColorCode>C-999</ProductColorCode>
<ProductColorDescription>Fire Truck Red</ProductColorDescription>
<ProductMaterialDescription>Faux Fabric</ProductMaterialDescription>
<NRFStandardColorAndSize>
<NRFColorCode>533</NRFColorCode>
<NRFSizeCode>10185</NRFSizeCode>
</NRFStandardColorAndSize>
</ShipmentLine>
<PhysicalDetails>
<PackQualifier>IN</PackQualifier>
<PackValue>1</PackValue>
<PackSize>1</PackSize>
<PackUOM>EA</PackUOM>
</PhysicalDetails>
<PriceInformation>
<PriceTypeIDCode>RTL</PriceTypeIDCode>
<UnitPrice>60</UnitPrice>
</PriceInformation>
<ProductOrItemDescription>
<ProductCharacteristicCode>08</ProductCharacteristicCode>
<ProductDescription>Sample Description1</ProductDescription>
</ProductOrItemDescription>
<Dates>
<DateTimeQualifier>037</DateTimeQualifier>
<Date>2018-03-12</Date>
<Time>14:22:14</Time>
</Dates>
<References>
<ReferenceQual>MR</ReferenceQual>
<ReferenceID>MerchandiseTypeCode</ReferenceID>
</References>
<Notes>
<NoteCode>GEN</NoteCode>
<Note>Gift Message: Happy Birthday!</Note>
</Notes>
</ItemLevel>
</PackLevel>
</OrderLevel>
<Summary>
<TotalLineItemNumber>1</TotalLineItemNumber>
</Summary>
</Shipment>
</Shipments>

Re: DateFormat conversion in tXMLMap - Document type

Anonymous — Wed, 22 May 2019 18:38:41 GMT

Hi,

This helped to find the issue.

Please change the date pattern under tree schema editor to your desired pattern and it should resolve your issue. The issue happened because it was in the default format.

If the answer has resolved, please spend a second to mark the topic as resolved 🙂

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: DateFormat conversion in tXMLMap - Document type

Anonymous — Wed, 22 May 2019 18:44:49 GMT

Super.. Thank you so much Nikhil. It has worked.

Thanks Again.