Column condition to excel writer

[tazar]

Hi, please help me with a simple task.
Practically I have a bunch of excel files (same format) and I want to re-rewrite the excel files in another folder, if they meet a condition (in a column).

In other words, I have a column with only “false” and “true” strings. The workflow implies looking into multiple Excel files and I want to apply a condition to a column. If there is at least one “false” in the tested column, then I want to write the file in a folder (Excel writer).
If there are all “true” values, then do nothing.

I just can’t find the way of translating the condition fulfilment into Write/Do nothing decision.
Thanks a lot!

[bruno29a]

Hi @tazar and welcome to the Knime Community.

I’m assuming that you are able to do most of what you explained, except for “can’t find the way of translating the condition fulfilment into Write/Do nothing decision”, so I won’t go so much into details about your whole process.

So, what you can do here is just filter in or out what you want do write vs those that you don’t want to write.

Once you have identified which file is what, you can then use a Row Filter or Rule-base Row Filter to choose the files that you want to write.

If it’s not clear, please show us what you have done, and I can then point where to add these nodes.

[tazar]

Hi bruno29a, thanks a lot for your quick answer. Hope that I correctly understand what have you suggested, but dont think solves my task.

I practically have 100 excel files, and want to put some of them (that fulfill a condition) in another folder. The trick is not to look into all the files because it’s time consuming.

Its practically a risk assessment. The files that trigger some risks will be looked into more detailed.

[bruno29a]

Hi @tazar , unfortunately I think you do need to look into each file if the condition depends on the content of the file, and them being excel files, you do need to open them as excel file.

But it should not be a big deal. Here’s an example I put together.

First off, I can read all of the files in 1 Excel Reader. In my example, I have 4 excel files with the same structure. In the Excel Reader, I make sure I choose the option Append path column:

In the settings, I choose Files in folder, and you can add Filter options:

Further down, this is the Preview of my files:

I understand that I only have 4 files, but it should still work with 100 files. If I understood correctly, you don’t really care about all the data, but only 1 column in particular. In my case, that’s the “OK” column. To help make things less bulky, you can also choose to read only that column:

In my case, the column I’m interested in is my column E. In your case, it can be different.

After that, I can do a row filter based on my column OK having the string “false”:

Results:

Of course, you can have files that have more than 1 false row, you can just apply a Duplicate Row Filter to remove the repeated files/path:

Then just transfer these to a new folder using the Transfer Files (Table).

The whole workflow looks like this:

Here’s the workflow (the 4 excel files are included): Column condition to excel writer.knwf (37.1 KB)

[Daniel_Weikert]

If you don’t want to look into the files then how would you identify which file fulfills the condition?

If you got excel files with multiple sheets you would probably need a loop for that
br

[tazar]

That was the reason of the workflow, to automize this process.
I thought that was possible since there is a simple condition which can be easily checked by a Rule Engine or Math Formula node.

[bruno29a]

Hi @tazar , I’m not sure I’m following. The proposed solution is the automation. You do not have to manually open a file one by one and check. The workflow is doing that for you.

[ipazin]

Hello @tazar,

as @bruno29a said the proposed solution is automation although requires reading data into KNIME so I can see how this is something that would be nice to avoid when dealing with lots of (big) files. Especially in your case when you only want to move files and don’t need to do data processing. However there is existing ticket that should ease this process (Internal reference: AP-14410) and your request is noted in it. Upon news someone will update this topic.

Br,
Ivan