Convert Deserialized XML to Excel or CSV

[a23688t (MR)]

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?

[a23688t (MR)]

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>

[vvaidya (Vinay Vaidya)]

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

[vvaidya (Vinay Vaidya)]

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

[nyusuf (Nowfal AY)]

Hi,

could you send me one example for this scenario?

Thanks

[Uthraa]

Hi
Refer this thread. this would be helpful

Read xml and store data in an excel file

Hi all, starting from an XML file that has the following structure: [image] as you can see, not all the question are always present in every node ( eg question 2 doesn’t appear in node2 ). i have to write this info in an excel file, possibly with this scheme: [image] is it possible to create a project that do this? thanks

Thanks.

[Charles_Norman_US_AS (Charles Norman)]

Hey @vvaidya thanks for your suggestions on this topic. I have a similar use case where I want to use this method to parse any xml file into and excel workbook. The issue I am having is when using the append range function I get a compiler error where you have table saying implicit conversions from object to system data table is not allowed. I’m confused because when I print the type of each table it says it’s a data table so wondering why it is seen as an object? Any help would be greatly appreciated.

[DEEPAK_K.S]

Hi,
I did not understand how you have XML address can u please explain
Thanks

[yvrnsravankumar (Sravan Kumar Yanamandra)]

Thanks a lot Vaidya

[nhkazi (Nahid K)]

Error ERROR Validation Error ‘DataSet’ does not have a public instance method named ‘ReadXML’ matching the parameter types, generic type arguments, and generic type constraints supplied to InvokeMethod ‘Invoke Method’.

Although all seems ok … why am i ending up with this error

[nhkazi (Nahid K)]

i have already passed the file name as a parameter.