Concatenate data from multiple xls files

Hi,

It's not really an issue anymore because I found a solution but I have the feeling that he should be much easier or intuitive.

The goal: read multiple excel files and concatenate the data from these files. Some of these xls files have an extra-column information (e.g. most xls have columns A, B and C, some have columns A, B, C and D).
This can be adressed with multiple xls reader nodes and several concatenate nodes.
But since I have a large number of xls files, I would like to use the xls reader in a loop but neither the simple loop end nor the "add column" one are able to concatenate the readed xls due to inconsistency in the number of columns. So how to do it ?

I found a workaround by using a transpose node before and after the loop end node (see snapshot below) but is there a better way to solve this ?

Probably if you create an empty table with the preferred column types for A, B, C and D and append the read table from XLS Reader in each iteration to that table (Use union of columns) it would be more safe regarding the types, even if it is a bit more effort. What do you think? (The Loop End node without column append should be used for this task.)

Thanks for the suggestion !

I think I get the idea. The drawback is that you should make upfront a detailled analysis of the content of the xls files. But this solution sounds reasonnable, thanks !

How do you get the file reader to read the files without specifying a single one in the node?

Nevermind: Set flow variable data source to URL in the file reader node.

I am new with the platform, so I would kindy ask anybody to post the solution in .knar file in order to understand it faster. I need to concatenate over 600 excel files with the same column structure. Any response will be much appreciated.!
Hector.-

Hi Eric, could you please share your solution in a .knar file? thanks in advance…!

Hi @hsrb -

I think something like this would work for you. Just replace the Table Reader node with an Excel Reader. The concatenated results will be collected in the Loop End node.

2019-07-30%2010_03_11-KNIME%20Analytics%20Platform

4 Likes

Hi Hector,

To illustrate the solution suggested by Scott, below is a workflow which should do what you are looking for.
Best Regards,
Eric.

Concat_Excel_Files.knwf (10.8 KB)

5 Likes

Hello Scott. So many thanks for your prompt response. I will give it a try and let you know!!!
Just updating:
It worked excellent…!!!

2 Likes

Hello Eric. Thank you very much for your time and help!!!
Updated: Worked like a charm…!! Sweet…!

2 Likes

A post was split to a new topic: concatenate xls without common columns