How can I read all sheets of an excel file with one upload to the excel reader?

[Frank2323]

I want to join all shets of the excel file with the key ID. Every sheet has different columns but always the column ID.

How can I read all sheets and combine it with one excel reader node?

A example file is attached.

Mappe1.xlsx (10.2 KB)

Thank you.

[pigreco]

You need a loop.
You cannot reach your goal with one single Excel Reader Node.

23479.knwf (13.2 KB)

[Frank2323]

Thank you. But how can I get all columns of every sheet in one sheet and joined by ID?

[pigreco]

Do you want them in a single column or one column for each original sheet?

[andrejz]

Hi,

Try this … at the end you can add a “GrouoBy” node to group by IDAll in one.knwf (36.6 KB)

[Frank2323]

Thanks a lot. It works

[Frank2323]

Hello, how do I get this red connection? And is it also with join instead of concatenate possible? That I join every ID from each sheet to the first sheet?
Thank you.

[andrejz]

On the node from the menu (Right click) select Show Flow Variable Ports.

[ipazin]

Hi there @Frank2323,

in case of join read the first sheet separately (instead of Table Creator on your picture), filter that sheet before loop and replace Concatenate with Joiner node.

Flow variable connection is not necessary here as flow variables are transferred to all downstream nodes regardless of connection type. For more about flow variables check this video:

Br,
Ivan