How to read data from different tabs/sheet in an excel workbook

[aswanidasa]

Hi,
I have two tables: Data and Template. The Data table contains multiple tabs, and the Template table contains mapping information. I want to rename all the column names in my Data tables according to the mapping in the Template table. I created a workflow for this, but the loop is not working. Can anyone suggest what the issue might be or provide an alternative solution?

[Fabien_Couprie]

suggestion : If the table row to variable loop Start is the name of your excel sheets, why don’t you you use it to transmit these names to an excel writer after your column renamer, ending with a simple Loop end (and not column append) ?

[aswanidasa]

Actually, I want the final result in a single sheet.

I have a reference Excel file with a fixed format that shouldn’t change. The data to fill it comes from a “DATA” table, which is spread across different tabs and has different column names from the reference.

So I started by mapping the column names, then I merge everything into one table to match the reference structure.

I’m using flow variables to pass the file path and sheet name to the Excel Reader, but I’m getting this error:
“Multiple occurrences of lookup key” — even though the values (like sheet names) are unique.

[takbb]

Hi @aswanidasa , welcome to the KNIME forum.

With any question such as yours it is quite difficult sometimes to guess at what the issue might be because it will often come down to configuration, and you haven’t shared with us how you have configured the nodes (specifically the Excel Reader).

I am assuming that you have correctly configured the Excel Reader to use the passed file path and the sheet name.

However, a common mistake (often overlooked) is that the Excel Reader must also be configured to read the sheet “by name”". It is an easy mistake to make that it is still set to read the data from the “first with data” which is the default on the File and Sheet tab.

If you haven’t selected “by name”, it will always be reading the same sheet within the loop, regardless of whether you have passed across the sheet name.

Can you confirm your configuration settings?

finally, I agree with @Fabien_Couprie that the Loop End Column Append is unlikely to be what you want, unless you actually want separate columns generated for each of the sheets. Without seeing your actual data and expected results, it is difficult for us to be definitive in our advice, but my thoughts are that the standard Loop End is more likely the way to go, so each iteration generates additional rows rather than additional columns.

[aswanidasa]

Got it. Thank you for your response .