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.

[Madhan007]

Hi @Ankur Bawiskar @Vishal Jaswal , the script is working fine now, but if the Excel sheet contains a lot of rows
(exceeds 5000), a lot of time is being consumed to parse the Excel. Any solution for this ?

Thank You

[Ankur Bawiskar · Accepted solution]

@Madhan007 

So what was the fix? please share the complete script so that it helps other members in future

Unfortunately it will take time as you are iterating 1 row at a time

There is nothing much you can do there

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