How to populate data in Multirow Variable Set from Excel ?

[Dhanu1]

Hi All,

I have variable set named as New stop in 13 variable are there with different type of formats data, string, numeric.. etc
Requirement: There is bulk (Excel ) data which contains all these columns we need to validate and after that populate those values into the service catalog.

Thanks in Advance.

[Brad Bowman]

Here is a script I use in a UI Action so that end users with only the itil role can populate an empty MRVS with data from an Excel spreadsheet.  It takes the most-recently attached Excel file (or there's a line commented out where you can hard-code the name) and imports the data into a MRVS.  In this example the Excel file contains 3 columns and the first row is headers. I'm not doing any validation as this wasn't necessary in this case, but the first column in Excel is a model display name and the variable in the MRVS is a reference type, so I'm looking up the sys_id.

current.update();
action.setRedirectURL(current);

var att = new GlideRecord('sys_attachment');
//att.addQuery('file_name', 'serial number import.xlsx');
att.addQuery('content_type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
att.addQuery('table_sys_id', current.sys_id);
att.orderByDesc('sys_created_on');
att.query();
if(att.next()){
	var imp = new sn_impex.GlideExcelParser();
	var attachment = new GlideSysAttachment();
	var attachmentStream = attachment.getContentStream(att.sys_id.toString());
	imp.parse(attachmentStream);
	var headers = imp.getColumnHeaders();
	var model = headers[0];
	var snt = headers[1]; 
	var sn = headers[2];
	var count = 0;
    var newRow = '';
	while(imp.next()) {
		var row = imp.getRow();
		var mrvs = current.variables.network_gear_asset_mrvs; //MRVS internal name
        newRow = mrvs.addRow();
        var mdl = new GlideRecord('cmdb_model');
    	mdl.addQuery('display_name', row[model]);
    	mdl.query();
    	if(mdl.next()){
            newRow.v_mrvs_model = mdl.sys_id;
        }
    	newRow.v_mrvs_serial_number_temp = row[snt];
    	newRow.v_mrvs_serial_number = row[sn];
        count++;
    }
	if(count > 0){
		gs.addInfoMessage(count + ' rows added to Network Gear Assets table.');
	}
	current.update();
	action.setRedirectURL(current);
}