How to Concatenate/Union Excel Files with Different Columns?

KNIME Analytics Platform
1

How to Concatenate/Union Excel Files with Different Columns?

Example:

  • File 1: Name, Address, Gender, Age, Zipcode
  • File 2: Name, Address, Gender, Age, Zipcode, City
  • File 3: Name, Address, Gender, Age, Zipcode
  • File 4: Name, Address, Gender, Age, Zipcode
2

list files/folders
loop
read via Excel reader, set it to load A to Z, using Excel names
trim columns with missing value column filter
first row to column names
loop end, allow varying column specs

3

I @Neerajkumar_86

If you have only four files, you can use four excel readers and connect them all to a concatenate node.
Take a look at

Br.

4

Hi @Neerajkumar_86

The Excel Reader node has the option to read Files in Folder and combine them to one table.

afbeelding

gr. Hans

2 Likes
5

@HansS
someone asking such a simple question will end up with an error following your (incomplete) advise (given that the person didnt even see the literal 2nd option in the whole node).

you should also point out that for this to work, the default settings have to be changed:

image

6

I have 26 Files and column that are similar in same order. only one column is extra in some files which lies in between column like ex:

  • File 1: Name, Address, Gender, Age, Zipcode
  • File 2: Name, Address, Gender, Age, Zipcode, City

Yes the combination of all the excel sheets make sense to be in a single table.

Final combined files should be like below after all 24 file union

  • Final file: Name, Address, Gender, Age, Zipcode, City

I hope i am clear all of your queries.

7

and now? do you expect someone to build the workflow for you?
if you provide a semi-finished workflow and state the issues you are facing, you are more likely to receive further help

8

Hi @Neerajkumar_86.

The @HansS answer is the best for your problem. Inport all excels from the same folder without headers, (untick fail if schema differs on Advanced tab), remove extra header lines and finaly insert the headers.

What @fe145f9fb2a1f6b wanted to say is for you to read the help guidance at

Please poste your best efford workflow with some data, to get better help from the comunity.

Best regards
Hugo

3 Likes