Validate the excel template automatically, when the user submits the request. Validate if it has the columns as mentioned

[mahfooz1]

On submitting the request after attaching the attachment, if user has attached file other that xlsx format then request should not be submitted.
Also, if user has attached xlsx file then Validate the excel template automatically, when the user submits the request. Validate if it has the columns as mentioned.

[mr18 · Accepted solution]

Please find the below Business Rule:

Table: sc_cart_item

When: before, insert

var gr1 = new GlideRecord('sys_attachment');
gr1.orderByDesc('sys_created_on');
gr1.setLimit(1);
//gr1.addQuery('table_sys_id', gr1.sys_id);
gr1.addQuery('table_name','sc_cart_item');
gr1.addQuery('file_name', 'CONTAINS', '.xlsx');
gr1.addEncodedQuery('sys_created_onRELATIVEGT@minute@ago@1');
gr1.query();

if(gr1.next())
{
//gs.addInfoMessage('Excel: inside while: attachment: ' + gr1.file_name);
var header_count = 0;


var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
// use attachment sys id of an excel file attachment
var attachmentStream = attachment.getContentStream(gr1.sys_id);
parser.parse(attachmentStream);
//retrieve the column headers
var headers = parser.getColumnHeaders();

var a = headers[0];
var b = headers[1];
var c = headers[2];
var d = headers[3];
var e = headers[4];
var f = headers[5];
var g = headers[6];
var h = headers[7];
var i = headers [8];
var j = headers[9];
var k = headers[10];
var l = headers[11];
var m = headers[12];
//gs.addInfoMessage('Column in the attachment contains: ' + a + ', ' + b + ', ' + c + ', ' + d + ', ' + e + ', ' + f + ', ' + g + ', ' + h + ', ' + i + ', ' + j + ', ' + k + ', ' + l) ;
//var obj = {};
// while(parser.next()) {
// var row = parser.getRow();
// //print row value for both columns
// gs.addInfoMessage(row[key] + ': ' + row[value]) ;
// obj[row[key]] = row[value];

if(a!='Asset tag' || b!='Serial number' || c!='Model category' || d!='Model' || e!='Owned by' || f!='Managed by group' || g!='Managed by' || h!='Assigned to' || i!='Company' || j!='State' || k!='Substate' || l!='Location' || m!='Cost')
{
gs.addErrorMessage('The columns used does not match with the template. Kindly review the template');
current.setAbortAction(true);
}
}

[shashank32]

Hi Mr

Can you please help me in solving below equirement ans suggest me the code to do it

i had source field names shown below

when i try to attach the excel sheet with different header name the transform map should not insert data to my target table 

Can you suggest me the code 

 the values of headers are not matching with source field names

so it should not allow to run transform

Thank You

Shashank