Help in Reading Tables in Excel Files in diferent Sheets and Possitions to Finally concatenate them.

[Terry_Schmidt]

Hi Community,

My name is Terry, I’m working currently in an idea to read multiples files and I want to know if any of you has pass through a similar situation.

I need to process like 500+ files of excel, every file with different worksheets and every sheet with different formats. So I need to get a specific “Table” or “Data rows” from every file understanding that every file will have this table in a different position in a different worksheet.

So my idea so far is to create a loop that will process every file and in every file will process every worksheet, in every worksheet will process every row until it finds the rows that match with the headers of my data that will be some kind of “standard”.

When I recognize the headers after that I plan to create a condition that reads the subsequent rows until it find a blank row and with that break the cycle.

After getting the “Header+rows” I think to use text processing nodes in order to recognize de headers names and standardize them, to finally concatenate the result of every exercise. (Excel File)

This is the first Idea I thought but before going in something like that I would like to know if you know how to do this easier.

Thanks,
Terry Schmidt

[matu3ba]

Do you have the delimiter information of the files beforehand (so that you know which delimiter for each file to use) ?
I would suggest you to use a simple regex for changing the format with a program of your choice before using knime.
I tried to read arbitrary formatted files within the loops module and it failed, but in general that is a bad approach.

When the format is the same you can use this
https://www.knime.com/nodeguide/control-structures/loops/example-for-reading-a-list-of-files
and combine it by looping through the rows appending it to your final file.