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
|
Rank: Advanced Member
Joined: 2/27/2009
Posts: 565
|
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 77 time(s).
K101 attached the following image(s):
|
