PowerShell导出CSV因哈希字段变为ASCII编码，如何强制输出UTF-8？

我编写了PowerShell脚本，将SQL Server中PUB_FINANCE库gdm架构下的视图数据导出为.ldr格式的CSV文件，供下游财务应用使用。脚本内容如下：

Write-Host 1
#############################################################
#
# - Name: Create_Ldr_Files_GDM
#
# - Purpose: This script exports GDM tables to csv for WKSaaas
#
#############################################################
# - Change history:
# - Date      Intials    Notes
# - --------  -------    ------------------------------------
# - 20231204  EOS      Initial Version
# - 
#############################################################

#############################################################
#                     Functions                             *
#############################################################
$RepoDsn        = ${env:WSL_META_DSN}
$p_gdm_ldr_param_path   = "Path_GDM_AC"
$p_gdm_ctl_param_path   = "Path_GDM_ctl_AC"

# save current CUlture info
$OldCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture

# start trap to make sure it reverts should anything break
trap {
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
}

# set the new Culture.
[System.Threading.Thread]::CurrentThread.CurrentCulture = [cultureInfo]::GetCultureInfoByIetfLanguageTag('en-US')



function get_param_value ($p_gdm_ldr_param_path, $RepoDsn){
    $sql = "select [dbo].[WsParameterReadF] ('$p_gdm_ldr_param_path') as src_path"
    $conn = New-Object System.Data.Odbc.OdbcConnection
    $conn.ConnectionString = "DSN=$RepoDsn;Uid=$RepoUser;Pwd=$RepoPass"
    $conn.open()
    $cmd = [system.data.odbc.odbcCommand]::new($sql,$conn) 
    $adapter = [system.data.odbc.odbcDataAdapter]::new($cmd) 
    $dr = [system.data.dataSet]::new() 
    [Void]$adapter.Fill($dr) 
    $conn.close()
    $path_file = $dr.Tables[0].Rows[0]["src_path"]
    
    return $path_file
}

$server =  ${env:WSL_META_SERVER} 
$database = "PUB_FINANCE"
$tablequery = "SELECT TABLE_NAME FROM [PUB_FINANCE].INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='gdm' AND TABLE_TYPE = 'VIEW' AND (TABLE_NAME NOT LIKE 'mapping%' and TABLE_NAME != 'vw_gdm_ACBS_LodAmount')"
$fileDirectory =  get_param_value $p_gdm_ldr_param_path $RepoDsn 
$ctlFileDirectory = get_param_value $p_gdm_ctl_param_path $RepoDsn

#Delcare Connection Variables
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
 
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection
 
#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

# delete contents of output directory

Get-Childitem -Path $fileDirectory | Remove-Item

# Loop through all tables and export a CSV of the Table Data
foreach ($Row in $DataSet.Tables[0].Rows)
{
    $queryData = "SELECT * FROM [PUB_FINANCE].[gdm].[$($Row[0])]"

    #Specify the output location of your dump file
    $extractFile = "$($fileDirectory)\$($Row[0]).ldr"

    #have to remove the vw_ from the file name as data is coming from staging out views but filename needs to be the same as the table
    #eg vw_COUNTERPARTY.ctl -> COUNTERPARTY.ctl
    $parseExtractFile =  $extractFile.replace('vw_','')
     
    $command.CommandText = $queryData
    $command.Connection = $connection
 
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $connection.Close()
         
   #Create csv with headers only if no data is found in the gdm table
        if ($DataSet.Tables[0].Rows.Count -eq 0) {
  #$header = ""
   # foreach ($col in $DataSet.Tables[0].Columns) {
   #     $header += $col.ColumnName +","
   # }
     Out-File $parseExtractFile}
#} 
  else {
   $DataSet.Tables[0]  |  ConvertTo-Csv  -NoTypeInformation | Select-Object -Skip 1| Set-Content $parseExtractFile
  }
        
}

# Copy ctl files to output directory

Copy-Item -Path $ctlFileDirectory\*.ctl  -Destination $fileDirectory  

# reverts culture to the original
[System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture

$RESULT_CODE = 1
$RESULT_MSG = "ctl and loaded files have been created"
Write-Output $RESULT_CODE
Write-Output $RESULT_MSG

其中一个字段通过以下SQL语句生成哈希值：

CAST(HASHBYTES('SHA2_256',
               COALESCE(CAST(Dim_Protection_Received.Protection_Data_Source_Code AS VARCHAR(MAX)),'null') +'||'+ 
               COALESCE(CAST(Dim_Protection_Received.Protection_Id AS VARCHAR(MAX)),'null')
               ) AS BINARY(32)) as ide_position_ref 

该哈希字段导致生成的文件编码变为ASCII，被财务应用拒绝，要求文件必须为UTF-8编码。当前文件内容显示异常，期望输出正确的UTF-8编码文件，请问如何在PowerShell中避免编码切换为ASCII？

1. 强制指定UTF-8编码输出 #

PowerShell中Set-Content和Out-File的默认编码会根据输出内容自动切换，当遇到二进制数据时可能 fallback 到ASCII，因此需要显式指定UTF-8编码：

修改脚本中的输出语句： #

• 处理空表的Out-File操作，添加编码参数：

  Out-File $parseExtractFile -Encoding utf8
  

  若下游应用要求无BOM的UTF-8（PowerShell 7+版本支持），可使用：

  Out-File $parseExtractFile -Encoding utf8NoBOM
  

• 处理有数据的Set-Content操作，同样添加编码参数：

  $DataSet.Tables[0] | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Set-Content $parseExtractFile -Encoding utf8
  

  或无BOM版本：

  $DataSet.Tables[0] | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Set-Content $parseExtractFile -Encoding utf8NoBOM
  

2. 转换二进制哈希字段为文本格式 #

问题根源是BINARY(32)类型的哈希值属于二进制数据，直接导出会干扰文本编码识别，建议在SQL视图中将其转换为纯文本格式（十六进制或Base64）：

修改哈希字段的SQL语句： #

• 转换为十六进制字符串（推荐，长度固定64字符）：

  CONVERT(VARCHAR(64), HASHBYTES('SHA2_256',
               COALESCE(CAST(Dim_Protection_Received.Protection_Data_Source_Code AS VARCHAR(MAX)),'null') +'||'+ 
               COALESCE(CAST(Dim_Protection_Received.Protection_Id AS VARCHAR(MAX)),'null')
               ), 2) as ide_position_ref
  

  参数2表示返回大写十六进制字符串，若需要小写则用1。

• 转换为Base64字符串：

  CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("hash_val")))', 'VARCHAR(MAX)') as ide_position_ref
  

  需先将哈希值作为子查询的hash_val字段，再执行转换。

这样处理后，哈希字段会以纯文本形式导出，不会触发PowerShell的编码切换，同时保持数据的唯一性和可验证性。

内容的提问来源于stack exchange，提问作者Eseosa Omoregie