Modify csv based on a column value in Nifi by adding a new line

Question

I have csv file of form

Id, Name, Class
1, Kevin,[Eight, Nine]
2, Mark,Four

How can I create a new csv as follows

Id, Name, Class
1, Kevin,Eight
1, Kevin,Nine
2, Mark,Four

Basically, If column Class has array of string then put it in multiple rows replicating all other column values. I am able to remove [ ] parenthesis using replaceText and replace with empty string. So, basically i have following csv if it helps.

Id, Name, Class
    1, Kevin, Eight, Nine
    2, Mark,Four

Answer 1

I used ExecuteGroovyScript to split the string in [Eight, Nine] by ,. Once this string is split then I append remaining before and after contents. Then I use ReplaceText to remove [ and ].

def flowFile = session.get()
if(!flowFile) return
try {
flowFile = session.write(flowFile, {inputStream, outputStream ->
    outputStream.withWriter("UTF-8"){ w ->
        inputStream.eachLine("UTF-8"){ line ->
         def splitArray = new String[0];
         def subString = "";
         def x = line.indexOf("[")+1;
         def y = line.indexOf("]");
         if(x > 0 && y >0)
         subString = line.substring(x,y);
         if(subString != null && subString.length() >0)
             splitArray = subString.split(',')
             if(splitArray.length > 1) {
                 def lineBefore = line.substring(0,x);
                 def lineAfter = line.substring(y,line.length());
                for(int i=0;i<splitArray.length;i++) {
                    w << lineBefore << splitArray.getAt(i) << lineAfter << '\n'
                }
            }else {
                w << line << '\n'
            }
        }
    }
} as StreamCallback)

session.transfer(flowFile, REL_SUCCESS)
}catch(e) {
      log.error('Error capturing nextLink', e)
      session.transfer(flowFile, REL_FAILURE)
}