PowerShell special characters encoding

Question

I've built a PS script to create and populate items from a CSV file. The issue is that chars like ä, ü, ö are replaced with � in the field values. I've changed the encoding from ConvertToDynamicObject() function from 'Default' to 'UTF8', without luck.

The function to read the CSV:

function ReadCSV($media)
{
    # get stream and save content to variable $content
    [System.IO.Stream]$body = $media.Fields["Blob"].GetBlobStream()
    try    
    {
        $contents = New-Object byte[] $body.Length
        $body.Read($contents, 0, $body.Length) | Out-Null
    }
    
    finally 
    {
        $body.Close()    
    }
    
    return $contents;
}

Then I convert the byte[] to a Dynamic object:

function ConvertToDynamicObject($contents)
{
    # convert to dynamic object
    return $csv = [System.Text.Encoding]::Default.GetString($contents) | ConvertFrom-Csv -Delimiter ","
}

Then process the data:

function ImportItem($csv)
{
    foreach ($row in $csv)
    {
        # ... 
    }
}

Answer 1

It is possible that your source file uses an encoding different from UTF8 or your system default. Please double check the encoding of the file your are uploading into the Media Library and then use the same encoding in PowerShell script. If your CSV file was created in Excel, most likely you will need Windows-1252 encoding.

I have tested your code with a CSV file similar to this:

Column1,Column2

äää,äää

üüü,üüü

On my machine, a file in ANSI encoding works well with the line [System.Text.Encoding]::Default.GetString($contents), UTF-8 file works with the line [System.Text.Encoding]::UTF8.GetString($contents), and Excel-generated CSV file in Windows-1252 encoding works only with the line [System.Text.Encoding]::GetEncoding(1252).GetString($contents). All other combinations produce an incorrect result.

For reference, here is the full script that I used:

function ReadCSV($media)
{
    [System.IO.Stream]$body = $media.Fields["Blob"].GetBlobStream()
    try    
    {
        $contents = New-Object byte[] $body.Length
        $body.Read($contents, 0, $body.Length) | Out-Null
    }
    
    finally 
    {
        $body.Close()    
    }
    
    return $contents;
}

function ConvertToDynamicObject($contents)
{
    return $csv = [System.Text.Encoding]::GetEncoding(1252).GetString($contents) | ConvertFrom-Csv -Delimiter ","
}

function ImportItem($csv)
{
    foreach ($row in $csv)
    {
        Write-Host $row.Column1, $row.Column2
        $item = Get-Item "master:/content/home"
        $newItem = New-Item -Parent $item -Name $row.Column1 -ItemType "{76036F5E-CBCE-46D1-AF0A-4143F9B557AA}"
        $newItem.Editing.BeginEdit()
        $newItem["Title"] = $row.Column2
        $newItem.Editing.EndEdit()
    }
}

$csvMediaItem = Get-Item -Path "master:/sitecore/media library/Files/test-excel"
$csvContent = ReadCSV($csvMediaItem)
$csvDynamicObject = ConvertToDynamicObject($csvContent)
ImportItem($csvDynamicObject)

This is the result I got with the above script and sample CSV: