Excel to XML with split columns

[frameexpert]

I have a bunch of data like this that I need to convert to XML:

The problem is that ColumnC can have different numbers of rows within each cell.

My goal is to get something like this:

<?xml version="1.0" encoding="UTF-8"?>
<!-- File created by Easy Data Transform v2_4_0 at 2025_04_15 17_00_47_727 -->
<root>
  <entry>
    <ColumnA>Data 1</ColumnA>
    <ColumnB>Data 2</ColumnB>
    <ColumnC>
      <data type="I" value="6000 FH"/>
      <data type="I" value="18 CAL MO"/>
      <data type="R" value="6000 FH"/>
      <data type="R" value="18 CAL MO"/>
    </ColumnC>
  </entry>
</root>

Any pointers would be appreciated. Thank you.

MyTransform.transform (3.0 KB)

[Admin]

Tricky. I managed to do it. But it’s a bit kludgy, so I am not sure how well it will generalise.

Remove the .csv from the Excel file extension after downloading:

test.xlsx.csv (8.8 KB)
excel-to-xml.transform (6.8 KB)

The major problem is having multiple records called ‘data’ with the same parent. I kludged that using ‘!’.

See: Easy Data Transform

I have made a note to see if I can improve this, by allowing multiple leaf nodes with the same name.

Your other option is to call them data1, data2 etc and then have an extra step to read the XML as plain text and clean that up.

[frameexpert]

Thank you for your efforts Andy. I have to further transform the data with XSLT so I am simply replacing the returns in Column C with a unique delimiter that I can later use to make separate elements. Since the number of lines in that column’s cells is variable, it is easier to do it with XSLT. Thanks again for the transform. I am sure I can use the concepts in the future.

[Admin]

I am hoping to look into XSLT at some point.