Get data from an attached xl spreadsheet and parse the data into a multi row variable sets

Saai1
Tera Expert

‎07-24-2023 12:27 PM

Hello All,

 

I have a requirement to get the data from an attached xl sheet and parse the data into a multi row variable set then delete the attachment from the request.

 

Thanks so much for any feedback you have!

0 Helpfuls
  • 739 Views
1 REPLY 1

Ratnakar7
Mega Sage
Mega Sage

‎07-24-2023 11:47 PM - edited ‎07-24-2023 11:48 PM

Hi @Saai1 ,

 

You can use the API called "sn_impex.GlideExcelParser()" to parse the Excel data.

The is the sample Business Rule, which will be triggered when a record is created, and it will parse the Excel attachment using the GlideExcelParser API and insert the individual records into the multi-row variable set on the target table. After successful insertion, it will delete the attachment from the request.

 

(function executeRule(current, previous /*null when async*/) {
  var attachmentSysId = current.getValue('attachment_field'); // Replace 'attachment_field' with the actual field name where the Excel attachment is stored.
  var targetTableSysId = current.getUniqueValue(); // Assuming the Business Rule is on the target table.

  // Check if an attachment is present
  if (!attachmentSysId) {
    gs.addInfoMessage('No Excel attachment found.');
    return;
  }

  // Use GlideExcelParser to parse the Excel data
  var parser = new sn_impex.GlideExcelParser();
  var excelData = parser.parse(attachmentSysId);

  // Get the target table
  var targetTable = new GlideRecord('target_table_name'); // Replace 'target_table_name' with the name of the target table.
  if (!targetTable.get(targetTableSysId)) {
    gs.addErrorMessage('Target table record not found.');
    return;
  }

  // Insert records into the multi-row variable set (target table)
  for (var i = 0; i < excelData.length; i++) {
    var record = targetTable.newItem('multi_row_variable_set_field_name'); // Replace 'multi_row_variable_set_field_name' with the name of the field representing the multi-row variable set.
    // Set values of each field in the multi-row variable set record based on excelData
    record.field1 = excelData[i].field1;
    record.field2 = excelData[i].field2;
    // ...
    targetTable.addNewItem(record);
  }

  // Save the target table record to persist the changes
  targetTable.update();

  // Delete the attachment from the request
  var attachment = new GlideSysAttachment();
  attachment.deleteAttachment(attachmentSysId);

  gs.addInfoMessage('Excel data successfully parsed and inserted into the multi-row variable set.');
})(current, previous);

 

Please ensure that you replace 'attachment_field', 'target_table_name', and 'multi_row_variable_set_field_name' with the actual field names in your instance. Additionally, handle any error scenarios and add appropriate validation checks to ensure data integrity and prevent any potential issues. Test the solution in a non-production environment before deploying it to production.

 

Also, refer https://developer.servicenow.com/dev.do#!/reference/api/utah/server/sn_impex-namespace/GlideExcelPar...

 

Thanks,

Ratnakar

 

0 Helpfuls