Multiple excel sheets to multiple tables

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.
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!

@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:

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.

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?

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.


1 Like

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:

Example from my own workflow:

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.