Hi, I’m trying to clean up some text in an excel file. I can use string manipulation to replace “\r” for any single column in the file. I’m having trouble using column list loop start/end and loop variables to make the manipulations on every column in the file. I’m choosing to replace column, so simply replacing the column name in the serial flow with the $${ScurrentColumnName}$$ in the loop results in a file where every row value is replaced by the column name. I’m missing some basic understanding of how this should work. Any help would be appreciated.
Hi @junkitems and welcome to the Forum
See this workflow from the KNIME Hub Looping over all columns and manipulation of each.
gr Hans
Hello there and thank you. I have examined that flow several times and I’ve determined that nodes used in it are unique suited to work with flow variables in a way the the string manipulation node is not.
serial string manipulation expression (works for any column name):
replace($my column$,"\r","")
loop string manipulation expression (including using the flow variable to override the column replacement):
replace($${ScurrentColumnName}$$,"\r","")
the latter results in a column with each row replaced with the name of the column.
The point is that you have to rename each column to certain constant name ( ANONYMOUS_COLUMN in example) later you point in string manipulation node that constant name of column as name of column to work with - not the $$CurrentColumnName$$ variable) after your work is done reneme it to previous name and its done. Ofcourse all columns have to be string columns. See attached workflow.
ExcelFixer.knwf (18.9 KB)
1 Like
Thank you very much! I see how your example can work, but I’ll admit I’m a bit mystified that it’s done this way. I thought that while looping, the flow variable value is the actual name of the current column, which would be a great way to reference the column you want to operate on. Is there no way to use flow variables for this purpose?
Agree with you. IT would be nice. I dont know anything about such possibilty.
1 Like
I ran into the same problem some time ago and built this tool. You just swap/edit the column expressions node with whatever you want done to the ENTIRE dataset and it will iterate through.
It works great for your stated problem, and is awesome for removing white space in an entire data-set.
3 Likes
Thank you Doctor! Column Expressions is quite a handy extension. Must exterminate my white spaces!
2 Likes
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.