Import CSV/Excel file using Postman

Using the built-in /sys_import.do 'API' it's possible to import CSV or Excel files directly. There's no need to use a MID Server for this, it can be done from any host where you have the relevant tools (either a script that can do POST requests or a program such as Postman). Note that there's also nothing stopping you doing this import from a MID Server host. 

 

Note: the file content itself must be uploaded as a POST request, as a multi-part attachment. The name (key) of this parameter can be called anything you like.

 

Pls note that there's also nothing stopping you doing this import from a MID Server host. Those who are interested to work on this, give a try in your free time and let me know if any questions 😊

 

Here are the simple steps can be followed to achieve this:

1. Create a simple test CSV file
2. Upload the CSV file once manually using 'Load Data' on the instance, to create the necessary Import Set Staging table (e.g. u_mytestimport)
3. Delete the uploaded data from the Import Set Staging table (e.g. u_mytestimport)
4. POST request to https://<PDI Name>.service-now.com/sys_import.do?sysparm_import_set_tablename=u_mysampleimport&sysparm_transform_after_load=true  (Where u_mytestimport is the Import Set Staging Table, this must have already been created by manually uploading a CSV with the same columns once)
5. Authorization: Basic authentication
6. Body: form-data (for multi-part attachment). Create a parameter (this will be a POST parameter) with Key set to anything you like ('uploaded_csv_file', 'somefile' etc) of type File (select from dropdown) and for Value set the location of the file
7. Send, the data will now appear in the Import Set Staging table

Snapshots from my PDI instance

Table created using Load Data from SN AES:

 

 

 

 

 

 

 

 

 

Postman configuration to post a csv file:

 

 

 

 

 

 

 

 

 

Sample Data.csv file:

 

 

 

 

 

 

 

 

 

 

After calling the API service, the records from the used csv file in the API request are being stored in the staging table:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Similarly, we can build another API call to retrieve the data from the stage table and here is the Postman configuration:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To connect to my PDI => I took Basic authentication like my user/pwd.

 

References:

1) https://community.servicenow.com/community

2) https://developer.service-now.com/