Question

Looping over cell content

  • 3 March 2023
  • 2 replies
  • 106 views

Hello,

I am reading an excel file and my cell contains content like - “A/B/C”

I need to seperate out the A, B, C into different records.

I was using a for loop which is working fine as a java code, but in streamsets it’s printing out only the last updated value i.e. C in case of the above example.

 

for(String item : splitCellData(cell, indexOfChange, cell.toString().length(), "/")){                    ffileRecord.value[‘content’] = item;                 }

 

 

The split data function

 

def splitCellData(Cell cell, int startIndex, int endIndex, String regex){
   String res = null;
    res = cell.toString().substring(startIndex, endIndex);
    String[] result = res.split(regex);
    return result;
 }


2 replies

Userlevel 4
Badge

@groversakshi1998 

 

Is this something what you are trying to do?

 

Just added a JSONEvaluator with the following code:

var records = sdc.records;
for(var i = 0; i < records.length; i++) {
    try {
          var splitcolumn = records[i].value.f1.split( '/' );
          for (var j = 0; j < splitcolumn.length; j++) {
               records[i].value.col1 = splitcolumn[j];
              sdc.output.write(records[i]);
        }
    } catch (e) {
        sdc.error.write(records[i], e);
    }
}

 

You can then add a field remover to remove F1 and then it will leave being 3 records of col1

 

Hope that helps.

Userlevel 5
Badge +1

@groversakshi1998 

I am having one doubt here , can’t we use field splitter processor to get the required output.

As per your use case , I created one excel file with the cell content (A/B/C) and splitted the cell entries into multiple fields.

I am attaching the excel file for your reference and do let me know if it helps .

Below the preview of file entries and its output in detail.

 

 

 

 

Thanks & Regards

Bikram_

Reply