Parse the Excel sheet and create records in Table

[Madhan007]

Hi Everyone,

when an Excel sheet is attached in an Attachment Variable with the provided template in a Record Producer, I need to parse the Excel and create the records in a custom table.
so I wrote an After insert BR and parsed the Excel through GlideExcelParser(). The Problem is when the data is deleted/updated/modified in the template(after the data is copied into the Excel template), the GlideExcelParser keeps running even for the empty rows as well and the BR kept Running which is causing a major impact on instance. Even the Break statement inside the while loop did not work.

please find the code below

var parser = new sn_impex.GlideExcelParser();

    var attachment = new GlideSysAttachment();

    // use attachment sys id of an excel file

    var attachmentStream = attachment.getContentStream(current.participant_list);

    parser.parse(attachmentStream);

    //retrieve the column headers

    var headers = parser.getColumnHeaders();

    var header1 = headers[0];

    var header2 = headers[1];

    //var header3 = headers[2];

    //var header4 = headers[3];

    var header3 = headers[2];

    var header4 = headers[3];

    var header5 = headers[4];

    if (header1 == 'Associate ID' && header2 == 'Associate Name' && header3 == 'Project Code' && header4 == 'Project Name' && header5 == ' Associate Participation Status(Active/Inactive)') {

        while (parser.next()) {

            var row = parser.getRow();        

              if (row[header1] != '') {
                   //create records in table
               }else{
                  break;
                  }
             parser.close()
      }

[Ankur Bawiskar · Accepted solution]

@Madhan007 

try this

var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
// use attachment sys id of an excel file
var attachmentStream = attachment.getContentStream(current.participant_list);

parser.parse(attachmentStream);

// Retrieve the column headers
var headers = parser.getColumnHeaders();
var header1 = headers[0];
var header2 = headers[1];
var header3 = headers[2];
var header4 = headers[3];
var header5 = headers[4];

if (header1 == 'Associate ID' && header2 == 'Associate Name' && header3 == 'Project Code' && header4 == 'Project Name' && header5 == ' Associate Participation Status(Active/Inactive)') {
    while (parser.next()) {
        var row = parser.getRow();
        
        // Check if the row is empty
        if (!row[header1] && !row[header2] && !row[header3] && !row[header4] && !row[header5]) {
            break;
        }

        if (row[header1] != '') {
            // Create records in table
        }
    }
    parser.close();
}

Changes included

1. The while (parser.next()) loop continues to process rows until it encounters an empty row.
2. The if (!row[header1] && !row[header2] && !row[header3] && !row[header4] && !row[header5]) condition checks if all the relevant columns in the row are empty. If they are, the loop breaks.
3. The parser.close() method is called after the loop to ensure the parser is properly closed.

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Shivalika]

Hello @Madhan007 

You are only breaking the loop for this. Also use "return" 

    if (row[header1] != '') {

                   //create records in table

               }else{

                  return; 

                  }

This will break your Function in BR, BR will not run anymore. 

Kindly mark my answer as helpful and accept solution if it helped you in anyway. This will help me be recognized for the efforts and also move this questions from unsolved to solved bucket. 

Regards,

Shivalika 

My LinkedIn - https://www.linkedin.com/in/shivalika-gupta-540346194

My youtube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=0WynLcOwNeEISQCY QQ

[Vishal Jaswal]

Hello @Madhan007 

You are only checking one column ‘header1’ rather you should check all 5 using !rowcolumn. The ‘parser.close’ shouldn’t be inside the loop. When you want to skip, then use continue instead of break

var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(current.participant_list);

parser.parse(attachmentStream);

// Retrieve column headers
var headers = parser.getColumnHeaders();
var header1 = headers[0];
var header2 = headers[1];
var header3 = headers[2];
var header4 = headers[3];
var header5 = headers[4];

// Validate headers
if (header1 === 'Associate ID' && header2 === 'Associate Name' &&
header3 === 'Project Code' && header4 === 'Project Name' &&
header5 === ' Associate Participation Status(Active/Inactive)') {

while (parser.next()) {
var row = parser.getRow();

// Skip empty rows by checking all  columns
var isRowEmpty = !row[header1] && !row[header2] && !row[header3] && !row[header4] && !row[header5];
if (isRowEmpty) {
continue; // continue will Skip this iteration for empty rows whereas break will stop
}

// Process non-empty rows
if (row[header1]) {
// Create records in the table
// Example: gs.log("Processing row: " + row[header1]);
}
}
}

// parser is closed outside the loop
parser.close();

[Ankur Bawiskar · Accepted solution]

@Madhan007 

try this

var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
// use attachment sys id of an excel file
var attachmentStream = attachment.getContentStream(current.participant_list);

parser.parse(attachmentStream);

// Retrieve the column headers
var headers = parser.getColumnHeaders();
var header1 = headers[0];
var header2 = headers[1];
var header3 = headers[2];
var header4 = headers[3];
var header5 = headers[4];

if (header1 == 'Associate ID' && header2 == 'Associate Name' && header3 == 'Project Code' && header4 == 'Project Name' && header5 == ' Associate Participation Status(Active/Inactive)') {
    while (parser.next()) {
        var row = parser.getRow();
        
        // Check if the row is empty
        if (!row[header1] && !row[header2] && !row[header3] && !row[header4] && !row[header5]) {
            break;
        }

        if (row[header1] != '') {
            // Create records in table
        }
    }
    parser.close();
}

Changes included

1. The while (parser.next()) loop continues to process rows until it encounters an empty row.
2. The if (!row[header1] && !row[header2] && !row[header3] && !row[header4] && !row[header5]) condition checks if all the relevant columns in the row are empty. If they are, the loop breaks.
3. The parser.close() method is called after the loop to ensure the parser is properly closed.

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Madhan007]

Hi @Ankur Bawiskar @Vishal Jaswal , parser.close() is outside the while loop only.