英文:
How to resolve garbled code when using powershell to execute psql and the Copy command?
问题
我正在编写PowerShell脚本，使用psql复制命令将客户端机器上的“csv”文件复制到远程PostgreSQL数据库表格中。
复制命令执行成功，但是一些日文字符变成了乱码。
我已经为客户端和控制台的标准输入和输出设置了编码，但仍然出现了乱码问题。
[System.Console]::OutputEncoding = [Text.UTF8Encoding]::UTF8
[System.Console]::InputEncoding = [Text.UTF8Encoding]::UTF8
$env:PGPASSWORD = 'pwd';
foreach($file in Get-ChildItem .\Import -Filter *.csv){
$table = $file.BaseName
$Truncate_Sql = "truncate table $table;"
$Result = & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c $Truncate_Sql
Write-Host $Result $table "Ok."
$Result = Write-Output (Get-Content -Path $file.FullName -Encoding utf8) | & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c "set client_encoding=UTF8;COPY $table FROM STDIN WITH(FORMAT CSV,HEADER TRUE, ENCODING 'UTF8')"
$Result
}
例如，csv（utf8编码）的内容如下所示：
header1,header2
1001,その他
当复制到PostgreSQL后，我使用pgAdmin查看表格数据。显示的内容如下：
| header1 | header2 |
|---|---|
| 1001 | ??? |
英文:
I'm writing powershell script to copy "csv" from client machine to remote postgre database table with psql copy command.
The copy command executed successfully, but some Japanese characters have become garbled.
I have set the encoding for client and console stdin and stdout, but garbled code still occurred.
[System.Console]::OutputEncoding = [Text.UTF8Encoding]::UTF8
[System.Console]::InputEncoding = [Text.UTF8Encoding]::UTF8
$env:PGPASSWORD = 'pwd';
foreach($file in Get-ChildItem .\Import -Filter *.csv){
$table = $file.BaseName
$Truncate_Sql = "truncate table $table;"
$Result = & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c $Truncate_Sql
Write-Host $Result $table "Ok."
$Result = Write-Output (Get-Content -Path $file.FullName -Encoding utf8) | & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c "set client_encoding=UTF8;COPY $table FROM STDIN WITH(FORMAT CSV,HEADER TRUE, ENCODING 'UTF8')"
$Result
}
For example, the csv(utf8 encoding) content looks like the following
header1,header2
1001,その他
When copied to postgre, I use pgAdmin to view the table data. It shows:
| header1 | header2 |
|---|---|
| 1001 | ??? |
答案1
得分: 0
我解决了我的问题。我混淆了PostgreSQL的COPY命令和psql的\COPY指令。
实际上，我需要使用\COPY指令来执行copy命令，然后我可以直接从文件复制而不是写入stdin。
[System.Console]::OutputEncoding = [Text.UTF8Encoding]::UTF8
[System.Console]::InputEncoding = [Text.UTF8Encoding]::UTF8
$env:PGPASSWORD = 'pwd';
foreach($file in Get-ChildItem .\Import -Filter *.csv){
$table = $file.BaseName
$Truncate_Sql = "truncate table $table;"
$Result = & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c $Truncate_Sql
Write-Host $Result $table "Ok."
$Result = & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c "\COPY $table FROM $($file.FullName) WITH(FORMAT CSV,HEADER TRUE, ENCODING 'UTF8')"
$Result
}
英文:
I resolved my question. I confused the Postgre SQL COPY command with psql \COPY instruction.
Actually, I need to use \COPY instruction to execute copy command, then I can directly copy from file instead of writing to stdin.
[System.Console]::OutputEncoding = [Text.UTF8Encoding]::UTF8
[System.Console]::InputEncoding = [Text.UTF8Encoding]::UTF8
$env:PGPASSWORD = 'pwd';
foreach($file in Get-ChildItem .\Import -Filter *.csv){
$table = $file.BaseName
$Truncate_Sql = "truncate table $table;"
$Result = & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c $Truncate_Sql
Write-Host $Result $table "Ok."
$Result = & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c "\COPY $table FROM $($file.FullName) WITH(FORMAT CSV,HEADER TRUE, ENCODING 'UTF8')"
$Result
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论