Add column to csv using predefined file using apac

问题:

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

回答1:

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](?!,)

Debuggex Demo

Result: