How to convert from 1 sheet to multiple sheets in one workbook?

[sophieee]

Hi

I am new to Knime and I am figuring out if it is possible to split a sheet like this:

From a workbook like this (headers are those in gray, no fill are the values):

To two separate sheets in the same workbook like this (headers are those in gray, no fill are the values)

Any recommendations? Would appreciate any help

Thank you so much!

[gab1one]

Hi @sophieee,

you can configure the Excel Reader node to only read a specific range of cells from a sheet. So if you configure several excel reader nodes, you can read the segments you need it into separate KNIME tables.
Then you can apply the usual data manipulation nodes to shape the data into form, creating two KNIME tables that contain the data in the desired format. Then use two Excel Writer nodes to write the data into two sheets of the same workbook, you can do this by selecting the same output file in both nodes, but selecting (append) in the write options and setting a different sheet to write to.

best,
Gabriel

[sophieee]

Hey Gab - thanks for your help! This is my new workflow, but it would require multiple touches to open the same file on different excel readers (since it reads specific segments)

Any tips on how I could simplify it in a way that the user would only need to select a file once?

[gab1one]

Hi @sophieee,

for that you can use a Local File Browser Configuration node (use a File Upload Widget on the server) to create a flow variable with the input file. This node should be wrapped into a component so that it offers easy configuration to users. Connect that flow variable to all your reader nodes, so that they all operate on the same file. While you are at it, you will want to offer a configuration option for the output file as well.

If you have trouble with getting this set up, feel free to upload your workflow and input data here and I can help you get this set up.

best,
Gabriel

[sophieee]

Hi @gab1one -

I am quite unsure on how to use the local file browser configuration node. I have attached a sample input data and my workflow - please help me understand how this solution works

Thank you so much for your help! Really appreciate this

Sample Knime Workflow.knwf (44.2 KB) Sample.xlsx (19.5 KB)

[gab1one]

Hi @sophieee

I tuned up your workflow a bit, using the new Excel reader nodes from KNIME 4.3 as well as the configuration option, you probably want to turn the whole workflow into a component if you want it to make it even easier for your users. For more information on components look here: https://docs.knime.com/2020-12/analytics_platform_components_guide/index.html

Excel reading.knwf (52.4 KB)

Happy KNIMEING,
Gabriel

[gab1one]

BTW If you want to have nice styling on your excel sheets, take a look at the continental xls styling extension: https://www.knime.com/community/continental-nodes-for-knime-xls-formatter
That one allows you to do some really cool things with xls files.

[sophieee]

Hi @gab1one - Thank you so much for your help! Your suggestion works.

Thanks for being awesome!