How to read multiple excel files in a folder and multiple sheets in an excel file?

Hi,

I need to read multiple sheets of same format of an excel file and combine them into a single table.

Also I need to read multiple excel files of a folder and combine them.

Thanks

Hi Nagaraja,

you can find this here: https://www.knime.com/nodeguide/data-access/common-type-files/reading-excel-files

Best, Iris

Hi Iris,

I am trying to read/consolidate data from multiple sheets and files in a folder. I have created the same workflow as given in the link shared by you. I was not successful in getting the desired output.

I am not sure where i am missing.

I have attached the workflow and the files for the reference.

It would be very helpful if anyone can assist me on this.

Regards,
Pavan.Test.knwf (16.1 KB)

You could give this a try. Or you might upload the excel files you want to import so we could see what is going on.

kn_example_multiple_excel_sheets.knar (73.6 KB)

1 Like

Hi mlauber71,
I am unable to use the knime workflow shared by you due to version conflict. I am uploading the files for your reference. Could you please look into my workflow and let me know where i am missing.

test1.xlsx (19.0 KB)
test2.xlsx (15.3 KB)

Regards,
Pavan.

All three of your sheets have different structures. Question is what do you want to do. If you want to consolidate them into one database you might have to come up with a rule which columns you want to keep and implement that, or you might have to have a rule which Sheets you want to consolidate.

Hi mlauber71,

my requirement is to consolidate all the data in all the sheets and files irrespective of different structures and then take only required columns.

As per my understanding that might be the easy thing to work on. Please let me know if you have better solution for this.

Regards,
Pavan.

Yes that is also possible. You can just tell the loop end to accept all kinds of Columns and data. You will then have to sort them out later. I have added a function where you store the source of you data since that might play a role.

The whole structure then is somewhat messy.

kn_example_multiple_excel_sheets2.knar (106.3 KB)

I have changed the structure of the files to be same, since it is getting messy and complicated. Could someone assist on how to consolidate the data from all the sheets and files.

Attached the files and configuration screen shots for your reference.
test1.xlsx (18.9 KB)
test2.xlsx (15.2 KB)


image
image
image
image
image
image

image
image
image

Regards,
Pavan.

The structure still is not the same and either way you can use the example from before. You either collect all columns and do the restructuring later or you make sure every sheet has the same structure. If you know the columns are the same and just the names do differ you might omit the column names and add them later by reference.

Hi mlauber71,

Yes you are right, the data in the columns are same, but the header names are different.
So i have unchecked the column names box while configuring the excel reader node and it worked.
I just can’t believe that i have missed on this small logic.

Here is the screen shot of the configuration change i have made (highlighted in yellow) for just reference.

Thank you for your support in getting the expected output/result.

Regards,
Pavan.

1 Like