Can I loop through tabs in an Excel Spreadsheet?

Is there a way to loop through tabs of an Excel spreadsheet in a workflow? I know the Excel reader allows you to choose the sheet you want to extract data from. But can the sheet name be used as a variable in a workflow to process each sheet? If so, how would it be defined?


Jon Timko

In the current KNIME version you need to know the sheet names before processing them. A simple workflow, would have a Table Creater (with the sheet names), A TableRow to Variable Loop Start node (provides each sheet as a variable), the XLS Reader (connected with the variable port from the loop start), and a Loop End which collects the results. The XLS Reader need to have the variable (which is the original column name) assigned in the dialog tab "Flow Variables" as SHEET_NAME. In next release, we will have a XLS Sheet Name Reader allowing to set up more dynamic workflows.

This doesn't work. XLS reader node set with SHEET_NAME flow variable "Sheet" from XLS Sheet Name reader but xls reader settings does not use flow variable to define which sheet to read. Hence, it loops through and keeps reading the first sheet on every loop.

Sure it does :-)


you first need a read xls sheet names, than a table row to variable than the xls reader and the last node in row is the loop end.

In the xls reader go to the flow variables tab and select the sheetname flovariable for the flowvariable SHEET_NAME

Cheers, Iris

I tried the Excel Reader, and the Sheet_Name variable is not getting parsed. For the total number of excel tabs, only the first sheet is read. so if there are 10 tabs, only the first sheet is read 10 times and not the rest of the sheets at all.

I think this is a major bug in KNIME.

@dvkumaraws2019 you could take a look at this entry (AP-18133):

Hi @dvkumaraws2019 -

This thread is several years old, and things have changed a lot with the Excel Reader since it was originally posted. What you’re describing sounds like a misapplication of a flow variable, but it’s hard to be certain based on the information provided.

If you are still having trouble, could you post your existing workflow in a new thread, so we can try to diagnose and troubleshoot? I will close this thread to keep the forum tidy. Thanks!

1 Like