Format all sheets and files in a folder

[Xushimin88]

Hi everyone,

I would like to set a standard format to all sheets and files in a folder. I successfully set the format for one single file but when I tried to loop through all files, it returned an error. And I do not know how to close the outer loop. Do you have any idea how to do it? Thank you.

[ActionAndi]

Hi,

you can close the outer loop with the “variable loop end” node. Just connect the “xls formatter (apply)” with it.

[Xushimin88]

Many thanks for your hint. I have added the variable loop end at the end. And now I encountered an error with the merge ports. Could you please kindly support? Thank you

[Xushimin88]

Hi @ActionAndi , could you please also support for the merge ports part? Thank you

[ActionAndi]

Hi,

I’m a volunteer with a (work) life outside of the KNIME forum that’s why I cannot answer here everytime.
It seems that the row splitter node creates an empty table on the second output. I’m wondering why you pass the same table to the second input of the recursive loop end node.

[takbb]

Hi @Xushimin88 , I note that as well as just adding the variable loop end, you changed the inner loop which is now a recursive loop, but I’m not sure why.

It’s quite difficult for people to provide support from just looking at screenshots (even though this is well annotated) because even experienced KNIMErs cannot always second guess the intentions, and as @ActionAndi has mentioned most people on the forum are not KNIME team members, but simply community users of KNIME so are giving their “spare” time to assist.

---

Can I go back to your original screenshot. It is very similar to the the example on the hub, and I suspect possibly used that as a starting point:

Format Multiple Excel Sheets – knime

So, rather than write it all myself, I took that and looked to adapt that using your additional loop, plus the variable loop end as rightly suggested by @ActionAndi

So it would go from this (which supports only one file)

to this, which (almost) supports multiple files:

I say almost because when I looked at that, I could see there was one very important missing ingredient: there was nothing that defined the changing output file in the XLS Formatter(apply) node. So it would be repeatedly writing to the same output file, regardless of the input file.

So you would need to include some additional nodes, such as Path to String (Variable) + String Manipulation (Variable) + String to Path (Variable) to provide a different output file path for each iteration

I have a component called Edit Path Variable Filename, that can also help do the same job

In the above configuration, the value in the existing path variable named Path is used to create a new path variable called “pathout”. The current path will be adjusted so that the filename is given an additional suffix of “-fmt” (my abbreviation for formatted) and the output folder will be based on the current input folder, but with the addition of the subfolder “/out”

so if, for example the current xlsx file were:
c:\myfolder\somefile.xlsx
the resultant output file would be:
c:\myfolder\out\somefile-fmt.xlsx

This is then used within XLS Formatter (apply) as the output file:

For reference, this is the previously mentioned workflow from the hub with the above modifications:
Format Multiple Excel Sheets - multiple files.knwf (2.3 MB)