Hi @Till_H , this option works only if you choose the “Select sheet with name”, so for sure it won’t work with the other 2 options.
Instead of waiting for your workflow, which would still be good to have, I put together a demo that works, so perhaps you can compare with your workflow if it’s easier.
The sample file has 5 sheets, with simple data (this is not meant to do data manipulation or any operation, but only to demo reading through all the sheets of the file), and I’ve renamed the sheets so that there is no pattern in the names:
Workflow looks like this:
From the first node, I extract the file path and the sheets. Results:
I then loop through each row which will produce the variable
Path using the value from the column
Path, and a variable
Sheet using the value from the column
In the Excel Reader’s Flow Variables, you just use the Path and Sheet variables as follows:
In the Settings tab, you just need to make sure that you are using the “Select sheet with name” option, and that’s it. The name is controlled by the flow variables:
Don’t mind the name from the drop down. This is a bit of an inconsistency from Knime where it greys out/disables the “Read from” drop down and the file field in the Input location when the path is controlled by a flow variable, but does not grey out/disable the drop down of the “Select sheet with name” when the sheet name is controlled by a flow variable. As I said, ignore it, it will be overwritten at runtime.
As an additional test, I’m adding the name of the sheet as a new column called Source inside the loop so we can capture the sheet name at each iteration.
Here’s the final results:
So, it’s working fine as expected.
Here’s my demo workflow if you want to compare with yours (the Excel file is included): Excel read multiple sheets.knwf (23.0 KB)