How to skip 1st row and parse data to mrvs from excel sheet

[akshayp]

Hi All, i searched lot on community for this scenario, but no luck..

I am attaching excel sheet to catalog item to reflect data in MRVS,

But its getting reflected only if column headers are in 1st row.

Now, in std sheet which is going to be attached to item, it has column headers in 2nd row and 1st row has some instructions what needs to be filled in each column.

Now, if i attach this std. sheet, data is not getting reflected in MRVS

I searched a lot but didn't found any workaround .

Please see my scripts and let me know if theres any way

Script Include--

var ParseExcelAttachment = Class.create();
ParseExcelAttachment.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {

parseExcelToMrvs: function() {
var parseExcel = this.getParameter('sysparm_excel_id');
var dataArr = [];
var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
// use attachment sys id of an excel file
var attachmentStream = attachment.getContentStream(parseExcel);
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];
var header6 = headers[5];
var header7 = headers[6];
var header8 = headers[7];
var header9 = headers[8];
var header10 = headers[9];
var header11 = headers[10];
var header12 = headers[11];
var header13 = headers[12];
var header14 = headers[13];
gs.info("akshay"+header1 + "" + header2 + "" + header3 + "" + header4 + "" + header5 + "" + header6 + "" + header7 + "" + header8 + "" + header9 + "" + header10 + "" + header11 + "" + header12 + "" + header13 + "" + header14);

while (parser.next()) {
var row = parser.getRow();
if (global.JSUtil.notNil(row[header1]) == true && global.JSUtil.notNil(row[header2]) == true && global.JSUtil.notNil(row[header3]) == true && global.JSUtil.notNil(row[header4]) == true && global.JSUtil.notNil(row[header5]) == true && global.JSUtil.notNil(row[header6]) == true && global.JSUtil.notNil(row[header7]) == true && global.JSUtil.notNil(row[header8]) == true && global.JSUtil.notNil(row[header9]) == true && global.JSUtil.notNil(row[header10]) == true && global.JSUtil.notNil(row[header11]) == true && global.JSUtil.notNil(row[header12]) == true && global.JSUtil.notNil(row[header13]) == true && global.JSUtil.notNil(row[header14]) == true) {
dataArr.push(parser.getRow());
gs.info("abp"+row[header1] + ' ' + row[header2]);

}
}
return JSON.stringify(dataArr);

},

parseExtPartyvalue: function() {
var parseComp = this.getParameter('sysparm_ext');
var compArr = [];

var grCompany = new GlideRecord('core_company');
grCompany.addEncodedQuery('nameIN' + parseComp);
grCompany.query();
while (grCompany.next()) {
compArr.push(grCompany.sys_id.toString());
}
return JSON.stringify(compArr);

},
type: 'ParseExcelAttachment'
});

Onchange catalog client script--

function onChange(control, oldValue, newValue, isLoading) {
if (isLoading || newValue == '') {
return;
}
var ga = new GlideAjax('ParseExcelAttachment');
ga.addParam('sysparm_name','parseExcelToMrvs');
ga.addParam('sysparm_excel_id', newValue);
ga.getXML(loadData);
}

//parse the attachment file
function loadData(response) {
var answer = response.responseXML.documentElement.getAttribute("answer");

var attachm = JSON.parse(answer);
alert(answer);

var obj = (g_form.getValue('m_a_issues').length != 0) ? JSON.parse(g_form.getValue('m_a_issues')) : [];
var userData = g_form.getValue('m_a_issues').length;

var grComp = [];
for (var j = 0; j < attachm.length; j++) {
grComp.push(attachm[j]["External Party"]); //push all the third party names to an array
}
//get the sys_id from the External party name mentioned in the file
var grComp1 = new GlideAjax('ParseExcelAttachment');
grComp1.addParam('sysparm_name', 'parseExtPartyvalue');
grComp1.addParam('sysparm_ext', grComp); //send all the third party names to server side to get the sys_ids
grComp1.getXML(loadCompany);

var gr_comp;

function loadCompany(response) {

var answer1 = response.responseXML.documentElement.getAttribute("answer");

gr_comp = JSON.parse(answer1);

for (var i = 0; i < attachm.length; i++) {

var Istate = attachm[i]["State"];
var Sdescription = attachm[i]["Short Description"];
var desc = attachm[i]["Description"];
var Severe = attachm[i]["Severity"];
var Eparty = gr_comp[i];
var review = attachm[i]["Reviewer"];
var assignee = attachm[i]["Assigned to"];
var DSDate = attachm[i]["Desired Start Date"];
var DEDate = attachm[i]["Desired End Date"];
var ASDate = attachm[i]["Actual Start Date"];
var AEDate = attachm[i]["Actual End Date"];
var recommend = attachm[i]["Recommendation"];
var explain = attachm[i]["Explanation"];
var Ctask = attachm[i]["Create Task"];
alert(Sdescription);
obj.push({
state: Istate,
short_description: Sdescription,
description: desc,
severity: Severe,
external_party: Eparty,
reviewer: review,
assigned_to: assignee,
desired_start_date: DSDate,
desired_end_date: DEDate,
actual_start_date: ASDate,
actual_end_date: AEDate,
recommendation: recommend,
explanation: explain,
create_task: Ctask
});
alert(Sdescription);

g_form.setValue('m_a_issues', JSON.stringify(obj));
}
}
}

[Ankur Bawiskar]

@akshayp 

Can't you skip the 1st row directly in the loop?

[akshayp]

Sorry,I didn't get that. Can you please tell me where i should make change?

[Huma1]

Hi @akshayp 

Did you found any solution for this requirement? I also have the exact same requirement