We are trying to update a table using /sys_import.do?sysparm_import_set_tablename=u_kronos_department_import. I have the below Powershell, and when run I do get an HTTP 200 response, but the file does not appear to load. I can run an identical upload in POSTMAN and it is successful.
# Eg. User name="admin", Password="admin" for this code sample.
$user = "usr"
$pass = "pwd"
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
# 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')
#$headers.Add('Content-Type','application/json')
$headers.Add('Content-Type','multipart/form-data')
# Specify endpoint uri
$uri = "https://DEV.service-now.com/sys_import.do?sysparm_import_set_tablename=u_kronos_department_import&sysparm_transform_after_load=true"
# Specify HTTP method (POST, PATCH, PUT)
$method = "POST"
#Standard method?
<#
$body = @{
"file"=(Get-Item -Path "Departments.csv")
}
$bodyJson = $body |convertto-json
# Send HTTP request
$response = Invoke-WebRequest -Headers $headers -Method $method -Uri $uri -Body $bodyJson -ContentType "application/json"
#>
#Infile Method, https://community.servicenow.com/community?id=community_question&sys_id=d3707023dbaceb8023f4a345ca961949&view_source=searchResult
$fileToAttach = "Departments.csv"
$response = Invoke-WebRequest -Headers $headers -Method $method -Uri $uri -InFile $fileToAttach
# Print response
$response
It took me much longer than I would like to admit, but I stubbornly wanted to use PowerShell with no valid reason. I finally found the solution after using Fiddler to compare the API request with Postman/cURL and PowerShell.
Fiddler showed that Postman/cURL had a boundary and Content-Disposition along with the actual data:
Postman:
PowerShell was missing what is in the 2 screenshots above. After Googling "invoke-restmethod missing content-type boundary", I came across the solution on StackOverflow
https://stackoverflow.com/a/50255917
My file was a csv, so the only thing I had to change was to set the content type in the body to 'text/csv' instead of application/octet-stream.
Final code I am using below:
$SNowEnvironment = 'dev'
$SNowImportTable = 'u_imp_table_name'
$SNowURI = "https://<myinstancename>.service-now.com/sys_import.do?sysparm_import_set_tablename=$SNowImportTable&sysparm_transform_after_load=true"
$SNowHeaders = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
#Most people will probably use basic auth here instead. That code can be found in the REST API Explorer if needed
$SNowHeaders.Add('Authorization',('Bearer {0}' -f $OAuthAccessToken))
$SNowHeaders.Add('Accept','application/json')
$FileBytes = [System.IO.File]::ReadAllBytes("C:\Data.csv")
$FileEnc = [System.Text.Encoding]::GetEncoding('UTF-8').GetString($FileBytes)
$Boundary = [System.Guid]::NewGuid().ToString()
$LF = "`r`n"
$BodyLines = (
"--$Boundary",
"Content-Disposition: form-data; name=`"csvfile`"; filename=`"Data.csv`"",
"Content-Type: text/csv$LF",
$FileEnc,
"--$Boundary--$LF"
) -join $LF
try {
Invoke-Restmethod -Method POST -Headers $SNowHeaders -ContentType "multipart/form-data; boundary=`"$Boundary`"" -Uri $SNowURI -Body $BodyLines -ErrorAction Stop
}
catch {
$_
throw "Unable to import Data"
}One thing that really threw me off is the documentation says that only import_set_loader is required, but it seems as though import_admin or a custom ACL is required (see accepted answer below). A custom ACL is probably preferred depending on the situation.
