Import XML to Excel

Help
,
1

Hi,

I’m searching a method to import the data from a XML file to an Excel file (or DataTable), I try first to read the XML as a text and deserialize the XML string, and for each node print the name of the node with a message box but my problem is when the node is a parent it print all the data in the message box and i cant know if the current node has child. Do you know if there is solution or an easy way to extract properly the data of an XML file to an Excel file (or DataTable) like we open an XML with Microsoft Excel, sorry about my bad English. Thanks in advance.

Display of a node with child :

screen_node_with_child
screen_node_with_child638×898 65.4 KB

2

Hey @hmartin

It is easy to use with Dataset because their is already an extension method available to ReadXml.

Please see attached Sample for you:
XML_2_DT.zip (3.7 KB)

Regards…!!
Aksh

3 Likes
3

Thanks for your answer, help me a lot !

4

@aksh1yadav Hi, I got a problem with the result take a look too my XML file and the result.

Thanks in advance.

excel_result

and my xml is :
all_fields_5970607f6860c7.48878590.xml (8.6 KB)

5

i think its because the rest of the data is on the rest of the dataset like datatset.table(1,…2,…3), but i don know how to put this without overwritten on th precedent data in the excel file

7

ReadXml if the XML is in a single-table format.

It may not come in in the format you expect, so you may need to explore the structure of the dataset a bit, but it works just fine.

As a general rule when loading data from an XML file into a Datatable, I’d read it into a DataSet first and be sure it does not create more than one table. Any nesting int he XML usually results in multiple tables in a DataSet.

This particular file, however, looks like it would import into a single table just fine.
Well in that case …we have to use Looping for mass coverage of cases. will look into this when will get some free time Today. :slight_smile:

8

Hi @Iris_Panabaker @aksh1yadav thanks for your reply, i have successful do what i want but i have a last problem i have basicly a number of “client” its the “borower” in the xml but my problem is when I write in my excel file i cant write in 2 row (for 2 borowers) i will give you a screenshoot to illustrate

Capture
Capture1191×420 22.5 KB

To did that i just a number that get + 1 at each written DataSet to not overwritten but i cant to that with the column beacause its letter

Capture2

9

Hi,
I successfully imported my XML into excel. I’m having the name and ID column, but this ID column only not correctly printing.
Sample ID’s are 100,101,102,…
Some ID’s are correctly printing but remaining ID’s are printing like “09-01-1900”. Here 09 is my ID.Why am I getting like this…? can anyone give a solution.

10

Because Excel is… not great, to put it lightly, for working with variable format of data.
It interpreted your values as dates (similar to how CDate(string) works) and filled what was missing.
AFAIK there is no straightforward solution to this aside of not using Excel for XML’s.

11

Hi @aksh1yadav ,

I have tried all the mentioned methods, but although the xml is quite long and if I open it manually via excel (open file) I can see long row full of text, when I use solution provided above it only shows three cells with text and the rest is somehow lost. Although I tried to loop, it just doesn’t seem to work for me. Can you help please? Could you maybe adjust the attached sequence?

Thank you so much in advance

Data.xml (3.3 KB)
project.json (332 Bytes)
Books.xml (4.4 KB)
Process.xaml (8.8 KB)

12

Thank you Aksh, this really helped me.

13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.