Add column to csv using predefined file using apache nifi

Question

I get a raw csv file which looks like this

id,name,star
1,sachith,2
2,nalaka,1
3,abc,3

I want to map star column with another file where it has

1  1S
2  3S
3  5S

and finally csv should look like

id,name,star,level
1,sachith,2,3S
2,nalaka,1,1S
3,abc,3,5S

I have used ReplaceTextWithMapping, but it replaces all the 1,2,3 values including in id column.

Here it defines replacing a value, but I want to map and add a new column to the record.

Edit:

After @Upvote's answer. My ReplaceTextWithMapping conf

DarkLeafyGreen · Accepted Answer · 2019-10-28 11:20:03Z

2

Use ReplaceTextWithMapping. Overall flow:

GenerateFlowFile:

UpdateRecord:

Configure CSVReader to treat first line as header. Leave other properties untouched. Configure CSVRecordSetWrite to treat first line as header, schema to be derived from schema text property and set schema text to:

{
   "type":"record",
   "name":"foobar",
   "namespace":"my.example",
   "fields":[
      {
         "name":"name",
         "type":"string"
      },
      {
         "name":"age",
         "type":"int"
      },
      {
         "name":"id",
         "type":"string"
      },
      {
         "name":"nick",
         "type":"string"
      }
   ]
}

Notice that it includes the new column. ReplaceTextWithMapping:

Mapping file content:

1   1S
2   3S
3   4S

Values are separated by tab. Regex must match the last value not followed by a comma in each line:

[0-9](?!,)

Regular expression visualization

Debuggex Demo

Result:

edited Oct 28, 2019 at 11:20

answered Oct 25, 2019 at 7:09

DarkLeafyGreen

70.6k136 gold badges394 silver badges617 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sachith Muhandiram Over a year ago

When I follow these steps, final result row looks like 1,sachith,2,2, where final 2 should be 3S.

DarkLeafyGreen Over a year ago

@Sachith you have to replace ,2 with ,3S. Use ReplaceTextWithMapping or ReplaceText as described in the linked answer.

Sachith Muhandiram Over a year ago

Yes, I used ReplaceTextWithMapping processor. But final result same.

DarkLeafyGreen Over a year ago

Share your ReplaceTextWithMapping config.

Collectives™ on Stack Overflow

Add column to csv using predefined file using apache nifi

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related