How to read multiple excel files in one folder?

[bilali]

Hi together,

Thanks for the super workflows, it helped me a lot. Can you help me once more please to solve following further problem: I have one folder with 2 Excel files each Excel file has 5 Tables. Now I would like to read these 10 Tables in 10 seperate Nodes (Excel Reader). With this wf I would like to skip the manual selection of each table…
Is that possible?

Best wishes, Bili

[izaychik63]

Look at
@mlauber71 example here

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

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)

[bilali]

Hello izaychik,

Thanks for the wf, I tried it already, my tables are all different therefore it is not working (Execute failed: Input table’s structure differs from reference (first iteration) table: different column counts…), further i would like to have the results each in a separate node for further processing. Any ideas??

Regards
Bili

[bilali]

The issue with the different columns is no issue, but how to route or safe each table in a separate sheet I cannot find.

[mlauber71]

You could have a look at this post it uses R package to import CSV files with very different structures. You would still have to decide what to do with them later. I think this example would be adapted to your needs with readxl package.

And also it might help if you could provide an example most closely representing your problem (especially possible quirks that a solution must be able to overcome).

Merge csv files with different and same headers

Well well. I set up a workflow that hopefully does what you want. The CSV data really seems to be quite messy with funny column headers and stuff. For the “resultatspreliminaires (15).csv” I had to revert to my trusted readr package from R. I really would recommend investing in some useful data storage concepts - CSV is widespread but also unstable and messy. Why not use Parquet, or SQLite, or ARFF - all supported by KNIME - but that is just me The workflow assumes the first 6 co…

How to force column data type in "Excel Reader (XLS)" node?

that is actually a good idea I built a workflow that uses your idea and does three imports of an Excel file, one all numeric, one all strings and one guess. For the numeric ones when it might actually be a String there will be a huge number of missings. I do a calculation of % missing and I set a variable (75%) if there are 75% or more missing and the guess suggests a String it is a string. Obviously if you have date variables you would have to do something different. Here the …

[bilali]

Hello mlauber,

Thanks for the very quick response. My issue is, I have in one folder 2 excel files (a and b). These excel files contain several tables, 4 and 6 in total 10 tables. Now I want these uploaded automatically, instead of select them everytime time manually. The wf’s in this forum run through all excel files and all tables, but i cannot safe them somehow in seperate nodes. In my case I would like to use the wf but have at the end 10 Nodes as result (a1, a2, a3, a4, b1, b2, b3, b4, b5 ,b6) to process further.

[mlauber71]

OK so you want a workflow that creates import nodes that you then have within KNIME to further use them. Hmm spontaneously I think this is not possible in this way. I do not think KNIME has some meta script language (like SPSS Clementine had in those days). One could think about some freak experiment creating XML files representing nodes - but I would not go there.

If you have different structures of you data I would recommend to somehow loop thru them and store them in (separate) files. If the Excel reader can not do that one option would be to use R or Python scripts that might be more robust in that regard.

But still you would have to decide what to do with the data. Instead of having the data in various excel files you would then have them within KNIME.

Of course if you know what to do with them you could collect them all in one folder and then again loop thru them and do something with them.

If the structure is divers but would not change that much you might be better off just going thru the effort and creating the nodes by hand.

[bilali]

Is there really a very complex way needed? The uploaded wf’s are able to run through the files and have them in one table. Here I could filter and would have a solution, but this one would be not flexible. Thats why I search for a way to use the wf, and then when one table is found safe it in a separate excel reader for example. I tried it with switchs, but did not work…

[mlauber71]

Maybe I still do not understand what you want to do. An example might help explain what your idea is. Of course, you can also save the table you ran thru in separate tables. You might use the name of the file or generate a new one from the number of the iteration.

CSV files from subfolders to xlsx sheets

I created a workflow that demonstrates how such a job could be done. I use two loops. The first one lists all the folders from the import folder, the second one iterates thru all the CSV files found there. The names of the folders and files are extracted and used to name the resulting Excel files. Maybe you could modify that to your needs. [image] [image] kn_example_csv_to_excel.knar (229.3 KB)

How to read multiple excel files in one folder?

I tried this earlier also but it didn’t work, I’ll again explain my task, I’ve some files in my pc(they can be of any format excel or csv) and I need to import them into knime environment at one go instead of using Excel Reader(XLS) node again and again. I’m attaching 3 files here, please help me with the workflow that I can use. For the reference I’m taking example of 3 files but there can be hundred files since I’m very new to KNIME and wants to explore this as much I can.Data1.xlsx (15.3 KB) …

[izaychik63]

It could be better change the mental model from Excel centric to database centric. Load your tables to database and read them from DB to Excel.

[bilali]

Thank you very much for your help!! I try to explain.
I have one folder with 2 excel files (a and b). Each excel file has 3 Spreadsheets (1,2,3). I want to import them in KNIME with one click (instead of selecting and importing 6 times).
The result should be 6 separate imported tables (a1, a2, a3, b1, b2, b3).