How to push CSV file into import set via web service?

jamesmcwhinney · ‎07-07-2020

I am looking for a way to send a 1MB CSV file to ServiceNow and have it processed via an import set.

Does anyone know if there is functionality available out of the box for this? Or do I need to write my own scripted SOAP service or rest service etc?

Thanks!

jamesmcwhinney · ‎07-08-2020

Thanks for your replies, they were helpful in finding the solution.

I created a data source:

type: file
Format: CSV
file retrieval method: Attachment

I then used .net c# to upload the file as an attachment to the data source using the attachment REST API.

A few key things I tripped over:

ServiceNow user account must have the "import_admin" role
The HTTPClient must be configured to use TLS 1.2
Use of "AggregateException" to understand the error messages beneath "One or more errors occurred".

The C# code is below for reference:

using System;
using System.Globalization;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;


namespace SNFileAttachmentTest
{
    class Program
    {
        static void Main(string[] args)
        {
            //Configure Parameters
            string ServiceNowURL = "https://yourinstancename.service-now.com";
            string filePath = "C:\\temp\\YourCSVDataSourceFile.txt";
            string tableName = "sys_data_source";
            string tableSysId = "thedatasourcesysid";
            string username = "yourserviceaccountusername"; //User must have ServiceNow role "import_admin"
            string password = "yourserviceaccountpassword";

            
            

            //Create Client
            using (var client = new HttpClient())
            {
                //Configure Client for TLS 1.2
                System.Net.ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

                //Set Target Address
                client.BaseAddress = new Uri(ServiceNowURL);

                //Set Default Headers
                string authData = string.Format("{0}:{1}", username, password);
                string authHeaderValue = Convert.ToBase64String(Encoding.UTF8.GetBytes(authData));
                client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", authHeaderValue);
                client.DefaultRequestHeaders.Add("Accept", "*/*");

                //Create Form Data
                using (var content = new MultipartFormDataContent("Upload----" + DateTime.Now.ToString(CultureInfo.InvariantCulture)))
                {
                    var array = filePath.Split('\\');
                    var filename = array[array.Length - 1].ToString();
                    var fileStream = new ByteArrayContent(File.ReadAllBytes(filePath));
                    fileStream.Headers.Remove("Content-Type");
                    fileStream.Headers.Add("Content-Type", "application/octet-stream");
                    fileStream.Headers.Add("Content-Transfer-Encoding", "binary");
                    fileStream.Headers.Add("Content-Disposition", $"form-data;name=\"uploadFile\"; filename=\"{filename}\"");
                    content.Add(new StringContent(tableName), "\"table_name\"");
                    content.Add(new StringContent(tableSysId), "\"table_sys_id\"");
                    content.Add(fileStream, "uploadFile");

                    try
                    {
                        //Post Form Data and check response
                        var response = client.PostAsync("api/now/attachment/upload", content).Result;
                        Console.WriteLine("StatusCode:" + response.StatusCode.ToString());
                        Console.WriteLine("Response:" + response.Content.ReadAsStringAsync().Result.ToString());
                    }
                    catch (AggregateException err)
                    {
                        //If you dont use "AggregateException" then you may get the message "One or more errors occurred".
                        //https://stackoverflow.com/questions/51533760/aggregateexception-one-or-more-errors-occurred-an-error-occurred-while-sending
                        foreach (var errInner in err.InnerExceptions)
                        {
                            Console.WriteLine(errInner); 
                        }
                    }
                }
            }

            
            Console.ReadLine();

        }
    }
}

View solution in original post