Search code examples
pentahokettle

How to filter rows by length in Kettle

I'm using a row filter to filter out columns that are longer than given length. Under filter conditions there are no conditions for checking row length.

So the workaround is to use: 

Field1 REGEXP [^.{0,80}$]
OR
Field1 IS NULL

Field2 REGEXP [^.{0,120}$]
OR
Field2 IS NULL

Length check is a very common requirement. Is there a function/simpler way to do this that I'm missing?

Solution

  • Use Data Validator step:

    Create a new validation for every column you want to check and set "Max string length" for every validation created.

    You can redirect erroneous rows using "Error handling of step" hop: enter image description here

    By default these rows have same structure and values as the input rows, but you can also include additional information, such as the name of the erroneous column or error description.

    Alternatively, you can compute a string length before filtering using calculator step, but it may create a lot of additional columns if you have multiple columns to check.

    And, of course, you can always perform such checks in User Defined Java Class or Modified Java Script Value.