Reading information from multiple tabs within a single CSV file

[jmancero]

I have information from 5 customers splitted on 5 different tabs within a csv file. I would like to merge all the info in one single table. Is there any way to do it?

[takbb]

Hi @jmancero, can you describe what you mean by “different tabs” within a csv file?

[jmancero]

Sorry, my dataset is on an Excel file with 5 Sheets on it (one for each customer). CSV files are single tab files.

Even using the “Excel file reader” node you have to pick which Sheet to read. Is there any way to merge all 5 sheets without making changes at the Excel file?

[takbb]

Hi @jmancero ,

Thanks for the update. I had a feeling that might be what you meant but thought I’d better ask.

Assuming your sheets are all of the same format, for this you read the sheet names and then put the excel reader in a loop.

See this post for an example…

Reading an Excel file with Multiple Tabs

Hi @kmarrs901 , take a look at this workflow (Ignore the mention of “XML” in the description. I think that is a typo) You can use the sheet name to use for the csv output file name inside the loop (probably using String Manipulation (variable) and String to Path nodes to create the csv file name as a path variable and then attach Csv Writer inside the loop. Hope that points you in the right direction.

And also this example from @mlauber71

I want solution of this problem

@mythxAHIR you might want to take a look at this example and see if you can adapt that. At the core the sheet name will be inserted as a column. You should have all the elements that you would need to combine the elements accoring to your needs: [image]

If, however, the table formats are different on each tab, you’d need to have separate readers for each tab, and wouldn’t use a loop.

Finally, if you need to exclude hidden sheets, see here

Exclude hidden sheets in Read Excel Sheet Names

Hi @lelloba, something to play with while you wait… (yeah, I was bored!) Sorry it can only handle local files rather than all the KNIME path types. ps… if it works I’ll take the credit… but if there are bugs… blame chatGPT… … a second (possibly more friendly) option. Drop the following component directly after a Read Excel Sheet Names node, and it should filter out the hidden sheets automatically… [image]