Excel to XML Conversion

KNIME Analytics Platform
1

Do you know if there is any flow that converts an excel.xlsx file to XML spreadsheet 2003?

2

Hi @Valmir_Silva,
I have used a simple example

Steps to Create XML from .xlsx in KNIME

  1. Input Data:
  1. String Manipulation:
  • Add a String Manipulation node to construct the XML strings for each row. You can create an XML string for each entry in the format required.
  • Use an expression like:
"<Item><Number>" + $A$ + "</Number><Value>" + $B$ + "</Value></Item>"
  • This will generate XML strings for each item.
  1. Concatenate XML Strings:
  • Use a GroupBy node to concatenate all the generated XML strings into a single XML structure.
  • In the GroupBy configuration, select your newly created XML string column and set the aggregation method to “Concatenate”.
  1. Wrap with Root Element:
  • Use another String Manipulation node to wrap the concatenated strings with a root element, like <Items> and </Items>.
  • The expression would look like:

text

"<Items>" + $concatenated_column$ + "</Items>"

  1. Use String to XML node:
    Converts string cells in a column to XML

  2. Export as XML File:

  • Finally, use a XML Writer node to save the output as an .xml file.

2
2854×193 9.68 KB

3

convert .xlsx to .xml file.knwf (83.0 KB)

3

Hello !
@yogesh_nawale, the same result could be obtained in “xml environment” with something like that :
image

For the question of XML spreadsheet 2003, which has his own specifications, the question could be to find the correct xlst (and probably a little complex one) … but I didn’t succeed.
However Excel ‘understand’ xml files : @Valmir_Silva what is the goal of having a XML spreadsheet 2003 ? Export large data or anything else ?

Joel

1 Like
4

Perhaps the best way could be to use Excel or LibreOffice and, if necessary, drive operations with Knime and the bash node (Bash – KNIME Community Hub) and comand lines with excel.exe. Or other scripting nodes (Python, Java etc).

Joel

Edit : I see this post also External Tool – KNIME Community Hub and some about comand line like this Open and Close Excel File using the bash node

5

@Valmir_Silva you could employ the Python node to create such a spreadsheet.

image
image2190×862 255 KB

It will open as an Excel file. Although I agree with @JPollet that this might not be the best way to store data. But you can do it …

image

3 Likes
6

I believe that Python node should be used to pass the script?
I need to transform several Excel xlsx files to XML, since the platform I will input only accepts files in this format.

7

@Valmir_Silva you can also load the files using openpyxl and then convert them into the xml format.

Will there be different sheets? The example in the last post just has one sheet.

1 Like
8

Yes, I need to convert more than one sheet tab.
Thanks for your help, I’ll run the tests!

1 Like
9

@Valmir_Silva you can also just convert any Excel file into the same file as an XML Spreadsheet 2003 file by just giving the path from KNIME. This also could be a loop. The Python code is by ChatGPT and you can let it adapt that, I think.

I adapted the workflow

image
image1258×1108 205 KB

1 Like