Update the values in a field inside the CSV using NiFi

user_10157833 0
Viewed 2854

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.

apache-nifi etl apache
3 Answers

The ReplaceTextWithMapping processor can perform this operation. It reads a "lookup" file which contains the value mappings and performs a line-by-line replacement. Here is a related answer which has example configuration values. It's not "record-oriented", but should work for the scenario you're describing.

user_704650

Use QueryRecord processor (using Sql like case when statement)

(or)

LookupRecord processor by defining SimpleKeyValueLookupService

Refer to this and this links for similar questions.

user_76326950

On the field definition do another replace.

${field.value:replace("America","1"):replace("India","4"):replace("Australia","2")}

etc.

That worked for me

user_163312270
Related