Convert Deserialized XML to Excel or CSV

excel
activities
xml

#1

I am trying to take an XML file and deserialize it but then to convert the output into either a CSV or Excel file. I have figured out how to deserialize the XML and write the data I want into the writelin output but I’m not sure of the best/most efficient way to then convert the output to a data table of CSV/Excel.
Bascially I have XML structure like this:

Each line has specific structured data elements as well. I have written the sequence to deserialize XML and can get the output (example below) on the writeline function:

12/07/2017 10:13:33 UiPath execution started
12/07/2017 10:13:34 InvoiceNumber:TEST1
12/07/2017 10:13:34 LINE_NUM:1
12/07/2017 10:13:34 AMOUNT:100
12/07/2017 10:13:34 STCOUNTRY:US
12/07/2017 10:13:34 STCITY:LEXINGTON
12/07/2017 10:13:34 STPOSTCODE:40588
12/07/2017 10:13:34 STSTATE:KY
12/07/2017 10:13:34 InvoiceNumber:TEST1
12/07/2017 10:13:34 LINE_NUM:2
12/07/2017 10:13:34 AMOUNT:200
12/07/2017 10:13:34 STCOUNTRY:US
12/07/2017 10:13:34 STSTATE:IL
12/07/2017 10:13:34 STCITY:CHICAGO
12/07/2017 10:13:34 STPOSTCODE:60657
12/07/2017 10:13:35 UiPath execution ended in: 00:00:01

I want to print a single excel row for all of the line level data elements for each invoice on each iteration of this sequence. Any suggestions on best way to proceed from the deserialized XML to a CSV or Excel file?


#2

here is example of the XML that i created sequence to deserialize. I just am not sure on best way to now put these outputs into a data table.


<INVOICE_NUMBER>TEST1</INVOICE_NUMBER>

<GROSS_AMOUNT>100</GROSS_AMOUNT>
<LINE_NUMBER>1</LINE_NUMBER>
<SHIP_TO>
US
KY
LEXINGTON
40588
</SHIP_TO>


<LINE_NUMBER>2</LINE_NUMBER>
<GROSS_AMOUNT>200</GROSS_AMOUNT>
<SHIP_TO>
US
IL
CHICAGO
60657
</SHIP_TO>



#3

Try this way, looks like you might have to modify your xml structure a bit.

image

image


#4

If you are going with above solution and if your dataset is returning multiple tables (ds.Tables.Count). Do below way to write to Excel

image