Merge two excel file with multiple sheet into one file

I have two excel file with four sheet each and I want to make one excel file by combining given two excel file and form file of eight sheet .
I tried to solve by referring previous solution but that didn’t help. Please anyone can help me in this problem statement.

Hi @ksurendra and welcome to the Knime Community.

Can you tell us what is the previous solution you are referring to and what you have tried so far?

The general solution is to read each sheet and write them to a different sheet but of the same destination excel file.

For example, let’s say the 2 files are file_1.xls and file_2.xls, and let’s say the combined file is final_file.xls

You will read each sheet from file_1.xls and write to a new sheet in final_file.xls. Similarly with file_2.xls, you read each sheet and write to a new sheet in final_file.xls.

The specifics of the solution will depend on a few things, such as do you want to use new names for the sheets in final_file.xls, or do you want to use the original names of the sheets? And if you wanted to use the same original names, what if there are duplicated sheet names (that is some sheets from file_1.xls have the same names from file_2.xls)?

I put something simple together, and I chose to generate new generic names for the new sheets in case you need this - you can skip that part if you don’t need it.

The workflow looks like this:
image

I purposely used 2 similar processes in the workflow, 1 for each file so that it is not too confusing (the second one is copied from the first one, and simply points to file_2.xlsx instead of file_1.xlsx). This can of course be replaced by a loop (so you’d end up with a nested loop)

file_1.xlsx and file_2.xlsx are similar in that they each have 4 sheets, and there’s only 1 cell in each of the sheets, where file_1.xlsx has 1-4 incrementing in the sheets, and file_2.xlsx has 4-8 incrementing in the sheets.

file_1.xlsx:
image

image

file_2.xlsx:
image

image

Results:





Here’s the workflow:

3 Likes

Thanks @bruno29a for your solution. it almost solve my problem, it has one error that in first four sheet value are same and in last four sheet value are same. Like in first four sheet I got 1 and last four sheet I got 5.

Hi @ksurendra , I’m not sure what you did on your side, but you can clearly see in the screenshots of my results that the values are different - that’s why I showed the first sheet, one in the middle and the last one.

I just re-ran the workflow again to double check. Here’s the generated file by the workflow:
final_file.xlsx (6.3 KB)

And here’s the content of the file:








Each of the new sheets captured the content of the different sheets from the original files.

2 Likes

I think there is some fault from my side and thanks @bruno29a for helping