I just started with knime and want to search through 500 Excel files which have more or less the same form. I want to go into every excel and copy specific cells into another excel. My solution works okay but I got one big issue I cant resolve.
I have a column which consists of “io” and “nio” values. But due to the excel sheet macro which created the sheet somehow managed to put the io/nio values into Column 15, 16 or 17
But since a macro edited the sheets I have one fixed information:
The io/nio values are always in the last column available in the sheet.
(I cant really do something about it and need to figure it out. The whole task would be searching throug about 5000 excel files).
I tried using a Rule based Row Filter. The moment the Node wants to adress Col17, but the correspondig sheet has no Col17 (e.g. the io/nio stuff is in col15 and thats the last column in the sheet there literally isnt a col17) the program stops and exits.
Is there a way I could adress a filter and tell it: Always use the last column available and take all rows that have io/nio in it?
as @izaychik63 suggested you can extract table dimension (which gives you number of columns) and then build logic to do what you need with last column values. Also you can try Column Expressions node where you can automatically access last column with this following syntax:
column(numberOfColumns()-1)
Column Expressions is based on JavaScript syntax so you can wrap above expression into if/else clause for example to output 0 or 1 in new column that will always have same name or even better. You can just rename last column to always have same name and then use it in a Rule-based Row Filter node
3.6? Wow! When is update planned? Newer version bring cool features and nice improvements Also you are lucky as Column Expressions is available from version 3.6