Sidebar

How can I sort the rows in my CSV message?

0 votes
291 views
asked May 15, 2019 by michael-h-5027 (13,910 points)
My sample CSV has firstname, lastname, dob but it is not sorted by any column. I want to be able to sort by one or more of the column values.

 

Brant,Goldstien,01011998
Sara,Hudson,03281924
Brant,Johnson,01011996
Michael,Goldstien,01011997
Michael,Hudson,03281922
Eric,Hudson,03281923
Terrible,Turkey,06171945
Brant,Hudson,03281925
Frank,Davis,10181953

1 Answer

0 votes

The sample channel here has a function that you can pass your CSV to as well as what columns to sort on and whether or not the CSV has a header. This function will return the sorted CSV.

https://www.qvera.com/kb/?qa=blob&qa_blobid=13679081181613979613

function sortCsvMessage(csvMessage,sortColumns,hasHeaders) {
   // preserve header if needed
   var headerRow = '';
   if (hasHeaders) {
      headerRow = csvMessage.getNode('*[0]');
   }
   
   // cycle message, extract column, and row and add to new TreeMap
   var sortedMap = new java.util.TreeMap();
   for (var i = 1; i <= csvMessage.getRowCount(); i++) {
      var mapKey = '';
      // var build sort
      for (var j = 0; j < sortColumns.length; j++) {
         mapKey += csvMessage.getNode(sortColumns[j] + '[' + i + ']');
         if (StringUtils.isNumeric(mapKey)) {
            mapKey = StringUtils.leftPad(mapKey, 9, '0');
         }
      }
      var row = csvMessage.getNode('*[' + i + ']');
      qie.debug(mapKey + '-' + i+', ROW:['+ row+']');
      sortedMap.put(mapKey + '-' + i, row);
   }
   
   // build new message
   var newMessage = new java.lang.StringBuilder();
   if (hasHeaders) {
      newMessage.append(headerRow).append('\n');
   }
   
   var iterator = sortedMap.entrySet().iterator();
   while (iterator.hasNext()) {
      var entry = iterator.next();
      newMessage.append(entry.getValue()).append('\n');
   }
   
   return newMessage.toString();
}

answered May 15, 2019 by michael-h-5027 (13,910 points)
edited Jan 29, 2021 by michael-h-5027
...