Apache Nifi: Counting numbers in a string inside a csv file

Question

I have a csv which contains two columns. But the columns download and upload have a string that can have multiple number of values separated by commas.

download,upload
"12,3,43","2,33,7,10"
"2,1","4,5,6,23,1"

I need to add each value inside the string and form a new csv with the addition.So after the Nifi Workflow my output csv must be:

download,upload
57,52
3,39

Basically should be the addition of the values inside each string as shown below. Hope you can suggest me the processors to be used and the configurations to be changed in order to achieve this output csv from the input csv as shown below.

@daggett This seems like a possible route. But I am not supposed to use code in my project. Is there any other processor or a group of processors which can make this happen? — Himsara Gallege

Upvote Upvote · Accepted Answer · 2019-10-29T08:52:36

You can achieve this using the JoltTransformJSON processor, which transforms JSON. It offers a split and sum transformation, which you need to get you desired output.

The strategy is:

Inspect each line of the CSV file
Convert from CSV to JSON
Apply a Jolt transformation, that splits, converts the string to an integer and finally sums
Convert the record back to CSV
Merge record into a CSV file

Overall flow:

GenerateFlowFile:

SplitRecord will take each CSV line and transform it to JSON:

Create a CSVReader and JsonRecordSetWriter. Setup CSVReader to use first line as header line. Leave the default properties. Set records per split to 1.

Use a JoltTransformJson processor and provide following jolt specification:

[
  {
    "operation": "modify-default-beta",
    "spec": {
      "downloadSplit": "=split(',', @(2,download))",
      "uploadSplit": "=split(',', @(2,upload))"
    }
  }, {
    "operation": "modify-overwrite-beta",
    "spec": {
      "downloadSplit": ["=toInteger", 0],
      "uploadSplit": ["=toInteger", 0]
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "download": "=intSum(@(1,downloadSplit))",
      "upload": "=intSum(@(1,uploadSplit))"
    }
  }, {
    "operation": "shift",
    "spec": {
      "download": "download",
      "upload": "upload"
    }
  }
]

Split
Convert to integer
Sum
Overwrite original download/uploaded with transformed ones

Convert record back to CSV:

Leave the default properties of reader and writer. Finally merge each single record back into a CSV file:

Use CSV reader and writer with default properties. You can control the number of records per CSV file by adjusting the properties of MergeRecord. Read more about the meaning of the properties here.

Output:

Notice that the order of the original lines changed. First line corresponds to the last line in the input.

Apache Nifi: Counting numbers in a string inside a csv file

1 Answers