replace parse error with string

[JoPe]

Hi everyone,

i am very new the knime and encountered an error when reading an excel file.
I have a date column and some entries are not valid dates.

Unfortunately, I couldn’t find to a way to implement an error handler to replace the unparsed value with an error. I tried reading it as a string and then format as a date but then I lose the value. I still need to know that there was some value in that cell that couldn’t be parsed.

thank you

[sjporter]

Hey @JoPe,

It sounds like you want to extract dates from a string field into a Local Date (or similar) type, and then force your workflow to fail to execute if there are any data points missing.

I’ve uploaded a workflow to KNIME Hub which demonstrates how to do that:

replace-parse-error-with-string – sjporter

---

First, I made a phony data table using the Table Creator node. Then I used the String to Date&Time node to guess the data format and disabled Fail on error to try and replicate the screenshot you shared.

---

As you can see here, my output has valid dates as well as one missing value.

---

The real trick is to use the Table Validator node to forcefully fail to execute (with a relevant error) when there is a missing date.

---

This way, your data has been successfully parsed but there is also a failing node when some values are missing.

---

Hope this helps!

Cheers,

@sjporter

[sjporter]

It’s possible that I misunderstood your question - if you want to replace the dates with some kind of text indicating an error, one alternate option would be to use the Rule Engine node to create a new Boolean column which contains true or false depending on whether the field has a missing value. You can then use that column to reliably determine if the field was processed correctly or not.

I’ve updated the example workflow to include an example of how to do this.

Cheers,

@sjporter

p.s. welcome to the KNIME Community

[kowisoft]

Hi @JoPe and welcome to the forum,

@sjporter 's solution is already pretty good. The one thing I do not understand from your initial post is that you say

@JoPe

I still need to know that there was some value in that cell that couldn’t be parsed.

From the screenshot, there were only dashes in the cells so I assume that this is not the value you want to keep.

I made an adjusted version of what SJ did here:

76192-replace-parse-error-with-string – kowisoft

Basically the only difference in my version is that I do append the “converted dates” as a new column so I can keep the original ones

If this leaves some questions, you might want to upload an example dataset…

[JoPe]

thank you very much. this is what i was looking for. I still needed to know what values could not be parsed to a date for later reference.

can I also change the rule engine to return true when the incoming value is empty?

[sjporter]

Yes, you can do that in the Rule Engine node by configuring the expression like so:

Cheers,

@sjporter

[JoPe]

AWESOME… thank you very much