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? 

[KLee19]

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?

[Tim Darrach1]

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

[shilpi sharma]

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=532273..."

# 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

[Tim Darrach1]

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? 

[Swapnil Soni1]

Hey,

Check those links might this helps you

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

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

Please mark correct or helpful.

Thanks