How to extract data from DB with correct encoding using PowerShell's SqlClient.SqlConnection?

Question

I have the following working code:

Function DumpAssets {
    Param(
        [string]$DestinationPath = 'X:\path\to\dump_folder'
    )

    $SQLServer = "SQLSERVER123" 
    $SQLDBName = "SUPER_DB"
    $SqlQuery = "SELECT * FROM Lorem_Ipsum"

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd

    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet) | Out-Null

    $SqlConnection.Close()

    $DataSet.Tables[0] | Export-Csv "$DestinationPath\dump.csv" -Force -NoTypeInformation
}

DumpAssets

The script works as intended and I get the CSV with all the rows from the Lorem_Ipsum table. The problem is that I have data with accents and they won't show correctly, instead I get a bunch of ?, for example: SÃO PAULO turns into S?O PAULO. If I open the table with MS SQL Viewer, Power BI or any other tool that can access the DB, it will show correctly.

How to fix my code so the output will be properly formatted?

Answer 1

You can specify the encoding in Export-Csv with parameter -Encoding :

https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-5.1 :

$DataSet.Tables[0] | Export-Csv "$DestinationPath\dump.csv" -Encoding UTF8 -Force -NoTypeInformation