Multiple excel sheets to multiple tables

[killYRidols]

Hi all,

I am stuck with a stupid - I think - issue:
I have an Excel file with multiple sheets but different kind of data in each of the sheet. Basically each sheet should be used as a lookup sheet for different joiners.
Exmple
Sheet 1, named Geography, has a list of Cities and States and it is used to look up the State in the main flow
Sheet 2, names Customers has Customer ID and Customer Name and it is used to look up the Customer Name in the main flow
… and so on, with a total of ca 10 lookup sheets. SHeet names never changes

Now, the most obvious think is to have 10 Excel reader nodes in the workflow that point to each specific sheet name with each output feeding the appropriate Joiner.
And I could live with it.
…but what I would like to do is a combination of nodes that reads each excel sheet and put it in a specific table (in the workflow, not written on the disk) that then is used for the Joiners
Something like this illustration:

NB: the objective is not to concatenate the sheets, beacuse each sheet is different in content

Thank you!

[mlauber71]

@killYRidols if indeed you have a stable configuration and the Excel sheets would not change I think I would go for the 10 Excel Readers. You could also configure a meta table and tell this table what sheets to use and what IDs to join and tell the Excel Reader to allow changing formats. You might have to store your file from the main workflow after each iteration and read it back like in this example:

How to update a table in a loop every iteration

@Vincent_liang two things. If I have to use intermediate results and read them back I often just store them in a file that has been created before the loop start. Yes not the most elegant solution but I like it and one might even construct a loop that could start where it left in case of a crash. Concerning your problem. It might be best to create an example you chould share that would present your challenges and also edge cases. Very much data for a case, no data and so on. And define what…

So this is possible to do but if it is only 10 static sheets you are better off doing it separately and see if something goes wrong in-between.

[Daniel_Weikert]

Is it really not possible to concatenate the tables from the excel sheet first, use an identifier column and join the same table per inner join to the main flow?
br

[killYRidols]

Thanks both for the reply.
If there is no dedicated node for that, I’ll keep the 10 different Excel readers in the workflow, considering that the lookup tables will be pretty static, as per mlauber71 suggestion.
My objective was to make the workflow as clear as possibile to non-expert users (ie users even more noob than me!), so that seems to be the easier way rather than using meta-tables or a concatenated table with different data type possible issue.

thanks!
simone

[badger101]

Hi @killYRidols ,
If the total number of nodes in one workflow is overwhelming, and your intention is to make it easily comprehensible to new users, you can always compartmentalize the whole process into different annotated sections, and when possible, combine several nodes together in one metanode to tidy up your work. You can refer here especially under the Document Your Workflow section:

https://docs.knime.com/2021-12/analytics_platform_best_practices_guide/index.html#_use_proper_naming_for_your_workflows_or_groups

Example from my own workflow: