Importing large amounts of flat XML files to excel

Question

I have a bunch of XML files that have a flat hierarchy, but each contain a differing amount of (uniquely named) nodes. Example:

<?xml version="1.0" encoding="UTF-8"?>   
<SomeName>   
  <Node1> 
    DataA 
 </Node1>   
 <Node2> 
    DataB 
 </Node2>   
  <Node3> 
    DataC 
 </Node3>   
  <AnotherNode1> 
    DataD 
 </AnotherNode1> 
  <AnotherNode2> 
    DataE 
 </AnotherNode2> 
  <AnotherNode3> 
    DataF 
 </AnotherNode3> 
 <SingleNode> 
    DataG 
 </SingleNode> 
</SomeName>

I need to import all these files into an excel table with each column bein associated to a specific node.

Example:

Node1      Node2     Node3     AnotherNode1    AnotherNode2     Anothernode3     SingleNode

DataA      DataB     DataC        DataD           DataE            DataF            DataG

Is there some way to batch import these files or convert them first? Thanks!

score 0 · Answer 1 · edited May 23 '17 at 12:19

0

www.smooks.org will turn almost anything into almost anything else. You could even use XSLT to create a CSV file. how-do-i-write-an-xslt-to-transform-xml-to-csv

edited May 23 '17 at 12:19

Community

1
1

answered Jul 16 '10 at 11:41

Chris K

11,996
7
37
65

I actually already tried converting the XMLs into CSVs (ven using the link you posted), but it doesn't help me, because the resulting CSV files only comma-seperate the data and don't give any information about the node names. This is a problem because my XML files have varying amount of nodes. Smooks looks insteresting but a bit too complicated for me right now. Do you know if I can create a master-table (with all node possibilites) in excel and then import the XMLs into that structure? Thanks! – Grinner Jul 22 '10 at 13:35

score 0 · Answer 2 · answered Jul 16 '10 at 12:41

0

Why not use ActiveWorkbook.XmlImport in VBA?

answered Jul 16 '10 at 12:41

Fionnuala

90,370
7
114
152

Importing large amounts of flat XML files to excel

2 Answers2