如何确保PowerShell脚本导出CSV时始终使用点作为小数分隔符？

问题根源 #

你猜的没错：ConvertTo-Csv这个命令默认会用当前系统的区域设置来格式化数值，测试环境的区域配置是用逗号当小数点的（比如欧洲不少地区都是这样），所以输出才会不符合要求。数据库里的元数据格式定义管不到PowerShell的格式化逻辑，因为数据读到DataSet里是原始数值类型，最终怎么输出全看PowerShell的规则。

解决方案 #

有两种靠谱的方法能确保不管系统区域怎么设置，都强制用点当小数点：

方法1：临时切换脚本的文化设置 #

在脚本开头加几行代码，临时把当前线程的文化改成用点当小数点的（比如en-US），脚本跑完再改回去，免得影响后面的操作。

方法2：自定义格式化所有数值字段 #

遍历DataTable里的每一行，把数值类型的字段手动格式化成带点小数点的字符串，完全自己控制输出格式。

修改后的完整脚本 #

下面是用方法1改好的脚本，这种方法最简单，对现有代码改动最小：

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

#############################################################
#                     Functions                             *
#############################################################
# 临时切换文化为en-US（确保小数点是点）
$originalCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
[System.Threading.Thread]::CurrentThread.CurrentCulture = [System.Globalization.CultureInfo]::GetCultureInfo("en-US")

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


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()
         
   
        if ($DataSet.Tables[0].Rows.Count -eq 0) {
               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  

# 恢复原来的文化设置
[System.Threading.Thread]::CurrentThread.CurrentCulture = $originalCulture

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

关键修改说明 #

• 脚本开头先保存当前系统的文化设置，然后切换到en-US文化（这个文化默认用点当小数点）
• 脚本跑完、输出完结果后，把文化设置改回原来的，避免影响后续的其他脚本或操作

备选方法说明（自定义字段格式化） #

如果需要更精细的控制（比如严格匹配数据库元数据的#.##0格式），可以把原来ConvertTo-Csv的部分换成下面的逻辑，手动格式化每个数值字段：

else {
    $table = $DataSet.Tables[0]
    # 复制表结构，把数值类型列改成字符串类型
    $formattedTable = $table.Clone()
    foreach ($col in $formattedTable.Columns) {
        if ($col.DataType -in [int], [double], [decimal], [float]) {
            $col.DataType = [string]
        }
    }
    # 遍历每一行，格式化数值字段
    foreach ($row in $table.Rows) {
        $newRow = $formattedTable.NewRow()
        foreach ($col in $table.Columns) {
            if ($col.DataType -in [int], [double], [decimal], [float]) {
                # 用固定格式输出，#.##0和数据库元数据一致，小数点是点
                $newRow[$col.ColumnName] = [string]::Format([System.Globalization.CultureInfo]::InvariantCulture, "{0:#.##0}", $row[$col.ColumnName])
            } else {
                $newRow[$col.ColumnName] = $row[$col.ColumnName]
            }
        }
        $formattedTable.Rows.Add($newRow)
    }
    # 导出格式化后的表到CSV
    $formattedTable | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Set-Content $parseExtractFile
}

这种方法完全不受区域设置影响，能精准控制每个数值字段的输出格式，适合对格式要求特别严格的场景。

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