Update the values in a field inside the CSV using NiFi

Question

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

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

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:

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.

@Shu Can I not achieve this using UpdateRecord processor? Also using this link: stackoverflow.com/questions/53876754/… would make me write all the columns present in the CSV. For example, I have 1000+ column and I only want to update Field_1 so How would be the case statement for that? should i do select *, Case statements? I am confused here — Shrads
@Shu or can I achieve this with ReplaceText? If yes how? THanks in advance — Shrads

notNull notNull · Accepted Answer · 2019-01-12T02:06:52

Use QueryRecord processor (using Sql like case when statement)

(or)

LookupRecord processor by defining SimpleKeyValueLookupService

Refer to this and this links for similar questions.

Update the values in a field inside the CSV using NiFi

3 Answers