Search code examples
apacheetlapache-nifi

Update the values in a field inside the CSV using NiFi

I want to achieve a simple use case to update the multiple string/Text values to an Integer value in a field inside a CSV using NiFi. For Example, my CSV file looks like:

Input CSV File:

Field_1, Field_2

America, Apple

America, Apple

India, Grapes

China Town, Orange

Australian Nation, Peach

India, Grapes

China Town, Orange

So I want the output CSV to be like:

Input CSV File:

Field_1, Field_2

1, Apple

1, Apple

4, Grapes

3, Orange

2, Peach

4, Grapes

3, Orange

I want all the America in Field_1 to be updated to 1 and Australian Nation to 2 and China Town to 3 and so on...I was able to update just one value using UpdateRecord processor as using the replacement Value Strategy as "Literal replace". Please see the attached picture below:

UpdateRecord Processor

But I am not able to update the remaining values like China, India etc in Field_1. I know that I can replicate multiple UpdateRecord processors to achieve this But I want to achieve this use case with one processor only. How can I do this using one processor? If yes, what configuration to set in NiFi to achieve this? Thank you in advance. Any help is much appreciated.

Solution

  • Use QueryRecord processor (using Sql like case when statement)

    (or)

    LookupRecord processor by defining SimpleKeyValueLookupService

    Refer to this and this links for similar questions.