Read .CSV formatted files in ServiceNow

Hemanth M1 · ‎12-24-2023

Hi Everyone,

1)We know the existing API to parse Excel attachments using sn_impex.GlideExcelParser() : parse .xlsx formatted Excel files and access file data in script

More info: https://developer.servicenow.com/dev.do#!/reference/api/utah/server/sn_impex-namespace/GlideExcelPar...

2)Aslo, We have an API to parse CSV formatted records into an object or an array using -CSVParser (not to read the .csv formatted file)

Example :

Input : var csvLine = '\"Joe\",\"Smith\",\"1470 W Carmen, Chicago IL, 60640\"'; var delimiter = ','; var quoteCharacter = '"';

var x = new sn_impex.CSVParser().parseLineToArray(csvLine, delimiter, quoteCharacter);

gs.log(x[0]); gs.log(x[1]); gs.log(x[2]);

Output: Joe Smith 1470 W Carmen, Chicago IL, 60640

However how do we parse .csv formatted files into any fomatted data through script??

Possible Use cases : Read .csv files from an inbound email or Parse .csv file from the sys_attachment table.

Lets look at the parse logic and will include this logic in any above use case scenarion.

var table_sys_id = "table sys id from the sys_attachment table;
var table_name = "table name from sys_attachment"
  //read the file
  var gsa = new GlideSysAttachment();
  var bytesInFile = gsa.getBytes('sys_email', table_sys_id); // tablename, table sysID
  var originalContentsInFile = Packages.java.lang.String(bytesInFile); // constructs a new String by decoding the specified array of bytes.
  originalContentsInFile = String(originalContentsInFile);
  var fileData = originalContentsInFile.split('\n'); //split for new line
  var csvHeaders = fileData[0] ;  //get CSV headers in the first row.

//get file length 
  var fileLength =fileData.length;

  //lop through all the rows
  for(i=1 ; i<fileData.length -1 ; i++){
  var rowDetails = fileData[i] ;
  gs.print("Values: "+i+" : "+rowDetails); //this prints all the rows in the .csv file
    }

Lets try this with an use case:

1)Example .csv file

2)Lets attach this file to a record in the system

3)Lets look at the sys_attachment to get table sysid and table name.

4)Lets execute the parse login in the background script to review resuts.

5)Result 😍:

Things to consider here:

A)You can use split with " , " to retrieve each column values.

B)Include exception/error handling if file is empty or rows are empty.

C)add logic if column values have extra "," to retrieve appropriate values.

Hope this article helps when you want to parse .csv files

Thank you,