Uploading Excel file from Powershell script corrupts data

[Tim Darrach1]

This is a followup to a previous community case I had raised. We have setup a scheduled import to point to a data source record which reads it's attachment and if we manually upload an Excel file to the attachment, the import will run correctly. But we are now trying to upload that attachment to the record using a Powershell script. We have it now so that we can run the script without any error and the Excel file will show up on the Data Source record as an attachment but when we do it this way, the file becomes corrupted. Even if we download the file back from SNOW, it can no longer be opened in Excel. 

We started off this script using the Powershell sample from REST API Explorer and made a few tweaks to it. I think most of the script should be confirmed as correct since it is successfully uploading to the correct record in SNOW but I'm assuming I have a problem with something like the Invoke-RestMethod headers or the content type. Could anyone who has a little familiarity with Powershell assist with this, please?

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


# Specify HTTP method
$method = "post"


# Specify endpoint uri - this is the Data Source record for the import
$uri = "https://xxxxxxxxx.service-now.com/api/now/attachment/file?table_name=sys_data_source&table_sys_id=e977e602dbc91850dffc9a26db9619b4&file_name=Luis_Import.xlsx&encryption_context=multipart%2Fform-data"



#Get the attachment file to send to SNOW to be imported
$filePath = ".....\u_luis_custom_test_input.xlsx"
$attachment = Get-Content -Path $filePath -Encoding Byte


$contentType = "application/vnd.ms-excel"


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

[Michael Nau · Accepted solution]

Try this two-liner, just to see if you can get past the issue of the file getting corrupted:

$Uri = "https://[instance].service-now.com/api/now/attachment/file?table_name=sys_data_source&table_sys_id=[sys_id]&file_name=test.xlsx"

Invoke-WebRequest -Method Post -Uri $Uri -ContentType multipart/form-data -Credential (Get-Credential) -InFile C:\test.xlsx

[Tim Darrach1]

Hey Michael, you're the man. I had gotten a ton of replies from other sources and couldn't get this figured out. I think the problem was that I had been using the -Body property instead of -InFile. That seems to have fixed it. Cheers mate!

[Michael Nau]

Happy to help!

The only downside is you'll end up with an XLSX with a content type of multipart/form-data. That doesn't seem to cause any problems though.

Here's another hint if you want to schedule the script - to store the credentials for your instance, do this:

• log on to your machine with the user that will run the script
• run Get-Credential | Export-Clixml -Path .\credential.xml
• this creates a credential file with an encrypted password
• supply credentials to Invoke-WebRequest like this: -Credential (Import-Clixml -Path .\credential.xml)
• note that only the user who created the file can import it, and only on the machine where it was created - this is the most secure way to store credential information