How to add 'Real' type to a DataTable?

Question

I am bulkcopying records from a CSV file to a SQL table. The SQL table has columns that are varchar, and columns that are real datatype (based on the CSV attributes we are given).

Let's suppose that the first 7 columns are the Foreign Keys of varchar(100), and the rest of the 80+ columns are Real datatype.

During the bulk copy, I used Out-DataTable function because apparently that's the most efficient way to bulk copy (especially with our files containing 1000s of records).

However, I am getting the following error:

Exception calling "WriteToServer" with "1" argument(s): "The given value of type String from the data source cannot be converted to type real of the specified target column."

Now I wish the error could specify which column exactly, but based on my research, I've found that this could be related to the Datatype being presumed to be string type for all columns.

Verifying with the following: $column.DataType

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object

So the question is: how do I tell the Datatable to allow the first 7 columns to be string, but the rest of them real datatype?

Here is the code:

function Get-Type 
{ 
    param($type) 
 
$types = @( 
'System.Boolean', 
'System.Byte[]', 
'System.Byte', 
'System.Char', 
'System.Datetime', 
'System.Decimal', 
'System.Double', 
'System.Guid', 
'System.Int16', 
'System.Int32', 
'System.Int64', 
'System.Single', 
'System.UInt16', 
'System.UInt32', 
'System.UInt64') 
 
    if ( $types -contains $type ) { 
        Write-Output "$type" 
    } 
    else { 
        Write-Output 'System.String' 
         
    } 
} #Get-Type

function Out-DataTable 
{ 
    [CmdletBinding()] 
    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject) 
 
    Begin 
    { 
        $dt = new-object Data.datatable   
        $First = $true  
    } 
    Process 
    { 
        foreach ($object in $InputObject) 
        { 
            $DR = $DT.NewRow()   
            foreach($property in $object.PsObject.get_properties()) 
            {   
                if ($first) 
                {   
                    $Col =  new-object Data.DataColumn   
                    $Col.ColumnName = $property.Name.ToString()   
                    if ($property.value) 
                    { 
                        if ($property.value -isnot [System.DBNull]) { 
                            $Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)") 
                        } 
                    } 
                    $DT.Columns.Add($Col) 
                }   
                if ($property.Gettype().IsArray) { 
                    $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 
                }   
               else { 
                    $DR.Item($property.Name) = $property.value 
                } 
            }   
            $DT.Rows.Add($DR)   
            $First = $false 
        } 
    }  
      
    End 
    { 
        Write-Output @(,($dt)) 
    } 
 
} #Out-DataTable

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $connectionstring
$SqlConnection.Open()

$CSVDataTable = Import-Csv $csvFile | Out-DataTable

# Build the sqlbulkcopy connection, and set the timeout to infinite
$sqlBulkCopy = New-Object ("Data.SqlClient.SqlBulkCopy") -ArgumentList $SqlConnection
$sqlBulkCopy.DestinationTableName = "$schemaName.[$csvFileBaseName]"
$sqlBulkCopy.bulkcopyTimeout = 0
$sqlBulkCopy.batchsize = 50000
$sqlBulkCopy.DestinationTableName = "$schemaName.[$csvFileBaseName]"

#This mapping helps to make sure that the columns match exactly because BulkCopy depends on indexes not column names by default. 
#However, with the DataTable, the correct mappings seems to be already taken care of, but putting this here regardless, because why not?
#Better safe than sorry, right? ;)
#https://stackoverflow.com/a/50995201/8397835
foreach ($column in $CSVDataTable.Columns) { $sqlBulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName) > $null }

$sqlBulkCopy.WriteToServer($CSVDataTable)

# Clean Up
$sqlBulkCopy.Close(); $sqlBulkCopy.Dispose()
$CSVDataTable.Dispose()
# Sometimes the Garbage Collector takes too long to clear the huge datatable.
[System.GC]::Collect()

Maybe something like this?

PseudoCode:

foreach ($column in $CSVDataTable.Columns) { 
    $sqlBulkCopy.ColumnMappings.Add(
        if($DestinationTableName.Column.type -eq 'Real') {
            $column.type() = 'Real'
        }
        $column.ColumnName, $column.ColumnName
    ) > $null 
}

Answer 1

Out-DataTable is inspecting the properties of the first input object...

foreach($property in $object.PsObject.get_properties())
{
    if ($first) 
    {  

...to determine the DataType of the corresponding DataColumn...

if ($property.value -isnot [System.DBNull]) { 
    $Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)") 
} 

The problem is, the input objects are produced by Import-Csv...

$CSVDataTable = Import-Csv $csvFile | Out-DataTable

...which doesn't do any conversion of the CSV fields; every property will be of type [String], therefore, every DataColumn will be, too.

The .NET equivalent of real is Single, so you either need to hard-code which columns (by name or ordinal) should be of type [Single]...

$objectProperties = @($object.PSObject.Properties)
for ($propertyIndex = 0; $propertyIndex -lt $objectProperties.Length)
{
    $property = $objectProperties[$propertyIndex]
    if ($propertyIndex -lt 7) {
        $columnDataType = [String]
        $itemValue = $property.Value
    }
    else {
        $columnDataType = [Single]
        $itemValue = if ($property.Value -match '^\s*-\s*$') {
            [Single] 0
        } else {
            [Single]::Parse($property.Value, 'Float, AllowThousands, AllowParentheses')
        }
    } 

    if ($first) 
    {   
        $Col =  new-object Data.DataColumn   
        $Col.ColumnName = $property.Name
        $Col.DataType = $columnDataType

        $DT.Columns.Add($Col) 
    }

    $DR.Item($property.Name) = $itemValue
}

...or augment your detection logic...

foreach($property in $object.PSObject.Properties)
{
    $singleValue = $null
    $isSingle = [Single]::TryParse($property.Value, [ref] $singleValue)

    if ($first) 
    {   
        $Col =  new-object Data.DataColumn   
        $Col.ColumnName = $property.Name
        $Col.DataType = if ($isSingle) {
            [Single]
        } else {
            [String]
        }

        $DT.Columns.Add($Col) 
    }

    $DR.Item($property.Name) = if ($isSingle) {
        $singleValue
    } else {
        $property.value
    }
}

To comply with the column DataType, this code substitutes the [Single] value for the original property [String] value when parsing succeeds. Note that I've removed the checks for [DBNull] and IsArray because they would never evaluate to $true since, again, Import-Csv will only produce [String] properties.

The above assumes that if a property's value from the first input object can be parsed as a [Single] then the same is true for every input object. If that's not guaranteed, then you can do one pass through all input objects to determine the appropriate column types and a second pass to load the data...

function Out-DataTable
{ 
    End 
    {
        $InputObject = @($input)
        $numberStyle = [System.Globalization.NumberStyles] 'Float, AllowThousands, AllowParentheses'
        $dt = new-object Data.datatable 

        foreach ($propertyName in $InputObject[0].PSObject.Properties.Name)
        {
            $columnDataType = [Single]

            foreach ($object in $InputObject)
            {
                $singleValue = $null
                $propertyValue = $object.$propertyName
                if ($propertyValue -notmatch '^\s*-?\s*$' `
                    -and -not [Single]::TryParse($propertyValue, $numberStyle, $null, [ref] $singleValue))
                {
                    # Default to [String] if not all values can be parsed as [Single]
                    $columnDataType = [String]
                    break
                }
            }

            $Col =  new-object Data.DataColumn   
            $Col.ColumnName = $propertyName
            $Col.DataType = $columnDataType

            $DT.Columns.Add($Col) 
        }

        foreach ($object in $InputObject)
        { 
            $DR = $DT.NewRow()   
            foreach($property in $object.PSObject.Properties) 
            {   
                $DR.Item($property.Name) = if ($DT.Columns[$property.Name].DataType -eq [Single]) {
                    if ($property.Value -match '^\s*-?\s*$') {
                        [Single] 0
                    } else {
                        [Single]::Parse($property.Value, $numberStyle)
                    }
                } else {
                    $property.value
                }
            }   
            $DT.Rows.Add($DR)   
        } 

        Write-Output @(,($dt)) 
    }  

} #Out-DataTable

Answer 2

I had a similar challenge after importing XML-data via .readXml because the XML included empty strings instead of dbnull. I made a lot of tests to get this converted as fast as possible and for me this worked best:

1. create a dataTable with all columns as stings for the data-import
2. create the same columns with slighly different names with the correct target type in the same table and create a reference to the string-column (e.g. if first column of type 'string' is named "c1" then i named the new column of type 'real' "c1_")
3. during creation of each column in step 2 also create an expression like 'IIF(LEN([c1]=0),NULL,[c1])' that solves the "empty-string"-dilemma.
4. now do a bulk-import and finally export only the referencing columns via a dataTableReader into its own dataTable.