Search code examples
apache-nifi

Handle date column with spaces in nifi

I am trying to format the date field which contains spaces in replace text processor from a csv file.

Got the error as it is unable to parse the date column which is spaces for first record. Please let me know how to handle this

Error message: Replace text failed to process session due to Cannot parse attribute value as a date; date format ddMMyyyy; attribute value:

Input csv:

1,        , 123

2,02091997,234

Search value : (.{1}),(.{8}), (.{3})

Replacement value : $1, ${'$2':toDate("ddMMyyyy") :format("yyyy-MM-dd HH:mm:ss.SSS") }, $3

Replacement strategy : Regex Replace

Evaluation mode : Entire Text

Solution

  • You can use the isEmpty and ifElse function from the language expression.

    ex: ${'$2':isEmpty():ifElse('null', '$2':toDate("ddMMyyyy"):format("yyyy-MM-dd HH:mm:ss.SSS")) }

    Here I put 'null', when there is no date, but you can choose the value you want.

    However, if you can, parse your CSV files with a CSVReacordReader which handle that out of the box.