question Re: Powershell Script to Upload file to table in Developer forum

Powershell Script to Upload file to table

Tim Darrach1 — Wed, 08 Jul 2020 13:49:28 GMT

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?

Re: Powershell Script to Upload file to table

Ashutosh Munot1 — Wed, 08 Jul 2020 15:31:11 GMT

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

Re: Powershell Script to Upload file to table

Tim Darrach1 — Wed, 08 Jul 2020 19:48:17 GMT

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?

Re: Powershell Script to Upload file to table

Ashutosh Munot1 — Thu, 09 Jul 2020 09:26:11 GMT

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

Re: Powershell Script to Upload file to table

Tim Darrach1 — Thu, 09 Jul 2020 12:53:24 GMT

Sorry I'm confused. I thought that we would use either the Import module with a data source or Powershell. Just one or other other. Do you mean to set up the Data Source with File Retrieval Method set to Attachment and then use Powershell to attach the file to the Data Source record?

Re: Powershell Script to Upload file to table

Swapnil Soni1 — Thu, 09 Jul 2020 12:59:34 GMT

Hey,

Check those links might this helps you

https://community.servicenow.com/community?id=community_article&sys_id=df3caa61dbd0dbc01dcaf3231f9619aa

https://community.servicenow.com/community?id=community_question&sys_id=6513cfa1dbd8dbc01dcaf3231f9619f1

Please mark correct or helpful.

Thanks

Re: Powershell Script to Upload file to table

Tim Darrach1 — Thu, 09 Jul 2020 15:20:30 GMT

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!

Re: Powershell Script to Upload file to table

KLee19 — Thu, 06 Aug 2020 00:26:13 GMT

Hi Tim, I am trying to do something similar but I am posting to the import set table. I get the attachment could not be extracted error. Any suggestions?

Re: Powershell Script to Upload file to table

Tim Darrach1 — Thu, 06 Aug 2020 13:43:44 GMT

I think we ran into that issue at one point. The only way I could find to work was uploading the file as an attachment to the Data Source record and then setting the Scheduled Import to import from attachment directly. Here is what we used, however this was for an Excel file, not a CSV so it might take some adjustment.

# Eg. User name="admin", Password="admin" for this code sample.
$user = ""
$pass = ""

# Build auth header
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user, $pass)))

# Set proper headers
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add('Authorization',('Basic {0}' -f $base64AuthInfo))
#$headers.Add('Accept','application/json')



# Specify endpoint uri - this is the Data Source record for the import
$uri = "https://xxxxxxx.service-now.com/api/now/attachment/file?table_name=sys_data_source&table_sys_id=e977e602dbc91850dffc9a26db9619b4&file_name=u_luis_custom_test_input.xlsx"


# Specify HTTP method
$method = "post"


#Get the attachment file to send to SNOW to be imported
$filePath = "C:\Users\xxxxxxx\test_input.xlsx"



$contentType = "multipart/form-data"
#contentType = "application/vnd.ms-excel"


[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12


# Send HTTP request
$response = Invoke-RestMethod -Headers $headers -Method $method -Uri $uri -ContentType $contentType -InFile $filePath

# Print response
$response.RawContent

Re: Powershell Script to Upload file to table

shilpi sharma — Tue, 10 Jan 2023 16:16:26 GMT

HI Tim,

I am using the below script its just my requirement is to read data from CSV. But get syntax error while running action in flow designer

# Eg. User name="admin", Password="admin" for this code sample.
$user = "Integration"
$pass = "Micromax123#"

# Build auth header
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user, $pass)))

# Set proper headers
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add('Authorization',('Basic {0}' -f $base64AuthInfo))
#$headers.Add('Accept','application/json')

# Specify endpoint uri - this is the Data Source record for the import
$uri = "https://xxxxx.service-now.com/api/now/attachment/file?table_name=sys_data_source&table_sys_id=532273991bd46dd46440eca7b04bcbe4&file_name=splunk_data_against_AD.csv"

# Specify HTTP method
$method = "post"

#Get the attachment file to send to SNOW to be imported
$filePath = "E:\xxxxx\splunk_data_against_AD.csv"

$contentType = "multipart/form-data"
#contentType = "application/text/csv"

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Send HTTP request
$response = Invoke-RestMethod -Headers $headers -Method $method -Uri $uri -ContentType $contentType -InFile $filePath

# Print response
$response.RawContent

Appericate early response and help