How to consolidate data from multiple sheets into one?

Hi,

Im new in using this Knime tool. How do I consolidate the data with multiple Sheets into one one Sheet? Can this be done in Knime? Please help :(

Thanks for trying out KNIME. The KNIME Analytics Platform allows combining data from various sources in many different ways, standard procedures like concatenation or joining or more advanced methods. I would recommend to download KNIME Analytics Platform and check out the Example from the Example Server available within the platform.

Hello,

I’m trying to follow an example related to “Multiple Sheets into a Single Table” but I keep getting an error after Variable loop start. My input worksheet contains 4 different sheets (sheet names: A, B, C & D) My goal is to export the data into one XLS sheet.

How to set Flow variables ?

How to Set a Filename. and XLS_Location parameter is controlled by a variable.

1 Like

Hi there!

what error do you get? You have to set some name and then flow variable value will overwrite it.

Regarding flow variables you have String Input node which creates string flow variable. You should connect this node to Read Excel Sheet Names (XLS). With this node you can create Excel location and then in Excel Reader add this variable on Flow Variables tab or use “V” next to do “Browse…” on your picture.

Br,
Ivan

2 Likes

You should set the Flow variables in the (well) “Flow Variables” section of the Excel Import node. If you just have one Excel file you could use a static input and use the sheet names as variables. The attached example should work and can be modified.

kn_example_excel_consolidate_multiple_sheets.knwf (43.3 KB)

4 Likes

Thank you Ivan & mlauber71. Issue resolved.

2 Likes

Hi there, I am new to KNIME and tried using the workflow in order to consolidate three sheets into one. With the node Loop End, I get the error that “Input table’s structure differs from reference (first interation) table: different column counts 29 vs. 33”
However, the coloum count is the same on all sheets and it’s 35.
Has anyone an idea what I am doing wrong?
Thanks a lot!
BR

Hello @Felicitas,

and welcome to KNIME Community!

Although there are 35 columns in each sheet there are for sure 29 and 33 columns in two iterations Loop End is complaining about. Reason for that might be that Excel Reader doesn’t read all columns. For example if they are hidden or empty. (Check Advanced Settings tab of Excel Reader.)

Loop End has two ways for you to check which columns are read into KNIME in each iteration. One is to execute one iteration at the time using Step Loop Execution option from Loop End context menu which you get when you right click on a node. Another is to check Allow table changing specification option in Loop End node’s configuration and then inspect output table once loop is finished.

Give it a try and if still having issues feel free to ask more questions.

Br,
Ivan

3 Likes