What are the options for the -Encoding parameter in the Import-DbaCsv tool?

Question

I can't figure out what the Import-DbaCsv tool is expecting for the -Encoding parameter.

The docs aren't helpful - https://docs.dbatools.io/#Import-DbaCsv

I've tried UTF-8, UTF8, and UTF in upper case, lower case, with quotes, and without quotes.

I always get and error message like this:

Cannot convert the "UTF-8" value of type "System.String" to type "System.Text.Encoding"

score 2 · Accepted Answer · answered May 28 '19 at 18:12

2

The link you gave us shows the -Encoding parameter is of type <Encoding>, which leads me to believe you need to use any of the [System.Text.Encoding] encoding classes instead of a string:

So in your case, use [System.Text.Encoding]::UTF8

answered May 28 '19 at 18:12

Theo

57,719
8
24
41

Thanks for the help Theo. I had to add parentheses around the parameter or else it was still being seen as a string (actually, PowerShell ISE added the parentheses for me). Here's the command with your help `Import-DbaCsv -Path "C:\Temp\huge_file.csv" -SqlInstance TestServer -Database TestDatabase -Table "TestTable" -Encoding ([System.Text.Encoding]::UTF8)` – Daniel May 29 '19 at 12:43
Wow, that's painful. – js2010 May 30 '19 at 14:46

score 2 · Answer 2 · answered May 28 '19 at 18:51

The expected type is System.Text.Encoding, but there are no built-in string-to-Encoding conversions that apply here.

You can fix that by applying a PSTypeConverter:

using namespace System.Management.Automation
using namespace System.Text

class PSTextEncodingConverter : PSTypeConverter {
    hidden
    [hashtable]
    $ConversionTable = @{
        'ASCII'               = [System.Text.Encoding]::ASCII
        'ANSI'                = [System.Text.Encoding]::ASCII

        'UTF7'                = [System.Text.Encoding]::UTF7
        'UTF-7'               = [System.Text.Encoding]::UTF7

        'UTF8'                = [System.Text.Encoding]::UTF8
        'UTF-8'               = [System.Text.Encoding]::UTF8

        'Unicode'             = [System.Text.Encoding]::Unicode
        'UTF16LE'             = [System.Text.Encoding]::Unicode
        'LittleEndianUnicode' = [System.Text.Encoding]::Unicode

        'UTF16BE'             = [System.Text.Encoding]::BigEndianUnicode
        'BigEndianUnicode'    = [System.Text.Encoding]::BigEndianUnicode

        'UTF32'               = [System.Text.Encoding]::UTF32
        'UTF-32'              = [System.Text.Encoding]::UTF32

        'Default'             = [System.Text.Encoding]::Default        
    }

    [bool]
    CanConvertFrom([object]$value, [type]$targetType) {
        return (
            $this.IsEncodingType($targetType)
        ) -and (
            (
                $value -is $targetType
            ) -or (
                (
                    $value -is [string] 
                ) -and (
                    "$value" -in $this.ConversionTable.Keys
                )
            )
        )
    }

    [object]
    ConvertFrom([object]$value, [Type]$targetType, [IFormatProvider]$format, [bool]$ignoreCase) {
        if ($value -is $targetType) {
            return $value
        }

        if ($this.ConversionTable.Contains("$value")) {
            return $this.ConversionTable["$value"]
        }

        throw "Failed to convert '$value' to [$($targetType.FullName)]."
    }

    [bool]
    CanConvertTo([object]$value, [Type]$targetType) {
        return $this.CanConvertFrom($value, $targetType)
    }

    [object]
    ConvertTo([object]$value, [Type]$targetType, [IFormatProvider]$format, [bool]$ignoreCase) {
        return $this.ConvertFrom($value, $targetType, $format, $ignoreCase)
    }

    [bool]
    IsEncodingType([type]$targetType) {
        $type = $targetType
        do {
            if ($type -eq [Encoding]) {
                return $true
            }
        } while (($type = $type.BaseType) -ne $null)

        return $false
    }
}

Then register the type converter with Update-TypeData:

Update-TypeData -TypeName System.Text.Encoding -TypeConverter PSTextEncodingConverter

Now any parameter that expects an instance of [Encoding] will accept the string value UTF-8 or UTF8 in its place:

Import-DbaCsv -Path .\file.csv -SqlInstance sqlsrv -Database db1 -Encoding UTF8

score 1 · Answer 3 · answered May 30 '19 at 14:43

Sorry, that was my bad. I've updated Import-DbaCsv a ton and this will be addressed when dbatools 1.0 comes out in 20 days. In addition to being able to pass plain-text encoding, Encoding will autocomplete and actually work when we release. If you'd like it earlier, check out our prerelease branch in GitHub.

What are the options for the -Encoding parameter in the Import-DbaCsv tool?

3 Answers3