I am trying to split data from a master source by a unique column value (Department) and export this split data into existing files for each department. I have been able to split the data by department but only export this data as separate sheets to the same excel workbook. (see screenshot). I would like to append the data to existing excel files which contain the department name which are all in the same folder - is there a way to do this with some sort of loop on Knime where the department name can be recognized in the file name and the output can be added to the corresponding excel file?
As far as I understand, you have to specify the exact file you would like to export your data to in the excel sheet appender. I would just like to direct Knime to the folder that contains all the department files and have Knime automatically match the split data to the corresponding Department file.
Hi @jkyoung
The start of this workflow Split excel.knwf (304.5 KB) is a data tabel (Data Generator) and the flow creates an xls file for every group (cluster) in the data table (adjust your folder/map in the String Manipulation node). When you run the flow a second time (don’t forget to reset the current date&time, to every file a new sheet is append.
Hope this helps to solve your problem.
gr.
Hans
Hi there @jkyoung,
and welcome to KNIME Community!
Apart from what @HansS offered you can use your existing loop with little modification to achieve what you want.
Something like this:
So using List Files node on folder where your output excel files are and then filtering based on DepartmentID you can control your location in Excel Sheet Appender with created flow variable.
Check this example here:
All files are within workflow directory and workflow relative path are used so you can try it out and modify for your needs.
If any questions feel free to ask.
Br,
Ivan
Yes I think both of these solutions work but when it moves to the excel sheet appender node I get the error:
ERROR Excel Sheet Appender (XLS) 8:22 Execute failed: java.lang.reflect.InvocationTargetException
Do you know what the cause may be / how I can solve this?
Hi @jkyoung ,
This just started happening? Happens for every Excel Sheet Appender? Which one (workflow) are you running? What is the KNIME version you are running?
Br,
Ivan
Hi,
It happens when I choose the output location via the flow variable. If I choose a specific excel file as my output location through the Excel sheet appender, it will work. This error occurred when I replicated both your and Hans’ workflows. I am using Knime 3.7.1. These are the errors I receive:
ERROR Excel Sheet Appender (XLS) 8:22 Execute failed: java.lang.reflect.InvocationTargetException
WARN Excel Sheet Appender (XLS) 8:22 Output file ‘(First Department excel file)’ exists and will be overwritten
Hi there,
If I got it right error happens when configuring and not executing node? Well I’m using 4.0.1. so that might be the issue but if you are only replicating workflow (not copy pasting any nodes!) it shouldn’t matter I guess. Have you tried importing and running my workflow? Don’t think you have to do any configuration…
If possible I recommend to update KNIME to newest version.
Br,
Ivan
Hi Ivan,
The error happens when I am executing the node. Prior to executing I get the error: ARN Excel Sheet Appender (XLS) 2:22 Output file ‘(First Department excel file)’ exists and will be overwritten. Once I execute, I get the java error. However, when I download your workflow it still works on my computer.
Hi there,
Can you share example workflow with some dummy data where this occurs?
Br,
Ivan
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.