Excel Reader Sheet name in loop does not change

[Till_H]

Hello everyone,

I am experiencing a bug when reading various worksheets from an Excel file. The data is read in per sheet depending on its line position and then processed further. For this purpose, I use a loop, which first lists all sheets in the file and then imports the desired sections via an Excel reader. Using rules engines, I then get my flow variables, which I put into the reader. The first iteration works fine, but on the second the
sheet does not change (despite the flow variable changing). Is this something someone has experienced before?

(Some things are blackened due to confidentiality)

[bruno29a]

Hi @Till_H , when you say that the sheet name does not change, are you referring to what you are showing in your 2nd screenshot, that is the configuration window of the Excel Reader? If that is the case, this is how Knime behaves, it will not change the name there, it will simply overwrite it at runtime with the value of the flow variable.

Also, you can compare with your 1st screenshot where it’s at the 2nd iteration, the Sheet value is DE3M30, while the first sheet as you mentioned it in the 2nd screenshot is DE2540.

[elsamuel]

This process works fine for me.
Do you get any error messages?

@Till_H

For this purpose, I use a loop, which first lists all sheets in the file and then imports the desired sections via an Excel reader. Using rules engines, I then get my flow variables, which I put into the reader

I’m having a hard time picturing this workflow. Can you share it? We’d really need to see how the nodes are all configured.

[mlauber71]

@Till_H maybe you can check this example and see if it does work on your machine and maybe you can adapt it to your needs or use it to demonstrate your problem.

This workflow demonstrates how to use the new Excel Reader and path variables...

[Till_H]

This is part of my workflow where the error happens:

Both Excel Readers read in the same file ( the bottem one with correct the values). All is encapsulated in a loop. So the first iteration (DE2540) is read in and the loop continues, but does not read the values for the next sheet (DE3M30) in the next iteration, but the values from the first (DE2540). The metanode (“Formatting for reading in data”) does give out the value DE3M30, which you can see in the picture above. I tried switching the Sheet selection from “Select sheet with name” to the two other possibilities, but with no result.

[ipazin]

Hello @Till_H,

it would be helpful if you can create and share workflow with above described behavior that uses dummy data. Otherwise we can only guess and one guess would be something goes wrong when reading same Excel file at the same time with two Excel Reader nodes.

Br,
Ivan

[bruno29a]

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 Sheet

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)

[Till_H]

Hi @bruno29a , thanks for the detailed solution. My workflow looks like yours ( a bit more complex), but did not work. I scraped everything and rebuild it, now i don’t have any more problems. Not quiet sure what the error was.

[bruno29a]

Hi @Till_H , no problem. It was probably a config setting. I’m glad that your issue is solved.