How to append multiple sheets to Excel file

[Ana914]

Hello all,

It seems like an easy one but problem is, I don’t know how to use loops yet.
My use case is that, we have 25 Excel files with one sheet that looks the same in them.

I would like to take all that one sheet from the 25 files and put them (append as separate sheet) into one Excel workbook.

Which loop would I use?

Thank you

[bruno29a]

Hi @Ana914 , the choice of the loop would depend on how you want to read the files, so there are a few ways to do this.

If the files are not too big, and have the same structure, you could read all of them at once (the Excel Reader node allows you to read multiple files at once) and then use a Group Loop grouping on the Path and writing to different sheets of a new file. This is probably the fastest way.

Here’s something I put together that looks like this:

I have 5 Excel files that look like this:

After running the workflow, it merges these 5 files into 1 file with 5 sheets:

Detailing the workflow:
For the Excel Reader, just choose the Files in folder option and point to the folder where your 25 files are:

In the Advanced Settings, make sure you check the box Append path column, and give it a name (in my case I called it “Path”). This will append a column to the data to indicate which file the data came from:

The results of the Reader:

After that, we Group Loop on the column Path, so it will read only the lines that have Path ./file_1.xlsx in the first iteration, then only the files that have Path ./file_2.xlsx in the second iteration, etc, until all of them are done.

Inside the Loop, I extract the file name (file_1 for first iteration, file_2 for second iteration, etc, file_5 for the fifth and last iteration) that I will use dynamically as sheet name, that way data coming from file_1.xlsx will be saved into the sheet called file_1, and data from file_2.xlsx will be saved in a new sheet called file_2, etc… This is optional. You can create different names if your file names are too long. You can, for example, use the variable currentIteration which is provided by Knime inside the loop that automatically keeps track of what iteration you are in (first iteration starts with 0), so you can build a sequential sheet name using that variable.

In the Excel Writer, I make sure I am writing the same file but to different sheets (the dynamic names I discussed above). The sheet names can be set dynamically via the Flow Variables:

And that’s pretty much it.

Here’s the workflow (same excel files included): Merge multiple Excel files into one file with multiple sheets.knwf (58.1 KB)

[Ana914]

Hey Bruno,

Thank you so much!!! Very well explained and it works. Thanks for attaching your workflow too. I added a string manipulation node after the GroupBy node to get the first four letters of the sheet names because they’re quite long.

Thanks a lot again!

[bruno29a]

No problem @Ana914 , happy to help.

Nice one to get the first four letters. Just make sure that they’re unique, or you will have different files writing to the same sheet if they are not unique

You can do a pre-validation - do the same manipulation separately (get the first four letters), and check for duplicates.