Please use docs.servicenow.com for the latest documentation.

This site is for reference purposes only and may not be accurate for the latest ServiceNow version

Exporting Data

From Wiki Archive
Jump to: navigation, search
Note
Note: This article applies to Fuji and earlier releases. For more current information, see Exporting Data at http://docs.servicenow.com

The ServiceNow Wiki is no longer being updated. Visit http://docs.servicenow.com for the latest product documentation.

Overview

ServiceNow offers a variety of ways for administrators and users with the itil role to export data:

  • Form export: Export an individual record from the user interface. Choose PDF or XML format directly from a form.
  • List export: Export multiple records from the user interface. Choose CSV, Excel, PDF, or XML format directly from a list.
  • Scheduled export with reports: Automatically export multiple records from a table on a set schedule. Create a scheduled job to regularly export data as a report.
  • Direct URL access: Export multiple records from a table using the ServiceNow CSV, Excel, PDF, or XML processor. Specify the table form or list you want to export in the URL.
  • Web services/SOAP: Export multiple records from a table when an external client makes a web services request. Create an external application or process to automate the retrieval of data from an instance via web services or SOAP.

Available Export Formats

ServiceNow supports the following export formats:

Export Format Description
CSV Export table records as a comma-separated value text file. Use this option to export the currently displayed fields in the list or form as a text file. Configure the list or form to add or remove fields from the export. When exported to CSV, dot-walked fields appear using their full field name, such as u_assignment_group.parent.

Note: By default, ServiceNow exports all CSV files in Windows-1252 encoding. If you need to export translated data, set the glide.export.csv.charset system property to UTF-8 (starting with Calgary release).

Excel Export table records as a Microsoft Excel spreadsheet. Use this option to export the currently displayed fields in the list or form as an Excel spreadsheet. Configure the list or form to add or remove fields from the export.
XML Export table records as an XML document. Use this option to export all data from a table or all data for a particular record. The XML file has an XML element for each column in the table.
PDF Export table records as a Portable Document Format file. Use this option to export the currently displayed fields in the list or form as a PDF file. Configure the list or form to add or remove fields from the export.

For information about HTML and exported lists see Rendering HTML in Exported Lists on the ServiceNow Community.

Exporting Form Data

Export an individual record from a form by right-clicking a form header bar and selecting the export type. Export formats include:

  • PDF (Portrait)
  • PDF (Landscape)
  • XML (This Record)
Error creating thumbnail: Unable to save thumbnail to destination
Right-click a form header bar and select the export format.

Note
Note: When exporting PDF data from a form, only the fields added to the current view are exported. Fields present on the form but hidden by a UI policy are included in the PDF. When exporting XML data, however, all the fields are exported, regardless of the view. CSV and Excel records cannot be exported from a form.


Exporting List Data

Export a list of records by right-clicking a list header bar and selecting the export format.

Error creating thumbnail: Unable to save thumbnail to destination
Right-click a list header bar and select the export format.

Note
Note: To export records in an embedded list, export the record containing the list.

Export formats include:

  • Excel
  • CSV
  • XML
  • PDF (Portrait)
  • PDF (Landscape)
  • PDF (Detailed Portrait): Exports the list and the associated form for each record.
  • PDF (Detailed Landscape): Exports the list and the associated form for each record.

You can display Related List items on detailed PDF reports by setting the glide.export.pdf.list.related_list system property to true.

Note
Note: You can control how line breaks appear in exported CSV data using the glide.csv.export.line_break system property (starting with Dublin release).


Determining Which List Fields are Exported

By default, exporting data from a list exports only the fields that are visible from the current view. If you want to export fields from another list view, you can switch views from the UI. Alternatively, you can add the sysparm_view parameter to the URL request. For example, to export fields visible from the Self Service (ess) view:

<source lang="text">https://instance_name.service-now.com/incident.do?CSV&sysparm_view=ess</source>

If you are exporting CSV or Excel data and do not specify a view, the export uses the default list view. You can export all fields by setting the sysparm_default_export_fields parameter to all (available starting with the Dublin release). If you are exporting XML data, the export returns all fields unless you specify a particular view. The sysparm_default_export_fields parameter has no effect on XML exports.

Exporting Data with Scheduled Reports

You can schedule regular or one-time exports from list reports. ServiceNow sends the report to one or more users by email. Export formats include:

  • Excel
  • CSV
  • PDF
  • PDF (Landscape)

If you need to share data with another ServiceNow instance or integrate with another application, consider creating a web service or SOAP message instead.

Exporting Directly from the URL

You may want to export data from the URL if you need to dynamically export data from a script or web service. You must be familiar with the ServiceNow table and column names to export data directly from the URL. See Navigating by URL for more information about navigating to forms and lists.

To export data directly from the URL, create a URL containing the following parts:

  1. Specify the instance URL. For example, https://<instance name>.service-now.com/.
  2. Specify the table form or list to export. For example, incident_list.do.
  3. Specify the export format processor to use for the export. For example, ?CSV.
  4. [Optional] Specify a query and sort order with URL parameters. For example, &sysparm_query=sys_id%3E%3Db4aedb520a0a0b1001af10e278657d27.

The final URL should look like one of these sample URLs:

URL Description

https://<instance name>.service-now.com/incident_list.do?CSV

Export all incidents to a comma-separated value text file.

https://<instance name>.service-now.com/incident_list.do?CSV&sysparm_query=sys_id%3E%3Db4aedb520a0a0b1001af10e278657d27

Export a particular incident to a comma-separated value text file.

https://<instance name>.service-now.com/incident_list.do?CSV&sysparm_order_by=sys_id

Export all incidents to a comma-separated value text file and sort the list by sys_id.
Error creating thumbnail: Unable to save thumbnail to destination
Exporting data directly from the URL

Note
Note: ServiceNow enforces basic authentication for direct URL access. The data extracted from the URL contains only the fields to which the currently authenticated user has read access.


Export Format Processors

The ServiceNow platform provides a default upper limit for XML data exports starting with the Dublin release. If you are using an older version, see the previous version information.

ServiceNow provides the following export format processors:

Export Processor URL Syntax Export Limits Description
CSV  ?CSV 10,000 rows Exports table records as a comma-separated value text file.
Excel  ?EXCEL 10,000 rows Exports table records as a Microsoft Excel spreadsheet.
XML  ?XML 10,000 rows Exports table records as an XML document.
PDF  ?PDF 5,000 rows Exports table records as a Portable Document Format file.
Schema  ?SCHEMA N/A Exports the database schema for the table.
XSD  ?XSD N/A Exports the table structure in XSD format.

See Export Limits for information about processor export limits and how to work around them if a table exceeds the export limit.

Note
Note: Export processors return all requested records regardless of whether you use the table.do or table_list.do format to identify the export table.


Using URL Queries to Filter List Results

Use URL queries to programmatically generate filtered lists before exporting them. URL queries are useful for scripts that generate a list of records and where no user will manually add the filter from the UI. You must be familiar with the table's column names and values to create a query.

To create URL queries:

  1. Specify the instance URL. For example, https://demo.service-now.com/.
  2. Specify the list URL for the table you want to export. For example, incident_list.do.
  3. Specify the export format processor for the export. For example, ?XML.
  4. Specify the query as the value of the sysparm_query parameter. For example, ?sysparm_query=priority=1.
  5. [Optional] Specify the result sort order with the sysparm_order_by= parameter. For example, CSV&sysparm_order_by=assigned_to.
Note
Note: All queries use a column name, an operator, and a value. See Condition Builder for a list of available operators.


For example, to export a list of all priority 1 incidents as an XML file, use the following URL:
https://<instance name>.service-now.com/incident_list.do?XML&sysparm_query=priority=1&sysparm_order_by=assigned_to

URL Query Parameters

ServiceNow provides the following URL query parameters:

URL Parameter URL Syntax Description
sysparm_query sysparm_query=[column name][operator][value] Displays a list of records that match the query. For example:

https://<instance name>.service-now.com/incident_list.do?XML&sysparm_query=priority=1

sysparm_orderby sysparm_orderby=[column name] Sorts a list of records by the column name provided. For example:

https://<instance name>.service-now.com/incident_list.do?sysparm_query=priority=1&sysparm_orderby=assigned_to

You can sort by only one column using sysparm_orderby. To sort by multiple columns, use sysparm_query=ORDERBY[column name]^ORDERBY[column name]. For example: sysparm_query=ORDERBYassigned_to^ORDERBYpriority.

Example XML Export

The following URL query produces an XML document similar to the sample shown:

https://<instance name>.service-now.com/incident_list.do?XML&sysparm_query=priority=1&sysparm_order_by=assigned_to

<source lang="xml"> <?xml version="1.0" encoding="UTF-8"?> <xml> <incident> <active>true</active> <activity_due>2012-11-28 20:44:11</activity_due> <approval>not requested</approval> <approval_history/> <approval_set/> <assigned_to/> <assignment_group>d625dccec0a8016700a222a0f7900d06</assignment_group> <business_duration/> <business_stc/> <calendar_duration/> <calendar_stc/> <caller_id/> <category>inquiry</category> <caused_by/> <child_incidents>0</child_incidents> <close_code/> <close_notes/> <closed_at/> <closed_by/> <cmdb_ci/> <comments/> <comments_and_work_notes/> <company/> <contact_type>phone</contact_type> <correlation_display/> <correlation_id/> <delivery_plan/> <delivery_task/> <description/> <due_date/> <escalation>0</escalation> <expected_start/> <follow_up/> <group_list/> <impact>1</impact> <incident_state>1</incident_state> <knowledge>false</knowledge> <location>108752c8c611227501d4ab0e392ba97f</location> <made_sla>true</made_sla> <notify>1</notify> <number>INC0010040</number> <opened_at>2012-11-28 16:31:08</opened_at> <opened_by>6816f79cc0a8016401c5a33be04be441</opened_by> <order/> <parent/> <parent_incident/> <priority>1</priority> <problem_id/> <reassignment_count>0</reassignment_count> <rejection_goto/> <reopen_count>0</reopen_count> <resolved_at/> <resolved_by/> <rfc/> <severity>3</severity> <short_description>test +XB</short_description> <skills/> <sla_due/> <state>1</state> <subcategory/> <sys_class_name>incident</sys_class_name> <sys_created_by>admin</sys_created_by> <sys_created_on>2012-11-28 16:31:33</sys_created_on> <sys_domain>global</sys_domain> <sys_id>4efe74640982300000a286aa871e705a</sys_id> <sys_mod_count>4</sys_mod_count> <sys_updated_by>admin</sys_updated_by> <sys_updated_on>2012-11-28 18:44:11</sys_updated_on> <time_worked/> <upon_approval>proceed</upon_approval> <upon_reject>cancel</upon_reject> <urgency>1</urgency> <user_input/> <variables/> <watch_list>dfdc91a809c2300000a286aa871e7057,46d44a23a9fe19810012d100cca80666</watch_list> <wf_activity/> <work_end/> <work_notes/> <work_notes_list/> <work_start/> </incident> ... </xml> </source>

Calling URL Exports Programmatically

You can dynamically export data from a script or web service by calling a URL export from any programming language. The following procedure includes code samples that demonstrate calling a URL export in C# for a .Net framework call:

  1. Add the following Imports: <source lang="csharp"> using System.Net; using System.IO; </source>
  2. Call the Download method: <source lang="csharp"> static void Main(string[] args) { // Call to DownloadFile method supplying the URL and location to save CSV file locally int read = DownloadFile("https://demo007.service-now.com/incident_list.do?CSV&sysparm_query=priority=1&sysparm_order_by=assigned_to", "c:\\test\\incident.csv"); } </source>
  3. Create a Download method as follows: <source lang="csharp"> public static int DownloadFile(String url, String localFilename) { // Function will return the number of bytes processed // to the caller. Initialize to 0 here. int bytesProcessed = 0; // Assign values to these objects here so that they can // be referenced in the finally block Stream remoteStream = null; Stream localStream = null; WebResponse response = null; // Use a try/catch/finally block as both the WebRequest and Stream // classes throw exceptions upon error try { // Create a request for the specified remote file name WebRequest request = WebRequest.Create(url); // Create the credentials required for Basic Authentication System.Net.ICredentials cred = new System.Net.NetworkCredential("user_name", "password"); // Add the credentials to the request request.Credentials = cred; if (request != null) { // Send the request to the server and retrieve the // WebResponse object response = request.GetResponse(); if (response != null) { // Once the WebResponse object has been retrieved, // get the stream object associated with the response's data remoteStream = response.GetResponseStream(); // Create the local file localStream = File.Create(localFilename); // Allocate a 1k buffer byte[] buffer = new byte[1024]; int bytesRead; // Simple do/while loop to read from stream until // no bytes are returned do { // Read data (up to 1k) from the stream bytesRead = remoteStream.Read(buffer, 0, buffer.Length); // Write the data to the local file localStream.Write(buffer, 0, bytesRead); // Increment total bytes processed bytesProcessed += bytesRead; } while (bytesRead > 0); } } } catch (Exception e) { Console.WriteLine(e.Message); } finally { // Close the response and streams objects here // to make sure they're closed even if an exception // is thrown at some point if (response != null) response.Close(); if (remoteStream != null) remoteStream.Close(); if (localStream != null) localStream.Close(); } // Return total bytes processed to caller. return bytesProcessed; } </source>

Export Limits

The ServiceNow platform provides a default upper limit for XML data exports.

The purpose of the upper limit is to avoid creating performance issues when a table is excessively large. If you need to export more records than the threshold permits, break up the export into separate manageable chunks.

In addition to the format-specific limits, you may need to set com.glide.processors.XMLProcessor.max_record_count to match the upper limit set by the format-specific limit.

Export Limit Properties

You can set the number of records to return during an export using the URL parameter sysparm_record_count. However, the system analyzes the following settings to determine whether an export limit should be applied.

  1. First, the platform checks the property that defines the format-specific export limit (see table). Each format can have a different limit. Although this property can be set to any value, exceeding the default export limit can impact system performance. You may want to set the property at or below the default limit and have users export large amounts of data in smaller increments.
  2. If the format-specific property is not set, the system checks the property for the general export limit (see table). This property can also be set to any value, but exceeding the default export limit can impact system performance.
  3. If neither the format-specific export limit nor the general export limit property is set, the system enforces the default export limit (see table).
Note
Note: These properties are not defined by default. You must add the property to assign a value to it.


Format Format-Specific Export Limit General Export Limit Default Export Limit
XML glide.xml.export.limit glide.ui.export.limit 10,000
CSV glide.csv.export.limit glide.ui.export.limit 10,000
EXCEL glide.excel.export.limit glide.ui.export.limit 10,000
PDF glide.pdf.max_rows N/A 5,000
PDF glide.pdf.max_columns N/A 25

Although the number of columns can be set higher than 25 in the PDF export, this is not advisable as only 25 header labels fit on a page.

A warning threshold property called glide.ui.export.warn.threshold controls how the records are exported. If a user attempts to export a number of records from a list that exceeds the warning threshold, a dialog box offers the choice of waiting for the export to complete or having the exported records emailed as an attachment. The warning threshold can be changed in the system property. The email attachment must not exceed the maximum allowed email size or configured email attachment size. For more information, see Exported Table Emails.

Example 1: Exporting to CSV

  • glide.csv.export.limit = 20,000
  • glide.ui.export.limit = 10,000
  • com.glide.processors.XMLProcessor.max_record_count = 20,000
  • Default export limit for CSV = 10,000
Records to be Exported Records Returned
15,000 15,000
30,000 20,000
Note
Note: In the second export, the number of records returned from the database is limited because the number of records specified for export exceeds the value set in the glide.csv.export.limit property.


Example 2: Exporting to Excel

  • glide.excel.export.limit = no entry
  • glide.ui.export.limit = no entry
  • Default export limit for EXCEL = 10,000
Records to be Exported Records Returned
10,000 10,000
30,000 10,000
Note
Note: In the second export, the number of records returned from the database is limited because the number of records specified for export exceeds the default export limit for Excel, 10,000 records.


Example 3: Exporting to PDF

  • glide.pdf.max_rows = 3,500
  • Default and maximum export limit for PDF = 5,000
Records to be Exported Records Returned
2,000 2,000
10,000 5,000
Note
Note: In the first export, all records are returned because the number of records specified for export does not exceed the glide.pdf.max_rows property. In the second export, the number of records returned is limited because the number of records specified for export exceeds the value in the glide.pdf.max_rows property.


Breaking Up Large Exports

If the number of records to be exported exceeds the actual export limit, you may want to break the export into smaller increments that do not place a significant performance load on the platform.

  1. Filter the list to display the records you want to export.
  2. Write down the number of records returned.
  3. If the record number is higher than the defined threshold, issue a sysparm query for the first 10,000 records using the following syntax:
    https://<instance name>.service-now.com/syslog_list.do?XML&sysparm_order_by=sys_id&sysparm_record_count=10000
    This exports the first 10,000 records in order, sorted by the sys_id number.
  4. Find the next record in order, such as 10,001.
  5. Right-click the row and copy the sys_id of the next record you want to export.
  6. Access the next series of records with a greater than or equal to query run against the sys_id of record 10,001.
    The following example shows a query that uses a sys_id of b4aedb520a0a0b1001af10e278657d27. Use the syntax shown in this query to export the next set of records.
    https://<instance name>.service-now.com/syslog_list.do?XML&sysparm_query=sys_id%3E%3Db4aedb520a0a0b1001af10e278657d27&sysparm_order_by=sys_id&sysparm_record_count=10000
    Note: URL queries use typical percent encoding. In this example, the greater than sign (>) is encoded as %3E and the equal sign (=) is encoded as %3D.
  7. Continue issuing this query, using the starting sys_id for the next set of records until you have exported all the necessary records.

Excel Export Threshold

Excel exports are intended for relatively small exports, fewer than 500,000 cells, while CSV can handle larger exports.

Whenever you export to Excel and the resultant spreadsheet has more than 500,000 cells (by default), the export process stops and you are given the Excel file at that point. In the bottom row, there will be the following message: Export stopped due to excessive size. Use CSV for a complete export:

ExcelExportThreshold1.png


The Excel export cell threshold is customizable using the glide.excel.max_cells integer property. Note: Increasing this threshold may cause a memory issue in your instance. The threshold is set at an appropriate level to prevent resource issues.

The export will put the information into the Excel document with 32,000 rows per spreadsheet.

Enabling Export Debug Logging

When the property glide.export.debug is true, the instance logs export processing including database query time and the time taken to write data to the file. Debug logs are indicated by the text Export API. Prolonged use of this property can affect performance, so it is best to use it while debugging export processing, and then set the property back to false.

Enhancements

Fuji

You can enable export logging to record detailed export performance data.

Dublin

  • A new system property, glide.csv.export.line_break, controls how line breaks appear in exported CSV data.
  • A default upper limit is available for XML data exports. However, the upper and lower limits from earlier versions will continue to function correctly in a Dublin instance.
  • A new system property, glide.ui.export.warn.threshold, controls how a user receives returned export records when the limit has been exceeded. The available options are to wait for the export to complete or have the exported records sent as an email attachment.

Calgary

  • A new system property, glide.export.csv.charset, controls the character set used to export CSV files. This property allows localized instances to export strings in a character set supported by the language. Starting with Calgary, you can set the system property to the character set name you want to use to export CSV files.