Powershell script outputs numbers with comma decimal on one machine and period decimals on another

Question

I have a PowerShell script that exports data from a database into a CSV file. The consumers of the csv expect numbers in the file to have a period as a decimal point. The PowerShell script is being executed in WhereScape Red where the metadata format for each numeric field in the database is defined as #.##0. When the script is run in the dev environment I get the correct output. This is shown below:

When the same script, metadata, and application are deployed to the Test environment I get the output with numbers having a comma as a decimal point. I assume there is a local cultural setting that is causing this. Is there a way to ensure I have a period as a decimal point regardless of local cultural settings?

The Powershell script I wrote is shown below

#############################################################
#
# - 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                             *
#############################################################
$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  

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

Answer 1

This SHOULD change the culture only for the scope of the script.
Thanks to https://devblogs.microsoft.com/powershell/using-culture-culture-culture-script-scriptblock/

# 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.
# It-IT (Italian) uses commas for decimal separator, but feel free to use anything you want
[System.Threading.Thread]::CurrentThread.CurrentCulture = [cultureInfo]::GetCultureInfoByIetfLanguageTag('it-it')


# start of your code
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
}


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