Transform XML to CSV

Former Member · ‎05-10-2007

I have transform WDP parent-node into XML form.

And I have a requirement that I need to transform the XML file to CSV too.

I am having problem to transform this 2-level depth node into CSV by writting XSLT function.

Here is the sample of my XML file:


<Output>
	<Invoices_T>
		<Amount_Currency>USD</Amount_Currency>
		<Amount>115.35</Amount>
		<Desc>Claim order Invoice</Desc>
		<Amount_Currency>USD</Amount_Currency>
		<Amount>210.00</Amount>
		<Desc>Spare part Invoice</Desc>
		<Amount_Currency>USD</Amount_Currency>
		<Amount>1210.75</Amount>
		<Desc>Services Invoice</Desc>
	</Invoices_T>
	
	<Pymts_T>
		<Amount_Currency>USD</Amount_Currency>
		<Amount>11.35</Amount>
		<Item_Desc>Payment 1 of 3</Item_Desc>
		<Amount_Currency>USD</Amount_Currency>
		<Amount>110.00</Amount>
		<Item_Desc>Payment 2 of 3</Item_Desc>
		<Amount_Currency>USD</Amount_Currency>
		<Amount>800.75</Amount>
		<Item_Desc>Payment 3 of 3</Item_Desc>
	</Pymts_T>
	
	<Parts_Msgs_T>
		<Status>PO-pending</Status>
		<Part_Nr>13N5511-1</Part_Nr>
		<Message>This part will be available after one month</Message>
		<Timestamp>20070122132530</Timestamp>
		<Status>On Order</Status>
		<Part_Nr>13N5511-2</Part_Nr>
		<Message>This part is currently on order, status will be updated once available</Message>
		<Timestamp>20070122132610</Timestamp>
		<Status>Sent To Warehouse</Status>
		<Part_Nr>13N5511-3</Part_Nr>
		<Message>Order sent to Warehouse</Message>
		<Timestamp>20070122132610</Timestamp>
		<Status>Shipped</Status>
		<Part_Nr>13N5511-4</Part_Nr>
		<Message>This part has been shipped</Message>
		<Timestamp>20070122132700</Timestamp>
	</Parts_Msgs_T>
	
	<Parts_Info_T>
		<Status>Completed</Status>
		<Service>false</Service>
		<Shipped_Part_Desc>Base Assembly</Shipped_Part_Desc>
		<Line_Nr>0010</Line_Nr>
		<Part_Shipped>13N5512-1</Part_Shipped>
		<Part_Ordered>13N5511-1</Part_Ordered>
		<Scrap_Return>Scrap</Scrap_Return>
		<Part_Refid>Baseno2347</Part_Refid>
		<Fail_Code>F571</Fail_Code>
		<Cru_Tier_Value>CRU TIER V</Cru_Tier_Value>
		<Status>Completed</Status>
		<Service>false</Service>
		<Shipped_Part_Desc>Disk 3 read head</Shipped_Part_Desc>
		<Line_Nr>0020</Line_Nr>
		<Part_Shipped>13N5512-1</Part_Shipped>
		<Part_Ordered>13N5511-2</Part_Ordered>
		<Scrap_Return>Scrap</Scrap_Return>
		<Part_Refid>HD546784</Part_Refid>
		<Fail_Code>F573</Fail_Code>
		<Cru_Tier_Value>CRU TIER V</Cru_Tier_Value>
		<Status>Completed</Status>
		<Service>false</Service>
		<Shipped_Part_Desc>Disk 6 read head</Shipped_Part_Desc>
		<Line_Nr>0030</Line_Nr>
		<Part_Shipped>13N5512-1</Part_Shipped>
		<Part_Ordered>13N5511-3</Part_Ordered>
		<Scrap_Return>Return</Scrap_Return>
		<Part_Refid>ReadHead47944</Part_Refid>
		<Fail_Code>F256</Fail_Code>
		<Cru_Tier_Value>CRU TIER V</Cru_Tier_Value>
		<Status>Completed</Status>
		<Service>false</Service>
		<Shipped_Part_Desc>converter circuit board</Shipped_Part_Desc>
		<Line_Nr>0040</Line_Nr>
		<Part_Shipped>13N5512-1</Part_Shipped>
		<Part_Ordered>13N5511-4</Part_Ordered>
		<Scrap_Return>Return</Scrap_Return>
		<Part_Refid>Ckt879564</Part_Refid>
		<Fail_Code>F740</Fail_Code>
		<Cru_Tier_Value>CRU TIER V</Cru_Tier_Value>
	</Parts_Info_T>
	
	<Claim_Fields>
		<Basew_Type>Base</Basew_Type>
		<Incident_Nr>I456789</Incident_Nr>
		<Part_Comments>The Hard drive worked smoothly for first day. Only second day of purchase the drive refused to start, but again started next day and was okay for 3 months.</Part_Comments>
		<Basew_Desc>6 months Base Warranty</Basew_Desc>
		<Brand_Auth>Y</Brand_Auth>
		<Tecnhician_Email>Anthny@emailsrv.com</Tecnhician_Email>
		<Upgradew_Start_Date>2006-12-24</Upgradew_Start_Date>
		<Phone_Nr>919-456-7890</Phone_Nr>
		<Submitter_Name>Dhiraj Bhavsar</Submitter_Name>
		<Endcust_Postalcode>08813</Endcust_Postalcode>
		<Submitter_Phone>848-203-5184</Submitter_Phone>
		<Submitter_Id>DHIRAJB</Submitter_Id>
		<Endcust_City>Trenton</Endcust_City>
		<Pop_Date>2006-06-23</Pop_Date>
		<Rma_Nr>RMA9876543</Rma_Nr>
		<Shipto_City>Durham</Shipto_City>
		<Endcust_Addr3>
		</Endcust_Addr3>
		<Shipto_Addr1>597 Green Street</Shipto_Addr1>
		<Basew_Start_Date>2006-06-23</Basew_Start_Date>
		<Part_Failure_Desc>Everytime during the start, Hard disk is not detected for 2 to 3 minutes and then detected okay.</Part_Failure_Desc>
		<Shipto_Company>Terragear Inc</Shipto_Company>
		<Invoice_Date>2006-06-24</Invoice_Date>
		<Shipto_Addr3>
		</Shipto_Addr3>
		<Pymt_Date>2006-06-24</Pymt_Date>
		<Endcust_Country>USA</Endcust_Country>
		<Claim_Nr>100040</Claim_Nr>
		<Claim_Status>In Process</Claim_Status>
		<Shipto_Postalcode>27709</Shipto_Postalcode>
		<Shipto_Country>USA</Shipto_Country>
		<Endcust_Company>BearingPoint Inc.</Endcust_Company>
		<Serial_Nr>GS9546234754</Serial_Nr>
		<Company_Name>Wal-mart / Durham NC 28213</Company_Name>
		<Upgradew_Type>Upgr</Upgradew_Type>
		<Endcust_Type>Commercial</Endcust_Type>
		<Basew_Authorized>Y</Basew_Authorized>
		<Submitter_Email>DhirajBhavsar@yahoo.com</Submitter_Email>
		<Endcust_State>NJ</Endcust_State>
		<Endcust_Addr1>586 Market Street</Endcust_Addr1>
		<Basew_End_Date>2006-12-23</Basew_End_Date>
		<Bp_Claim_Refid>C7634572</Bp_Claim_Refid>
		<Endcust_Contact_Name>John Smith</Endcust_Contact_Name>
		<Shipto_Contact_Name>Caroline Smith</Shipto_Contact_Name>
		<Shipto_Addr2>Suite xxx</Shipto_Addr2>
		<Service_Site>Trenton, NJ</Service_Site>
		<Tech_Qualified>Y</Tech_Qualified>
		<Endcust_Addr2>Suite xxx</Endcust_Addr2>
		<Shipto_State>NC</Shipto_State>
		<Pymt_Paidto>XXX Inc, XXX Street, Edison, NJ</Pymt_Paidto>
		<Upgradew_Authorized>Y</Upgradew_Authorized>
		<Upgradew_End_Date>2007-12-23</Upgradew_End_Date>
		<Technitian_Name>Anthony Gonz</Technitian_Name>
		<Contact_Date>2006-12-29</Contact_Date>
		<Technitian_Phone>609-111-111</Technitian_Phone>
		<Loc_Id>1000040</Loc_Id>
		<Tecnhician_Id>   100247</Tecnhician_Id>
		<Upgradew_Desc>12 months upgrade warranty</Upgradew_Desc>
		<Product_Desc>Hard drive 80gb assembly</Product_Desc>
		<Service_Dt>2006-12-29</Service_Dt>
		<Prodct_Id>13N112</Prodct_Id>
		<Part_Ship_Type>Fedex Ground</Part_Ship_Type>
	</Claim_Fields>
</Output>

I tried to import the data into Excel and SAve It as CSV, but the display of XML file data in the Excel just don't really make sense to me.

Here is some analysis:

The <Claim_Fields> is cominf from a singleton node, and <Invoices_T>, <Parts_Msgs_T>, and <Parts_Info_T> are coming from non-singleton nodes.

And the parent node for them are <Output>.

I am doing the transformation through this function method:


 public java.lang.String iterateAndDisplay( com.sap.tc.webdynpro.progmodel.context.NodeInfo nodeInfo, com.sap.tc.webdynpro.progmodel.api.IWDNode actualNode )
  {
    //@@begin iterateAndDisplay()

	Iterator iterChild = nodeInfo.iterateChildren();
		Iterator iterAttributes = nodeInfo.iterateAttributes();
		ArrayList arrListAttrib = new ArrayList();
		//storing attribute names in an Arraylist 
		while (iterAttributes.hasNext()) {
			arrListAttrib.add(((AttributeInfo) iterAttributes.next()).getName());
		}

		xmlString.append("<" + nodeInfo.getName() + ">n");
		//if multiple objects present for the node 
		for (int i = 0; i < actualNode.size(); i++) {
			for (int j = 0; j < arrListAttrib.size(); j++) {
				String attrName = (String) arrListAttrib.get(j);
				//append attribute name 
				xmlString.append("<" + attrName + ">");
				//obtain IWDNodeElement object to obtain Attribute object
				//IWDNode childNode = wdContext.getChildNode(node.getPathDescription(), 0);
				IWDNodeElement actualNodeElement = actualNode.getElementAt(i);
				String strAttrValue = actualNodeElement.getAttributeValue(attrName) + "";
				xmlString.append(strAttrValue);
				xmlString.append("</" + attrName + ">n");
				//			}
			}
		}
		while (iterChild.hasNext()) {
			NodeInfo iwdNode = (NodeInfo) iterChild.next();
			iterateAndDisplay(iwdNode,actualNode.getChildNode(iwdNode.getName(), 0));
		}
		xmlString.append("</" +  nodeInfo.getName() + ">n");
		return xmlString.toString();
    //@@end
  }

I personal feel that there is something wrong in the non-singleton xml tag. like:


<Pymts_T>
		<Amount_Currency>USD</Amount_Currency>
		<Amount>11.35</Amount>
		<Item_Desc>Payment 1 of 3</Item_Desc>
		<Amount_Currency>USD</Amount_Currency>
		<Amount>110.00</Amount>
		<Item_Desc>Payment 2 of 3</Item_Desc>
		<Amount_Currency>USD</Amount_Currency>
		<Amount>800.75</Amount>
		<Item_Desc>Payment 3 of 3</Item_Desc>
	</Pymts_T>

Shouldn't this be:


        <Pymts_T>
		<Amount_Currency>USD</Amount_Currency>
		<Amount>11.35</Amount>
		<Item_Desc>Payment 1 of 3</Item_Desc>
	</Pymts_T>

         <Pymts_T>
		<Amount_Currency>USD</Amount_Currency>
		<Amount>110.00</Amount>
		<Item_Desc>Payment 2 of 3</Item_Desc>
	</Pymts_T>

       <Pymts_T>
		<Amount_Currency>USD</Amount_Currency>
		<Amount>800.75</Amount>
		<Item_Desc>Payment 3 of 3</Item_Desc>
	</Pymts_T>

Please advise.

Thanks.

Kent

Former Member · ‎01-26-2011

This message was moderated.

former_member182372 · ‎05-10-2007


for (int i = 0; i < actualNode.size(); i++) {
	xmlString.append("<" + nodeInfo.getName() + ">n");
	IWDNodeElement actualNodeElement = actualNode.getElementAt(i);
	for (int j = 0; j < arrListAttrib.size(); j++) {
		String attrName = (String) arrListAttrib.get(j);
		//append attribute name 
		xmlString.append("<" + attrName + ">");
		//obtain IWDNodeElement object to obtain Attribute object
		//IWDNode childNode = wdContext.getChildNode(node.getPathDescription(), 0);
		
		String strAttrValue = actualNodeElement.getAttributeValue(attrName) + "";
		xmlString.append(strAttrValue);
		xmlString.append("</" + attrName + ">n");
		//			}
	}

	while (iterChild.hasNext()) {
		NodeInfo iwdNode = (NodeInfo) iterChild.next();
		iterateAndDisplay(iwdNode, actualNode.getChildNode(iwdNode.getName(), actualNodeElement.index()));
	}
	
	xmlString.append("</" +  nodeInfo.getName() + ">n");
}

Transform XML to CSV

Accepted Solutions (0)

Answers (2)

Answers (2)

Re: What is Capacity Units for SAP Build Code?

Re: How to do Lease Accounting in Cloud 2402

I cannot cancel an import in SAP Analytics Cloud f...

Error while deleting object in eclipse - object do...

Re: SAP Analytics Cloud Planning Data Action Advan...