I have a PowerShell script that reads from an Excel file. It stores data from particular cells/columns, converts to JSON and then sends it via REST to my Wordpress install.
The issue I'm having is that when attempting to run the script using data taken from Excel, it presents the error
Malformed UTF-8 characters, possibly incorrectly encoded
#Declare the file path and sheet name
$file = "P:\file.xlsx"
$sheetName = "IN PRODUCTION"
###############################################
# #
# EXCEL FUNCTIONS #
# #
###############################################
#Create an instance of Excel.Application and Open Excel file
$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open($file)
$sheet = $workbook.Worksheets.Item($sheetName)
$objExcel.Visible = $false
#Count max row
$rowMax = ($sheet.UsedRange.Rows).count
#Declare the starting positions
$rowName,$colName = 1,1
$rowSignOff,$colSignOff = 1,2
$rowReceived,$colReceived = 1,3
$rowBuildStart,$colBuildStart = 1,4
$rowBuildEnd,$colBuildEnd = 1,5
$rowShipping,$colShipping = 1,6
$rowBuiltBy,$colBuiltBy = 1,7
$rowQA,$colQA = 1,8
$rowCage,$colCage = 1,9
$rowComment,$colComment = 1,10
$rowStatus,$colStatus = 1,11
$build = @()
#Loop to get values and store it
for ($i=1; $i -le $rowMax-1; $i++)
{
$name = $sheet.Cells.Item($rowName+$i, $colName).Text
$signoff = $sheet.Cells.Item($rowSignOff+$i, $colSignOff).Text
$received = $sheet.Cells.Item($rowReceived+$i, $colReceived).Text
$buildstart = $sheet.Cells.Item($rowBuildStart+$i, $colBuildStart).Text
$buildend = $sheet.Cells.Item($rowBuildEnd+$i, $colBuildEnd).Text
$shipping = $sheet.Cells.Item($rowShipping+$i, $colShipping).Text
$builtby = $sheet.Cells.Item($rowBuiltBy+$i, $colBuiltBy).Text
$qa = $sheet.Cells.Item($rowQA+$i, $colQA).Text
$cage = $sheet.Cells.Item($rowCage+$i, $colCage).Text
$comment = $sheet.Cells.Item($rowComment+$i, $colComment).Text
$status = $sheet.Cells.Item($rowStatus+$i, $colStatus).Text
$build += [PSCustomObject]@{
name = $name
start = $buildstart
end = $buildend
by = $builtby
notes = $comment
}
}
###############################################
# #
# POST FUNCTIONS #
# #
###############################################
$content = [PSCustomObject]@{
staging_fields = @{
staging_repeater=$build
}
}
$json = $content | ConvertTo-Json -Depth $([int32]::MaxValue)
Invoke-RestMethod -Uri $uri -Method POST -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)} -ContentType "application/json" -Body $json
Write-Host $json
#Close excel file
$objExcel.Quit()
The output of Write-Host $json is as below
{
"staging_fields": {
"staging_repeater": [
{
"name": "Test 1",
"start": "19/12/2016",
"end": "09/01/2017",
"by": "JM",
"notes": ""
},
{
"name": "Test 2",
"start": "01/01/2017",
"end": "11/01/2017",
"by": "JC",
"notes": ""
},
{
"name": "Test 3",
"start": "17/01/2017",
"end": "01/02/2017",
"by": "JM",
"notes": ""
}
]
}
}
Pasting this into Postman and sending a POST request produces no errors and successfully adds to my WordPress site.
If it helps, the full error is below
Invoke-RestMethod : {"code":"rest_invalid_json","message":"Invalid JSON body passed.","data":{"status":400,"json_error_code":5,"json_error_message":"Malformed UTF-8
characters, possibly incorrectly encoded"}}
At L:\\Untitled1.ps1:98 char:1
+ Invoke-RestMethod -Uri $uri -Method POST -Headers @{Authorization=("B ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
If I were to replace the variables with strings such as "Test", the script works correctly. Below is an example.
#Loop to get values and store it
for ($i=1; $i -le $rowMax-1; $i++)
{
$name = $sheet.Cells.Item($rowName+$i, $colName).Text
$signoff = $sheet.Cells.Item($rowSignOff+$i, $colSignOff).Text
$received = $sheet.Cells.Item($rowReceived+$i, $colReceived).Text
$buildstart = $sheet.Cells.Item($rowBuildStart+$i, $colBuildStart).Text
$buildend = $sheet.Cells.Item($rowBuildEnd+$i, $colBuildEnd).Text
$shipping = $sheet.Cells.Item($rowShipping+$i, $colShipping).Text
$builtby = $sheet.Cells.Item($rowBuiltBy+$i, $colBuiltBy).Text
$qa = $sheet.Cells.Item($rowQA+$i, $colQA).Text
$cage = $sheet.Cells.Item($rowCage+$i, $colCage).Text
$comment = $sheet.Cells.Item($rowComment+$i, $colComment).Text
$status = $sheet.Cells.Item($rowStatus+$i, $colStatus).Text
$build += [PSCustomObject]@{
name = "test"
start = "test"
end = "test"
by = "test"
notes = "test"
}
}
It appears the data coming from Excel is not coming through with the correct character encoding. This is where I'm at a loss.
