File type data sources

You can import a file from a local source, a remote network server, or another instance by providing a path and authentication information.

For file type import sets, you can select from a list of file retrieval methods, including FTP, FTPS, SCP, and HTTPS.

File retrieval methods

The following file retrieval methods are available to copy the file from where it resides to ServiceNow to be loaded into an import set.

After defining the files that are compatible for importing, define how the files can be imported.

Note: For the import to succeed, your FTP server and client must be set up for the same authentication mechanism that you select here. Refer to the following article for an explanation of the supported protocols http://en.wikipedia.org/wiki/FTPS.
Table 1. File Retrieval Methods
Attachment Attach a file to the record and import that file in an import set.
File Select a file to import from a local drive or a network location (this is unusual). Selecting this method provides a field to specify the network path. The system limits your selection to one of these file locations for increased security.
  • Tomcat home. For example, /Apache Tomcat.
  • Glide system home. For example, /<Tomcat home>/webapps/glide/itil/WEB-INF/.
  • Temporary directory. For example, /tmp or /var/tmp depending on how your system is configured.

If you need to specify an arbitrary network path, consider using a different file retrieval protocol such as FTP or SCP where you can secure the communications channel.

SFTP Retrieve a file using SFTP. Fields are provided for the server name and logon credentials.
FTP Retrieve a file from an FTP server in your network. Fields are provided for the server name and logon credentials.
FTPS (Auth SSL) An FTP Secure authentication command issued through the Secure Socket Layer (SSL) protocol. This method is also known as explicit FTP over SSL.
FTPS (Auth TLS) An FTP Secure authentication command issued through the Transport Layer Security (TLS) protocol. This method is also known as explicit FTP over TLS.
FTPS (Implicit SSL) In implicit security, the FTPS server defines a specific port for the client (990) to use for secure connections. This implementation of implicit security automatically begins with an SSL connection the moment an FTPS client connects to an FTPS server.
FTPS (Implicit TLS) In implicit security, the FTPS server defines a specific port for the client (990) to use for secure connections. This implementation of implicit security automatically begins with a TLS connection the moment an FTP client connects to an FTP server.
HTTP Retrieve a file using HTTP. FIelds are provided for the server name and logon credentials.

For HTTP and HTTPS protocols, the file path is automatically URL encoded. Do not specify a URL-encoded file path when using either of these protocols.

HTTPS Retrieve a file using HTTPS. Fields are provided for the server name and logon credentials. Use this method to transfer data between ServiceNow instances.

For HTTP and HTTPS protocols, the file path is automatically URL encoded. Do not specify a URL-encoded file path when using either of these protocols.

SCP The Secure Copy protocol (SCP) securely transfers files between a local and a remote host or between two remote hosts, using the Secure Shell (SSH) protocol.

FTP data source extended properties

Certain properties are available for customizing your FTP data source.

To enter these properties, you must configure the Data Source form and add the Properties field. When specifying more than one property, use a comma to separate each property.
Table 2. Available FTP Properties
Property name Description Default value Example custom value Data Source
connection.passive Flag that indicates the behavior of the FTP connection.

true = passive connection

false = active connection

true connection.passive=false FTP, FTPS
connection.timeout Number of milliseconds to wait before timing out the FTP connection attempt. 10000 connection.timeout=12000 FTP, FTPS
remove_file Flag that indicates whether the remote file is deleted after the import.

true = file deleted

false = file not deleted

true remove_file=false FTP, FTPS, SFTP
rename_file Flag that indicates whether the remote file is renamed after the import. Renamed files use the format <original file name>.imported.

true = file is renamed

false = file is not renamed

false rename_file=true FTP, FTPS, SFTP

File data source examples

These examples describe how to import various file types as data sources.

XLS data source file

XLS refers to the Microsoft Excel file format. This is the default format for spreadsheets created in Excel for versions 2003 and older. The .xlsx file format that is the default format for Excel 2007 is not currently supported. If importing a list from Excel 2007, it is necessary to save the file as .xls.

Note: All .xls files must use the 1900 date system as opposed to the 1904 date system as the latter will cause your dates to be imported with a year that is four years earlier than what your spreadsheet displays. Refer to MS Support for additional information about date systems.
Microsoft Excel demo data

XML data source file

XML data source definitions have an additional field to specify the "XPath for each row". This is an XPath expression to select the elements whose children will be converted to rows in the import set table. Each selected element will cause a row to be created in the import set table. The children of the selected element will be converted into columns in the row. For example, to import the asset information from the sample XML file below, the XPath expression should be specified as "/export/asset". This expression matches the 3 <asset> elements in the file, so 3 rows will be created. Although one could also specify "//asset" this expression is much less efficient for large files. It is strongly recommended that you use the absolute location path form for XPath expressions when using the XML loader. In other words, avoid expressions containing "//" unless strictly necessary.

Be careful not to specify an insufficiently restrictive XPath expression with a large XML file. For example, the expression "/export" would be wrong for the sample file below, since it matches the document (root) element. In general, you should never specify an XPath expression which matches the root element unless you want everything in the document to be made into a single row. Specifying an insufficiently restrictive XPath expression when attempting to load large XML files could result in lengthy processing times and incorrect output. For this reason, it is best to test and debug XML data source specifications with small XML files containing only a few rows worth of data. Once the specification has been tested, you can run it again with the full file.

Notice that the <userInfo> elements contained within <asset> contain child elements. This will cause a column called "userInfo" to be created containing XML for the userInfo element. If Expand child nodes is checked, individual columns will also be created for userInfo/lastName and userInfo/firstName. If Expand child nodes is not checked, only the userInfo XML column will be created.

Note that when loading data from an XML file, ServiceNow samples the first 10 records to determine what fields are required to hold the data. If none of the first 10 records specify a value for a field, ServiceNow does not add that field to the table that holds the imported data. Ensure that at least one of the first 10 records species a value for any fields being imported.

<?xml version= "1.0" encoding= "utf-8" ?><export><asset><assetTag>AT-01939</assetTag><type>Desktop</type><os>Windows 7 Professional</os><lastlogondate>12-07-2010 12:31:24</lastlogondate><userInfo><lastName>Loo</lastName><firstName>David</firstName></userInfo><isenabled>true</isenabled></asset><asset><assetTag>AT-53480</assetTag><type>Desktop</type><os>Windows 7 Professional</os><lastlogondate>09-07-2010 13:25:53</lastlogondate><userInfo><lastName>Merritt</lastName><firstName>Norris</firstName></userInfo><isenabled>true</isenabled></asset><asset><assetTag>AT-55782</assetTag><type>Desktop</type><os>Unknown</os><lastlogondate>01-01-1900 00:00:00</lastlogondate><userInfo><lastName>Currie</lastName><firstName>Mike</firstName></userInfo><isenabled>true</isenabled></asset></export>

CSV data source file

Character-separated value (CSV) files are used as a cross-compatible file format for transferring files across platforms. A CSV file is a text file that defines a grid, where columns are defined by commas and rows are defined by line breaks. To define precise spacing for importing strings you can optionally wrap text in quotes.

"user_name","name","email","sys_created_on","active"
"jared.laethem","Jared Laethem","jared.laethem@yourcompany.com","2008-02-24 22:21:32","true"
"jerrod.bennett","Jerrod Bennett","jerrod.bennett@yourcompany.com","2007-08-12 12:12:18","true"
"eric.schroeder","Eric Schroeder","eric.schroeder@yourcompany.com","2007-07-03 11:50:20","true"
"rob.woodbyrne","Rob Woodbyrne","rob.woodbyrne@yourcompany.com","2007-07-03 11:49:57","true"
"admin","System Administrator","admin@yourcompany.com","2007-07-03 11:48:47","true"
"christen.mitchell","Christen Mitchell","christen.mitchell@yourcompany.com","2007-05-16 15:26:42","true"
"rob.phillips","Rob Phillips","rob.phillips@yourcompany.com","2007-01-22 11:25:34","true"
"davin.czukowski","Davin Czukowski","davin.czukowski@yourcompany.com","2006-07-11 14:01:26","true"
"luke.wilson","Luke Wilson","luke.wilson@yourcompany.com","2006-02-07 15:29:48","true"
"bow.ruggeri","Bow Ruggeri","bow.ruggeri@yourcompany.com","2005-07-07 11:39:58","true"
"don.goodliffe","Don Goodliffe","don.goodliffe@yourcompany.com","2005-05-02 12:28:40","true"
"david.loo","David Loo","david.loo@yourcompany.com","2005-02-22 16:00:00","true"
"guest","Guest","guest@yourcompany.com","2004-05-01 17:00:00","true"
"fred.luddy","Fred Luddy","fred.luddy@yourcompany.com","2004-05-01 17:00:00","true"

When using CSV files, you can specify the encoding charset using the Properties field on the Data Source form. You may need to configure the Data Source form to see this field. For example, to use utf-8 encoding, enter charset=utf-8.

Processing custom CSV files

You can process CSV files that are delimited by a character other than commas.

About this task

This is an advanced step to create a CSV import. Normally, you would upload the data and import it directly using System Import Sets, which will create this CSV data source for you automatically.

Procedure

  1. Create the data source record.
  2. Attach the CSV file to the data source.
  3. Customize the Data Source form and add the CSV Delimiter field.
  4. Enter the character you want to use as the CSV file delimiter, such as the pipe symbol (|).
  5. Test load the data source.
    Data source