I was working with a third party company that would be receiving data from a Service-now.com ITSM instance. Although the company supported traditional CSV files for most imports, the data structure that we needed to import was only supported on their end through Tab or Pipe (“|”) delimited characters — not your traditional comma separation that makes CSV so popular.
Until the service-now.com development team comes up with a good solution for allowing users to quickly and easily change the delimiter used in their CSV formats, I have done a little research and designed a way to quickly get non-comma delimited text files for data export in Service-now.com.
To start off with, I decided to use the JSON Web Service plugin to generate JSON formatted data. After that, I catch the JSON response and convert it to delimited text before it is generated into a downloadable file. So, for this to work, you are going to need to activate the JSON Web Service plugin.
Next, you need to create a Script Include named “DELIMProcessor”. This script include will take the JSONProcessor class and inherit from it. I then override some of the JSON methods with Delimited Text specific code instead.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 | var DELIMProcessor = Class.create(); DELIMProcessor.prototype = new JSONProcessor(); /** * Constructor - constructs a JSONProcessor object to first generate a JSON * object (Requires the JSON plugin), then allows us to convert to delimited * text format instead. * * @param request - * HttpServletRequest object * @param response - * HttpServletResponse object * @param table_name - * the targeted table name in the URL * @processor - the Processor Java class that is handling this process */ DELIMProcessor.prototype.initialize = function(request, response, table_name, processor, delimiter) { this.delimiter = delimiter; JSONProcessor.prototype.initialize.call(this, request, response, table_name, processor); }; /** * Entry point into DELIMProcessor class, called from the processor, right * after object instantiation. Assumes correct specification of sysparm_* * parameters or specified as DELIM object field value */ DELIMProcessor.prototype.process = function() { var responseArray; if (this.sysparm['action'] == "getRecords") responseArray = this.getRecords(); else this._outputDELIMError("Invalid sysparm_action: {0}", this.sysparm['action']); if (this.error){ this.debug("There was an error: " + this.error); return; // there was an error } if (responseArray == null){ this.debug("There was no response"); return; // there is no response } var delimText = this.convertToDelimitedText(responseArray, this.delimiter, ""); this.debug("Delimited Text: " + delimText); this._outputDelimitedTextResponse(delimText); }; /** * This converts a JSON object into a delimited string * @param rArray - the JSON response array (or portion thereof) * @param delimiter - The delimiter to be using * @param textSoFar - Delimited Text generated so far * @return textSoFar */ DELIMProcessor.prototype.convertToDelimitedText = function(rArray, delimiter, textSoFar){ var recordText = ""; var headerText = ""; for(var field in rArray){ if(typeof rArray[field] == "object"){ textSoFar = this.convertToDelimitedText(rArray[field], delimiter, textSoFar); } else { if(!textSoFar){ //also build header row if( headerText ) { headerText += this.delimiter; } headerText += """ + field + """; } if( recordText ){ recordText += this.delimiter; } recordText += """ + rArray[field] + """; } } return headerText + textSoFar + "\n" + recordText; }; /** * output a Delimited error object */ DELIMProcessor.prototype._outputDELIMError = function(errorMsg, arg) { var msg = gs.getMessage(errorMsg, new String(arg)); var response = { "error" : msg }; this._outputDELIMResponse(response); this.error = true; }; /** * write out the response to http response with the correct content-type */ DELIMProcessor.prototype._outputDELIMResponse = function(response) { var r = this._outputDelimitedTextResponse(response); this.response.setContentType("text/tab-delimited-values"); this.processor.writeOutput(r); }; /** * Output the delimited text to the http response with content-type * @param delimitedText * @return */ DELIMProcessor.prototype._outputDelimitedTextResponse = function(delimitedText){ this.response.setContentType("text/tab-delimited-values"); this.processor.writeOutput(delimitedText); }; // debugging log, to turn off, set this.enableDebug = false DELIMProcessor.prototype.debug = function(msg) { if (!this.enableDebug) return; gs.log("DELIMProcessor: " + msg); }; |
Now, if we want to export all of our incidents in a Pipe delimited format, we must create a new processor in service now.
The processor script should look like this:
Name: PIPEProcessor
Type: Script
Parameters: PIPE
Script:
1 | new DELIMProcessor(g_request,g_response,g_target,g_processor,"|").process(); |
Now, once we have that created, we can type in a URL with the PIPE processor text like this:
https://myinstance.service-now.com/incident.do?PIPE
We will be prompted to save the delimited text file.
Now you can also create other delimited processors like our PIPEProcessor and export with just about any delimiter you want.
please disregard my previous inquiry … I think I found the system property: glide.processor.json.row_limit
again .. great work, and thanks for sharing.
@Dan — you found the answer. However, please keep in mind that this is not a good solution for large amounts of data. The base JSON Web Service in ServiceNow has terrible performance issues with large sets of data. This is something that they are revamping likely in their next release.
Hi,
As of Dublin release, the JSON Web Service plugin is not available and not the JSONProcessor class either. Does anyone know how to make this compatible with the new JSONv2 processor?
Kind regards,
Dan Berglin
@Dan…yes, try out this link: https://john-james-andersen.com/blog/service-now/jsonv2-tutorial.html
I believe inside that blog post I include information on the differences so that you can modify your calls. There is not much you have to change luckily.
Hi John, I am quite new to scripting and this is the exact requirement that i have right now, so can you please re-write this code in order to meet the same requirement but in Kingston version of ServiceNow.
Looking forward to your response.
Regards
Sukirti