Powershell Script to Upload file to table

[Tim Darrach1]

We have been looking for a solution for automating an upload from an Excel doc to a table as if just went to the table and did a right click > Import. We had tried setting it up originally as a scheduled import and setting up the Data Source as Type: File, Format: Excel, File Retrieval Method: File and couldn't get that to work. Tried reading some online sources and they made it seem like this import option doesn't actually work so we moved on to trying it with Powershell instead. If someone knows how to make that work instead this could save us some trouble.

So we moved onto attempting an upload through a Powershell script. I've got it to the point where it will run and connect to our instance but in our Import Log we see the error message

"Attachment could not be successfully extracted." from Source: ImportProcessor. 

So I have a few questions about making this work as a Powershell script. Apologies if these seem amateur, I'm just learning this from scratch as I go. 

We have used this as our URL using the import set table "u_imp_tmpl_u_luis_custom_test". Should the import set table be used or should it be the actual table we mean to have the data sent to?

https://xxxxxxxxx.service-now.com/sys_import.do?sysparm_import_set_tablename=u_imp_tmpl_u_luis_custom_test&sysparm_transform_after_load=true

How does ServiceNow expect the body to look? Can I simply import it as an .xlsx? Would it have to be converted to .csv or .json first? Would the body of the upload have to be converted to into some format SNOW would recognize? 

To insert new records on a table is -method POST or PUT recommended? 

[Ashutosh Munot1 · Accepted solution]

Hi,

POST method is used to create record into import set table.

you are using the right import set API. XLSX is proper format which you can use.

But the only thing is are you attaching this file to a data source and then the data source is running?

We are fetching an csv file from MID Server which automatically goes into import set table. But we are fetching it not like your case. You want to push it.

Thanks,
Ashutosh

[Tim Darrach1]

Maybe I didn't explain correctly. We attempted doing this using a scheduled import from a data source at first but when we couldn't find a way to do that by just selecting an Excel file we skipped past that altogether and moved onto trying to do it entirely from a Powershell script. Is it possible to set up an import Data Source to point to an Excel file and upload that way? 

[Ashutosh Munot1]

Hi,

You can create a data source for excel. Then you can import the excel file from PowerShell and then attach that file to data source and trigger the data source using script. This will help you.

Thakns,
Ashutosh

[Tim Darrach1]

Since we already had the scheduled import/data source structure setup I took another look at using Powershell to upload the Excel file to the data source as an attachment and that worked. So we're all set. We'll just run the import from the data source attachment. Thanks for the help!