Can I loop through tabs in an Excel Spreadsheet?

[jontimko]

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?

Thanks

Jon Timko

[gabriel]

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.

[glenerik]

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.

[Iris]

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

[dvkumaraws2019]

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.

[mlauber71]

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

[bug or no bug ?] Excel Reader preconfigured work sheet selection options

@Geo what you could do is force the use of sheet_selection = “NAME” thru a Flow Variable in case of doubt. Maybe not the most elegant way but shout provide an additional layer of security. [image] And indeed under certain circumstances it seems to revert to the default value. Not able to reproduce it yet in a constant way. But might be worth looking into, @laaaarsi. [image]

Loop thru same Excel sheet names from various sources and collect them back –...

[ScottF]

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!