ConvertTo-CSV -UseCulture ignores current thread's culture

Question

Question

Is it possible to force PowerShell to export to CSV in French format when run in a Windows Session with en-GB culture?

More Info

I'm hoping to export some data to CSV using the French culture rules (i.e. CSV's delimiter set to semicolon, but also with numbers using commas for decimal places, and other cultural formatting differences; so just using the -Delimiter parameter is not sufficient).

I came up with the below code (based on https://stackoverflow.com/a/7052955/361842)

function Set-Culture
{
    [CmdletBinding(DefaultParameterSetName='ByCode')]
    param (
        [Parameter(Mandatory,ParameterSetName='ByCode',Position=1)]
        [string] $CultureCode
        ,
        [Parameter(Mandatory,ParameterSetName='ByCulture',Position=1)]
        [System.Globalization.CultureInfo] $Culture
    )
    begin {
        [System.Globalization.CultureInfo] $Culture = [System.Globalization.CultureInfo]::GetCultureInfo($CultureCode) 
    }
    process {
        [System.Threading.Thread]::CurrentThread.CurrentUICulture = $Culture
        [System.Threading.Thread]::CurrentThread.CurrentCulture = $Culture
    }
}

function Invoke-CommandInCulture {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory,ParameterSetName='ByCode',Position=1)]
        [string]$CultureCode
        ,
        [Parameter(Mandatory,Position=2)]
        [ScriptBlock]$Code
    )
    process {
        $OriginalCulture = Get-Culture
        try 
        {
            Set-Culture $CultureCode
            Write-Verbose (Get-Culture) #this always returns en-GB
            Invoke-Command -ScriptBlock $Code
        }
        finally 
        {
            Set-Culture $OriginalCulture
        }
    }
}

The following code implies that this method works:

Invoke-CommandInCulture -CultureCode 'fr' -Code {
    [System.Threading.Thread]::CurrentThread.CurrentUICulture
    [System.Threading.Thread]::CurrentThread.CurrentCulture
} #shows that the command's thread's culture is French

Invoke-CommandInCulture -CultureCode 'fr' -Code {
    get-date
} #returns the current date in French 

However PowerShell has it's own idea of what's going on

Invoke-CommandInCulture -CultureCode 'fr' -Code {
    get-culture
    "PSCulture: $PSCulture"
    "PSUICulture: $PSUICulture"        
} #returns my default (en-GB) culture; not the thread's culture

And this impacts the logic for converting to CSV:

Invoke-CommandInCulture -CultureCode 'fr' -Code {
    get-process | ConvertTo-CSV -UseCulture
} #again, uses my default culture's formatting rules; not the FR ones

Answer 1

Workaround #1: Custom Function to Convert Values to Strings in Given Culture

Here's a workaround solution; converting each field to a string using the given culture, then converting the string values to a CSV:

function ConvertTo-SpecifiedCulture {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory,ValueFromPipeline)]
        [PSObject]$InputObject
        ,
        [Parameter(Mandatory)]
        [string]$CultureCode
        ,
        [Parameter(ParameterSetName='DefaultParameter', Position=0)]
        [System.Object[]]$Property
    )
    begin {
        [System.Globalization.CultureInfo] $Culture = [System.Globalization.CultureInfo]::GetCultureInfo($CultureCode) 
    }
    process {
        if($Property -eq $null) {$Property = $InputObject.PSStandardMembers.DefaultDisplayPropertySet.ReferencedPropertyNames}
        $Result = new-object -TypeName PSObject -Property @{}
        $Property | %{
            $Result | Add-Member -MemberType NoteProperty -Name $_ -Value ($InputObject."$_").ToString($Culture) 
        }
        $Result 
    }
}

Get-Process | select -first 2 | ConvertTo-SpecifiedCulture -CultureCode 'fr' | ConvertTo-CSV -Delimiter ';'

Workaround #2: Override Current Culture to Match Target Culture

Another option is to change the settings of the current culture so that it shares those of the required culture. This feels more hacky; though depending on scenario may work out cleaner/more practical than the above.

e.g. to use FR's number format we just update the current culture's number format to match FR's:

$(Get-Culture).NumberFormat = ([System.Globalization.CultureInfo]'FR').NumberFormat

...and we can do likewise for the remaining (settable) properties:

function Set-CurrentCulture {
    [CmdletBinding()]
    param (
        [string]$CultureCode
    )
    begin {
        $Global:FakedCurrentCulture = $CultureCode #in case we need a reference to the current culture's name
        [System.Globalization.CultureInfo]$NewCulture = [System.Globalization.CultureInfo]::GetCultureInfo($CultureCode)
        [System.Globalization.CultureInfo]$ReferenceToCurrentCulture = Get-Culture
        Write-Verbose "Switching Defintion to $($NewCulture.Name)"
    }
    process {
        #NB: At time of writing, the only settable properties are NumberFormatInfo & DateTimeFormatInfo
        $ReferenceToCurrentCulture.psobject.properties | ?{$_.isSettable} | %{
            $propertyName = $_.Name
            write-verbose "Setting property $propertyName"
            write-verbose "- from: $($ReferenceToCurrentCulture."$propertyName")"
            write-verbose "- to: $($NewCulture."$propertyName")"
            $ReferenceToCurrentCulture."$propertyName" = $NewCulture."$propertyName"
        }
        #ListSeparator
        $ReferenceToCurrentCulture.TextInfo.psobject.properties | ?{$_.isSettable} | %{
            $propertyName = $_.Name
            write-verbose "Setting property TextInfo.$propertyName"
            write-verbose "- from: $($ReferenceToCurrentCulture.TextInfo."$propertyName")"
            write-verbose "- to: $($NewCulture.TextInfo."$propertyName")"
            $ReferenceToCurrentCulture.TextInfo."$propertyName" = $NewCulture."$propertyName"
        }
        #for some reason this errors
        #Localized, TwoDigitYearMax
        <#
        $ReferenceToCurrentCulture.Calendar.psobject.properties | ?{$_.isSettable} | %{
            $propertyName = $_.Name
            write-verbose "Setting property Calendar.$propertyName"
            write-verbose "- from: $($ReferenceToCurrentCulture.Calendar."$propertyName")"
            write-verbose "- to: $($NewCulture.Calendar."$propertyName")"
            $ReferenceToCurrentCulture.Calendar."$propertyName" = $NewCulture."$propertyName"
        }
        #>
    }
}

function Reset-CurrentCulture {
    [CmdletBinding()]
    param ()
    process {
        Set-CurrentCulture -CultureCode ((get-culture).Name)
    }
}

function Test-It {
    [CmdletBinding()]
    param ()
    begin {
        write-verbose "Current Culture: $Global:FakedCurrentCulture" 
    }
    process {
         1..5 | %{
            New-Object -TypeName PSObject -Property @{
                Integer = $_
                String = "Hello $_"
                Numeric = 2.139 * $_
                Money = (2.139 * $_).ToString('c')
                Date = (Get-Date).AddDays($_)
            }
         } | ConvertTo-Csv -NoTypeInformation
    }
}


Set-CurrentCulture 'fr' -Verbose
Test-It
Set-CurrentCulture 'en-GB' -Verbose
Test-It
Set-CurrentCulture 'en-US' -Verbose
Test-It
Set-CurrentCulture 'ar-DZ' -Verbose
Test-It

Reset-CurrentCulture -Verbose
Test-It

We could potentially go further and look at overwriting the read only properties (this is possible: https://learn-powershell.net/2016/06/27/quick-hits-writing-to-a-read-only-property/)... but this already feels very nasty; so I'm not going to go there as the above was sufficient for my needs.