Read excel attachment from json

[tanz]

Hi,

I have an requirement to read the excel file from the json.

excel file name: device.xlsx

excel file has data: Device Name Device IP

                                  X- Window    1.2.3.4

I want to read and fetch this excel data from the excel file using the json

JSON:

"attachments": [
{
"payload": "", // base 64 format
"file_name": "device.xlsx",
"content_type": "text/plain",
"encoding": "base64"
}
]

This should give the output:

"device_name":"X- Window"

"device_ip":"1.2.3.4"

Is there any way to achieve this through scripting, Could you guide on the script also?

[Hayo Lubbers]

Hi @tanz ,

If you have the attachment sysId, you can use the GlideExcelParser. It's not converted to a JSON, but you can do all kinds of actions on the excel, like reading the rows and getting the columns.

More info at : https://developer.servicenow.com/dev.do#!/reference/api/utah/server/sn_impex/GlideExcelParserScopedA...

var parser = new sn_impex.GlideExcelParser(); 
var attachment = new GlideSysAttachment();
// use attachment sys id of an excel file
var attachmentStream = attachment.getContentStream(<attachment sys id>);

parser.parse(attachmentStream); 

//retrieve the column headers
var headers = parser.getColumnHeaders();  
var header1 = headers[0]; 
var header2 = headers[1]; 

//print headers
gs.info(header1 + " " + header2); 

while(parser.next()) { 
  var row = parser.getRow(); 
  //print row value for both columns   
  gs.info(row[header1] + ' ' + row[header2]) 
}

Example from the API documentation: 

[tanz]

What if the excel has multiple rows like:

Device Name Device IP

X- Window    1.2.3.4

Y-Window      4.5.6.7

Can I read these data also. Could you help with the script?

[Hayo Lubbers]

Hi @tanz,

I cannot deliver the full script, but looking at your columns, the header[0] would be Device Name and header[1] would be the Device IP.

Untested logic, but it would look something like:

var parser = new sn_impex.GlideExcelParser(); 
var attachment = new GlideSysAttachment();
// use attachment sys id of an excel file
var attachmentStream = attachment.getContentStream(<attachment sys id>);

parser.parse(attachmentStream); 

//retrieve the column headers
var headers = parser.getColumnHeaders();  
var header1 = headers[0]; //Device Name
var header2 = headers[1]; //Device IP

while(parser.next()) { 
  var row = parser.getRow(); 
  var deviceName = row[header1];
  var deviceIP = row[header2];
 //Your logic what you want to do with the information. The while loop will deliver every time a new device and IP
}