How to read the data from attached Excel file and store it in a variable before submitting the catalog item

[naveen80]

Hi Everyone,

I want to read the data that is present in the Excel/CSV template which is attached by end user. I would like to store it in a variable that is present on the catalog form before the request is submitted.

Any pointers/suggestions are highly appreciated.

Thanks in advance..!!

Regards,

Naveen.

[Brad Bowman]

Hi Naveen,

I have done this with a UI Action on an RITM or task.  In my case it was an Excel file attached, and I was using the contents to match a row in a multi-row variable set, then updating other variable(s) in the row based on the attachment.  This code could be adapted to run in an onSubmit Catalog Client Script that calls a Script Include with GlideAjax, passing in the unique id of the request to be able to look up the right attachment.  Are you expecting Excel attachments, CSV, or it could be either?  There's a different parser to read the file based on the file type.

[naveen80]

Hi Brad,

can you share me the script which you have worked on it.

Right now, I was using Excel file attachments.

Thanks for your quick response..

Regards,

Naveen.

[Brad Bowman]

This is my UI Action script.  My Excel file has a header row and 2 columns - model and sn.  I've simplified the script so that it updates a serial_number and model variable with the Excel contents, but in this example it will keep overwriting the same variable so that only the last one will show, but it's just an example.

var att = new GlideRecord('sys_attachment');
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 sn = headers[1];
	while(imp.next()) {
	  var row = imp.getRow();
	  current.variables.serial_number = row[sn];
	  current.variable.model = row[model];
          current.update();
        }
	action.setRedirectURL(current);
}
	

To use this in a Script Include called by an onSubmit script on the Catalog Item, the table_sys_id will be passed in from the client script.  Uncheck the Isolate script box and use gel('sysparm_item_guid').value to get the sys_id that will be used for the sc_cart_item attachment in the sys_attachment table.   

[Janani Venkatac]

HI naveen, Could you please share the complete code . thanks you