Parallel-process multiple Excel files by sheet name in a single KNIME workflow

[san_98]

Hi

I have multiple Excel files in a folder and want to process them in parallel in a single KNIME workflow. Each file should be processed based on its sheet names not file names: if a file has a specific sheet name, it should follow a particular workflow path. I want to have each file and its matching sheet name run concurrently. Please let me know starting few nodes for this.

[mlauber71]

@san_98 from what I recall we had a similar thread about parallel processes

Parallel Processing of Multiple Excel Files Based on Dynamic Sheet Names from List Files/Folders Node in KNIME

@san_98 In your screenshot I do not see any Parallel Chunks and no database access, but if you are fine we are fine For the fun of it I expanded on @MartinDDDD workflow and constructed a parallel access with a database node calling sub-workflow(s) that would then use this connection. So far it seems to work although any benefits in speed might depend heavily on the actual database(-connetion) supporting parallel access. [image] The callee workflow does look like this: [image…

Maybe you can combine it with these information

Create individual excel files from multiple worksheets

@lazaronewhall17 yes this can be done. With such tasks you can combine several nodes into a system to handle your files. [image] You read the Excel Sheet names and feed them into a Table Row to Variable Loop start to iterate over them (you can expand this obviously to several files). You create Path variables for a new sub-folder: [image] Using the name of the “Sheet” as Folder name from the Flow Variable (you can use other things too, or you can just skip this part): [image] You then c…

[san_98]

Hi @mlauber71 ,

Thanks for the quick response.

The issue is that using the “Read Excel Sheet Name” function on multiple large files takes over 15 minutes just to retrieve the sheet names. Is there a way to add a condition node that checks the sheet name, and if it matches a specified name, proceeds to read only that specific sheet?

[mlauber71]

@san_98 this sounds strange. Have you tried listing all Excel Files and all sheet names like this:

and then you can employ Row Filters to the sheet names.

Next option would be to employ Python Script:

Excel - Unzip and List all Excel Files and Sheets in a Folder – mlauber71

[san_98]

Hi @mlauber71 ,

I’ve attempted the same approach and to list all files. However, the challenge is that with a large number of high-volume files, the process becomes time-consuming. We’re working on a bulk data upload and aim to complete the process quickly. I’ve attached a workflow image for reference. Could you assist with optimizing this?

[mlauber71]

@san_98 we had a similar discussion before. Not sure if this is a bot thing? My suggestion: you try to plan out what it is you want to do - which data do you want to transfer where? For transfer zipping might be and option, also to do this in chunks, maybe.

Parallel Processing of Multiple Excel Files Based on Dynamic Sheet Names from List Files/Folders Node in KNIME

I used the List Files/Folders node to retrieve all the files available in the folder, regardless of the file name. Then, I used the sheet name to read all the available sheets in the Excel files. In my case, I will have more than three sheets in the Excel file. After retrieving the sheets, I applied the Rule Engine node to split the workflow based on conditions. If the sheet names are the same, the workflow runs in a loop to process the sheets. If the sheet names are different, the workflow ru…

[san_98]

Hello @mlauber71 ,

I have multiple Excel files with large datasets, each containing a maximum of 2 sheets. I want to read all these files and then read the sheet names within each Excel file. The data from each sheet will be transferred to a designated SQL Server table, with a different table assigned to each sheet. For instance, if there are three Excel files in the folder, the data from the first Excel file will be loaded into Table1, the second file’s data into Table2, and the third file’s data into Table3. This process should run in parallel for efficiency.