Post CSV or Excel files directly to an import set

An administrator can post CSV or Excel files directly to instance.

Before you begin

Roles required: import_set_loader

About this task

The sys_import.do target lets you dynamically upload a CSV or Excel file into the import set table specified by the sysparm_import_set_tablename parameter. You must specify a name that matches an existing import set table name, if it does not exist, pre-create it by doing a manual import. The sysparm_transform_after_load=true parameter makes the CSV transform execute immediately, if a transform map exists.

Procedure

Use the following post syntax: https://instance.service-now.com/sys_import.do?sysparm_import_set_tablename=table_name&sysparm_transform_after_load=true&uploadfile=path/to/file.csv

Posting a CSV file - Perl example

An example using Perl to post a CSV file with basic auth credentials.

# file: uploadafile.pl
# call me like this:
# uploadafile.pl --url="https://instance.service-now.com/sys_import.do?sysparm_import_set_tablename=dloo_test&sysparm_transform_after_load=true" 
# --uploadfile=/Users/davidloo/Desktop/test_files/test_users.csv
#
# the "sysparm_transform_after_load=true" parameter instructs the import set to transform immediately after loading
#
use strict;
use LWP::UserAgent;
use HTTP::Request::Common;
use Getopt::Long;
use File::Basename;
 
my ( $o_url, $o_fqn );
GetOptions(
    "url=s"        => \$o_url,
    "uploadfile=s" => \$o_fqn,
);
 
# mandatory arguments: url
&usage unless ( $o_url && $o_fqn );
 
my $url   = $o_url;
my $fname = $o_fqn;
 
# put timeouts, proxy etc into the useragent if needed
my $ua  = LWP::UserAgent->new();
 
# setup basic authentication credentials
$ua->credentials(
  'demo.service-now.com:443',
  'Service-now',
  'admin' => 'admin'
);
 
my $req = POST $url, Content_Type => 'form-data',
        Content      => [
                submit => 1,
                upfile =>  [ $fname ]
        ];
my $response = $ua->request($req);
 
 
 
if ($response->is_success()) {
    print "OK: ", $response->content;
} else {
    print $response->as_string;
}
 
exit;
 
sub usage {
        printf "usage: %s --url=%s --uploadfile=%s\\n",
                basename($0),'https://....','c:/data/test.csv';
        exit
}

Posting a CSV file - Java example

An example using the Java Apache HttpClient class to post a CSV file with basic auth credentials.

Attention: The Apache HttpClient may limit the amount of data you can import in a single transaction. This example is meant as a starting point and should not be used in production.
HttpClient httpclient = new HttpClient();
PostMethod post = new PostMethod("https://instance-name.service-now.com/sys_import.do?sysparm_import_set_tablename=u_test_upload&sysparm_transform_after_load=true");
 
try {
  Credentials defaultcreds = new UsernamePasswordCredentials("admin", "admin");
  httpclient.getState().setCredentials(AuthScope.ANY, defaultcreds); // Prepare HTTP post
  httpclient.getParams().setAuthenticationPreemptive(true);
 
  File targetFile = new File("/Users/davidloo/Desktop/test_files/nodeinfo2736820198834983863.csv");
  Part[] parts = { new FilePart(targetFile.getName(), targetFile) };
 
  post.setRequestEntity(new MultipartRequestEntity(parts, post.getParams()));
 
  int result = httpclient.executeMethod(post);
 
  // Display status code
  System.out.println("Response status code: " + result);
 
  // Display response
  System.out.println("Response body: "+post.getResponseBodyAsString());
} catch(Exception e) {
  System.err.println(e.getMessage());
} finally {
  // Release current connection to the connection pool
  // once you are done
  post.releaseConnection();
}