1

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: Excel Import Image

Improve this question
1

2 Answers 2

Reset to default
2

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:

XML Import

Improve this answer
1
  • @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 :)
    – CLockeWork
    May 28, 2014 at 9:23
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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .