Importing an XML file into excel
Solution 1
I'm not super hot on XML, but it looks like you're having problems with your source data, it's not structured in rows; instead it's structured as a huge collection of columns.
The usual best option would be converting it to a pivot table, but because the data creates duplicate columns a pivot table wouldn't work. You could create helper columns but I can't imagine what would get you the table structure you want without a macro.
So it looks like you'll need to restructure your data so that each item actually exists as a distinct item.
Here's a cut down sample of the code. Note each entry is stored in its own section and things like TypeID, Rate and even the state have been converted to attributes of that item:
<L1>
<L1dataId>07320</L1dataId>
<DateDetail>13-Oct-2013</DateDetail>
<Detail>
<TypeId>1</TypeId>
<RateID>1</RateID>
<State>Current</State>
<onsite>100</onsite>
<net>100</net>
<gross>100</gross>
</Detail>
<Detail>
<TypeId>1</TypeId>
<RateID>1</RateID>
<State>Past</State>
<onsite>100</onsite>
<net>100</net>
<gross>100</gross>
</Detail>
</L1>
When imported to excel the data looks like this:
Solution 2
The answer to this question was to actually alter the XML file before it gets imported to excel. To alter the XML file, you will need to code XSL and convert the XML to reduce the hierarchy.
More details here and the solutions that worked for me :
https://stackoverflow.com/questions/23887293/xslt-for-converting-xml-nodename-into-a-node-value
Related videos on Youtube
Sudhee
Updated on September 18, 2022Comments
-
Sudhee over 1 year
I have a multilevel XML file. When I import the XML into excel, it creates multiple columns for the multilevel data. However, I need the multilevel data as additional rows.
Is there any way I can achieve this ? Thanks a lot for your help.
My XML File:
<L1> <L1dataId>07320</L1dataId> <DateDetail>13-Oct-2013</DateDetail> <TypeDetail> <TypeId>1</TypeId> <Rate1> <Current> <onsite>100</onsite> <net>100</net> <gross>100</gross> </Current> <Past> <onsite>100</onsite> <net>100</net> <gross>100</gross> </Past> </Rate1> <Rate2> <Current> <onsite>2100</onsite> <net>2100</net> <gross>2100</gross> </Current> <Past> <onsite>2100</onsite> <net>2200</net> <gross>1200</gross> </Past> </Rate2> <Rate3> <Current> <onsite>300</onsite> <net>300</net> <gross>300</gross> </Current> <Past> <onsite>400</onsite> <net>400</net> <gross>400</gross> </Past> </Rate3> </TypeDetail> <TypeDetail> <TypeId>2</TypeId> <Rate1> <Current> <onsite>100</onsite> <net>100</net> <gross>100</gross> </Current> <Past> <onsite>100</onsite> <net>100</net> <gross>100</gross> </Past> </Rate1> <Rate2> <Current> <onsite>2100</onsite> <net>2100</net> <gross>2100</gross> </Current> <Past> <onsite>2100</onsite> <net>2200</net> <gross>1200</gross> </Past> </Rate2> <Rate3> <Current> <onsite>300</onsite> <net>300</net> <gross>300</gross> </Current> <Past> <onsite>400</onsite> <net>400</net> <gross>400</gross> </Past> </Rate3> </TypeDetail> </L1>
How Excel converts it and how I need it:
-
Leo Chapiro almost 10 yearsCann't google help? office.microsoft.com/en-gb/excel-help/…
-
-
CLockeWork almost 10 years@Sudhee, no problem. So it looks like the answer comes in two parts, the answer to this question is that you need your data in a different format but that raises a second question: is there an easy way to do that? If my answer was correct for this part of the question I'd greatly appreciate it if you could mark it as such :)