I have two workflows which i created. The both output are saved in my local system (2 separate folders). Each folder comprises of 300 files.
My challenge is that I want to append one file in folder 1 with the another file in folder 2.
Note: Both files belongs to the same UserID. The difference is that they have different information. I also want to use loops to check in folder 1 and folder 2. If the both folder has the same userID. Then it should consolidate both files into one excel as sheet 1 and sheet 2.
Please i require an urgent help in order to save my job
Do you want to append the file (join sideways) or concatenate ( add more rows under)? Both can be done with either the Joiner node or the Concatenate node. Depending on how your userID is stored you can confirm that it matches before passing along both the names of the files to join using string manipulation or column expressions to compare the two strings before consolidating
Hope this helps, if I misunderstood or you have any extra details let me know and i can try again
I have 300 files both in folder 1 and folder 2.
The files are named according to userID.
Each files in folder 1 have almost 20 columns and over 200 rows.
Each files in folder 2 have almost 40 columns and over 400 rows.
Challenge:
I want to create a workflow using loops, that would check the filenames(by either userid) in both folders 1 and 2.
If the same naming condition is met. I mean, if they have same filenames. Therefore, it should append file one in folder 1 as sheet 1 and file two in folder 2 all into one excel file.
For that I would suggest using two list files/folders nodes to read all the names in both folders, then compare strings before loading into an excel reader to be printed into an excel writer which has multiple input nodes for each sheet. This can be done by having a loop once you have the lists generated and use a flow variable to store the matching ID’s, upon matching them you can have that pass along into the excel reader to print into the excel writer.
Only issue i foresee you running into is the excel writer node needs to be within the loop for this to execute as i understand it, you could maybe have it be off on a side branch so that it executes each time but you continue to have data passed along to keep the loop running.
Hope this was helpful, if i can try to answer any other questions feel free to let me know!
P
You coul use list files/folder node to get all of the file paths from both folders together into one table in KNIME then group loop start based on file name (userid) then read the files and concatenate them together.
br
Can you please help me with the procedures. I still can’t understand your solution.
My earlier solutions were as follows
I used two "List files/folders. Connected both folders to each "List files/folders.
Thereafter, i used two string manipulation (using the expression - regexReplace…)connected below
Thereafter, I used two Table row to variable loop start …connected it below
Thereafter, used two excel reader…connected it below
Thereafter, used two excel writer…connected it below
Finally, used two variable end.
However, i connected the first variable end variable loop end to the second “list files/folders”
Can you please help me with the procedures. I still can’t understand your solution
Do you mean i use a string manipulation??
Can you please help me with the procedures. I still can’t understand your solution
Here is my earlier solutions were as follows;
I used two "List files/folders. Connected both folders to each "List files/folders.
Thereafter, i used two string manipulation (using the expression - regexReplace…)
Thereafter, I used two Table row to variable loop start connected it below
Thereafter, used two excel reader
Thereafter, used two excel writer
Finally, used two variable end.
However, i connected the first variable end variable loop end to the second “list files/folders”
Could you take a screenshot of the layout of the workflow you’re working on? It could help clear up some confusion
Is the issue now that the loop isnt closing properly?
In my idea i had one single excel writer that i would then write file1 into sheet1 and file2 into sheet 2 but all within the same excel file. If you wanted them in the same sheet then you could concatenate before loading into the excel writer too
Okay youre very close to what I am thinking of as the solution
Everything looks right and if its executing properly thats great. I would rearrange it to have a single shared excel writer between the two groups of nodes and have it have two inputs which would print into respective sheets. In the excel writer node you can arrange this for multiple inputs to be steered towards specific sheets.
Im just not sure if it will allow the excel file to be written within two different loops, you’d also need a string of nodes to automatically name the files if it is able to handle everything
Thanks for the solution.
I tried writing into "Base folder for the both excel writers but it results in an error.
Can you guide me from the “Table row to variable loop start”
Thanks
@Bewaji2 you can of course concatenate the two files and bring them into one. Your initial description suggested otherwise for me which is why it might be best to procure an example that represents your full challenge. Maybe you can adapt the one provided accordingly.
@Bewaji2 I always encourage planning since often only having a vague idea what to do makes things more complicated.
In your case I would encourage you to move to the practical side and check the example and see if it already does what you want or you can adapt it, or maybe come up with your set of sample files to explain what it is you want to do.
To make it add to a separate sheet is actually really simple. You can increase the number of input ports on the excel writer and by doing so you can route each input to a different sheet which you can name within the node.
If youre able to get the output you want and its just fitting into a single excel file is the last problem, this should deal with that
)
