PowerShell生成具有ASCI编码的CSV文件,原因是哈希字段而不是UTF-8

我编写了一个PowerShell脚本,将许多SQL Server表中的数据输出到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

表中的一个字段是散列的,现在导致生成ASCI编码的文件。此编码导致文件被应用程序拒绝。文件必须是UTF_8。该列被散列如下所示

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 

PowerShell中是否有方法防止编码更改为ASCI?

文件的内容如下所示:

期望是: