Merging multiples excel files with different columns

Hello,

I’am dealing with an issue about the integration of multiples files.

I want to combine mutiple excel files in one table. But the header columns are not the same for each files.

For example i can have

Id Name Country Sales
50 John US 41
40 Paul UK 20
30 Will Japan 40
14 Bob Espagne 30
User City Age Sales
1239 Madrid 40 360
1489 London 16 478
1360 NY 30 1414
1470 Tokyo 20 3690
Software Price Year Sales
A 260 2008 230
B 148 2014 148
C 130 2019 36
D 169 2011 23

I know there is a joiner node, but i don’t know how to do that for all my files.

Can you explain me ?

Thank a lot.

Please, provide desirable result table to be clear on what you try to achieve.

I want this result,

Id Name Country Sales User City Age Software Price Year
50 John US 41
40 Paul UK 20
30 Will Japan 40
14 Bob Espagne 30
360 1239 Madrid 40
478 1489 London 16
1414 1360 NY 30
3690 1470 Tokyo 20
230 A 260 2008
148 B 148 2014
36 C 130 2019
23 D 169 2011

Is that possible ? :the Joiner node will be perfect but how I can do it with a loop for all my files ?

Hi,

When you want to join tables you need to specify the joining columns. That means when there are rows where the values in those joining columns are the same then the rest of the columns from both tables will be joined together.

Now look at your tables and let me know which records of the three tables should be joined together?
It seems you can only join the first two tables based on “Id” and “User” columns if they refer to the same person.
You have “Sales” column in all three tables but are they all refer to the same thing? The sales by a person and the sales of a product. They are not the same. And if they were the same still they need to be unique or you get several redundant records from both tables joined together.

Here is a case where you can use joiner:
Suppose we have a table of book details. And another table has the information about our authors:

Book table:
Untitled

Author table:
Untitled

Now we want to have all the author information along with book details. We use a joiner and the “Author ID” column in both tables as the joining column. The name of the column could be different but it’s they role which is important and in this case both “Author ID” columns refer to the same person.

Untitled

:blush:

To have this output you can use the Concatenate node.

:blush:

2 Likes

Thank for your reponse.

It’s good idea to use the concatenate node, but how it work with a loop. My purpose is to use a list of excel files, read all of them and then concatenate in a single table or a file.

Hi @Foxgreen

See this wf reading_multiple_excel.knwf (11.2 KB) using a loop for every xls file in your directory. it gives you the table you specified.

gr
Hans

3 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.