错误格式错误的UTF-8字符，可能编码错误 [英] Error Malformed UTF-8 characters, possibly incorrectly encoded
问题描述
我有一个从Excel文件读取的PowerShell脚本.它存储来自特定单元格/列的数据，转换为JSON，然后通过REST将其发送到我的Wordpress安装.
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.
我遇到的问题是，当尝试使用从Excel中获取的数据运行脚本时，会出现错误
The issue I'm having is that when attempting to run the script using data taken from Excel, it presents the error
格式错误的UTF-8字符，可能编码错误
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()
Write-Host $ json 的输出如下
{
"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": ""
}
]
}
}
将其粘贴到Postman中并发送POST请求不会产生任何错误，并成功将其添加到我的WordPress网站中.
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如果要用诸如"Test"之类的字符串替换变量，则脚本可以正常工作.下面是一个示例.
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"
}
}
似乎来自Excel的数据未以正确的字符编码传递.这是我不知所措的地方.
It appears the data coming from Excel is not coming through with the correct character encoding. This is where I'm at a loss.
推荐答案
我是个白痴.
在excel文件中，自动更正已将 cafe 更改为Café，这是导致此问题的原因.只需重命名即可解决此问题.
Within the excel file, autocorrect had changed cafe to Café which was the cause of the issue. Simply renaming resolved this for me.
这篇关于错误格式错误的UTF-8字符，可能编码错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
