How to combine Excel-Files

KNIME Analytics Platform
1

Good Morning @all,

I would like to combine some Excel-Files. My wish is that the information in the files will be copied in one file. The sequence of the column are equal in the files so that it needs only to be copied one below the other.

Do you know which node I can use for this action?

Thank you in advance.
Daniela

2

You might take a look at this thread

1 Like
3

Hi @daniela02,

Your task needs a little work around since only CSV Writer can append data to existing file.

35
352072×774 71.4 KB

Please also find attached the workflow.

AppendXLSFiles.knwf (1.7 MB)

Best,
Anna

1 Like
4

Hi @daniela02,
you can actually skip the CSV Writer and CSV Reader in @amartin solution, if you put a Loop End node after the Excel Reader (XLS) node, it will collect all the rows from the loop iterations, then you can just write them out using the Excel Writer (XLS) node.

best,
Gabriel

3 Likes
5

Thank´s a lot.

But a the end I only receive a file with the File Paths and not with the data within the files.

What did I wrong? :see_no_evil: :weary:

6

Hi @daniela02,

Please find attached the updated workflow, which includes now the solution suggested by @gab1one.

Best,
Anna

AppendXLSFiles.knwf (1.7 MB)

7

After my vacation I tried it again.

It works. Thank´s a lot for your great Support!

2 Likes
8

Do you know if after i collected all the data in one file can i make the file of where the data came from as part of the new data?

9

You could use e.g. the Constant Value Column in the loop, take a look at this example I build:

best,
Gabriel

10

Hi gab1one,

thanks i applied your created workflow and it did work for me. All the best and thanks for sharing your work.

11

Hi, i’m just wondering after my end loop node i should only have 528 row for data. However, when i add an excel writer, the data it provides me is 5 times the total collected data. Is there a solution for this one?

12

Hi @GanymedeAqua,

let me guess, you are reading in 5 files?
Where did you write the data to? If you write it into the same directory you are reading from then you will read the same data again if you run the loop a second time.

You can use the node monitor to investigate the state of the data as it progresses through the workflow, this should allow you to locate where the data duplication occurs. It gives you a quick overview of the output data of the selected node:

image
image1381×648 64.3 KB

To investigate a loop execution, you can run in step wise as described here: KNIME Flow Control Guide

2 Likes
13

Thanks, i checked and it happens on Loop End. Although the folder containing the files being read and the destination of the file output is different. Is there anything i should change in the configuration of the Loop end?

14

Hi @GanymedeAqua

Have you made sure that the Excel Reader is not configured to read all files in the source folder?
This would explain this issue.

best,
Gabriel

15

I tried adding a file name filter because all files have the same words in them they just have numerical identifier, but when i run it, it shows no files in the folder for that one even when i ticked wildcard. Also the other filter i provided is to read only xlsx files.

16

Hi Gabriel,

Thanks, was able to investigate that the node where multiplication of data occurs in Loop End. Is there a reason for that? i tries removing the loop end and the result in my output is correct which is weird.