IMPORTANT:
this is not a Support Forum! Experienced users might answer from time to time questions posted here. If you need a professional and reliable answer, or if you want to report a bug, please contact Altova Support instead.

Excel to XML help Options · View
sanketgroup
Posted: Sunday, January 3, 2021 5:34:22 AM
Rank: Newbie

Joined: 1/2/2021
Posts: 1
Location: US
Hello,
I want to convert Excel data to XML format as shown below.
My excel file has data something like below.
+----+--------+--------+------+-------+
| ID | Name | ItemSr | Item | Price |
+----+--------+--------+------+-------+
| 1 | Peter | 1 | A | 100 |
| 1 | Peter | 2 | B | 200 |
| 2 | Thomas | 1 | A | 300 |
+----+--------+--------+------+-------+


And I want to convert it to XML like below.

Code:
<SampleMESSAGE
    xmlns:UDF="SampleUDF">
    <Voucher.List>
        <VOUCHER>
            <Name>Peter</Name>
            <Item.LIST>
                <Item>A</Item>
                <Amount>100</Amount>
                <Item>B</Item>
                <Amount>200</Amount>
            </Item.LIST>
        </VOUCHER>
        <VOUCHER>
            <Name>Thomas</Name>
            <Item.LIST>
                <Item>A</Item>
                <Amount>300</Amount>
            </Item.LIST>
        </VOUCHER>
    </Voucher.List>
</SampleMESSAGE>
   
   


Please help.

Thanks
K101
Posted: Monday, January 4, 2021 2:57:55 PM
Rank: Advanced Member

Joined: 2/27/2009
Posts: 438
That's possible to solve by applying several things in MapForce as demonstrated by the attached example and in the screenshot.

First, we need a variable to hold the rows grouped by ID. Since we can't use Excel structures for variables, we create an analogous FLF structure in MapForce's bundled FlexText utility.

Then, we need to use a source-driven connection to drive the creation of the groups of Item/Amount pairs in your output. The source-driven connection is the dotted line you can see in the screenshot. Without this, you'd get the two Items, then the two Amounts.

The item-at() function is used to limit the number of Name elements per group to only one (otherwise there would be two Name elements in the first VOUCHER).

File Attachment(s):
example.zip (10kb) downloaded 64 time(s).

K101 attached the following image(s):
Capture.PNG

Users browsing this topic
guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Use of the Altova User Forum(s) is governed by the Altova Terms of Use.