Hey Guys, I am quite new with Knime but I very much like it
I need to split an excel file which has different information and I need to split this file based on the requester name - meaning each requester name has to have only the orders which they have created.
My file has 40+ users and I need to split the file into 40+ unique excel files based on the name of the requester ( eg. requester X has 100 orders= 1 excel file; requester Y has 40 orders = 1 excel file etc)
What kind of node or information I can use?
Hello Iulia,
After reading in the Excel file with an Excel Reader (XLS) node, you can use a Group Loop Start node to loop through the records with individual requester names in groups. Then in the loop, you can use a Create File Name node to create a file name from a base directory of your choice, the requester name (published as flow variable from the Group Loop Start) as the file name and the extension “.xlsx”. Make sure you connect it to the Group Loop Start via Flow Variable connection first by drawing a red line from the Group Loop Starts top-right to the Create File Name node’s input. Then you connect an Excel Writer (XLS) to the Group Loop Start and connect the Create File Name node’s output to the top-left of the Excel Writer (There is a hidden port there. You can enable it by right-clicking on the node and clicking “Show Flow Variable Ports”). Then in the Excel Writer, click on the button with the “v” next to the Browse button and check “Use Variable”. Also select the variable “filePath” in the combobox next to the checkbox. Then close the dialog and add a Variable Loop End node. Its input has to be connected to the top-right of the Excel Writer, just like we previously connected the Create File Name node, just on the other side. And that should be it. See below screenshot for a reference.
Kind regards,
Alexander
Hey @AlexanderFillbrunn, thanks so much for your super fast help/reply, really appreciated.
I will give it a try to see if I will be able to have this done and let you know.
Again, thanks so much
Hi @AlexanderFillbrunn, I am back and not with good news, unfortunately.
So, I have tried it and I am having problems
In the group loop start I have manual selection, include requester name – nothing else.
In the create file name, in the selected directory – I should put the path there I need to have this file, right? And in the base file name I should put the actual requester name as the name of my future file with extension xlsx?
In the excel writer, if I put the path of my folder where I need to have all files ( each requester excel file) I cannot save unless I give a file name and if I do that when I execute the entire flow, it gives me an error at excel writer as output file C\users\xxxx\xxxx\xxxx\desktop\open orders files\alla dan.xlsx exists and must be overwritten due to user settings. And if I check the box of overwrite existing file , the outcome after executing the flow is not correct.
In the variable loop end I have manual selection, include filepath, groupIdentifier, requester name, currentIteration and knime.workplace.
But when I execute the flow, I only have one excel file in my folder and with only 2 lines instead of 40 for the first requester.
Looking forward for your guidance. Thanks in advance.
Hi Iulia,
You need to make use of the flow variables. See here for an introduction: https://www.knime.com/knime-introductory-course/chapter7/section1/creation-and-usage-of-flow-variables.
In the Create File Name node, you need to use the flow variable “requester” for the base file name (there is this small button with the “v” that you have to click, then check “use flow variable” and select the one with the name of your requester column). The same you have to do with the Excel Writer, but here you need to set the flow variable for the output path to the one created by the Create File Name node, i.e. “filePath”.
Kind regards,
Alexander
! This is amazing ! Thanks so much