Powershell, SQL Server, SQLBULKCOPY: Importing NULL Values in .csv

I'm using a Powershell script found at ((https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/) to load data from quotes delimited, comma separated .csv in to a SQL Server (2016) database.

I've (for now) changed the Powershell script to use this line (KeepNulls instead of TableLock):

`$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring,([System.Data.SqlClient.SqlBulkCopyOptions]::KeepNulls))
`

I'm making an assumption that KeepNulls will populate a column in the SQL table with a NULL value if the corresponding field in the source .csv is empty:

FirstName,Lastname
"Bruce","Foxton"
"Paul",""
"Johnny","Marr"

So the copied, row (2) in the SQL table, if KeepNulls behaves as I anticipate, should have a NULL value in the Lastname column. It doesn't, it's an empty string.

Does KeepNulls behave in a different manner than I'm expecting it to?

Also, how do I correctly configure SqlBulkCopy to use both KeepNulls and TableLock?

Solution

Jeroen Mostert has provided the crucial pointers in comments on the question; let me build on them:

CSV data is inherently untyped, and PowerShell's Import-Csv and its in-memory counterpart, ConvertFrom-Csv, treat all column values (fields) as strings, meaning that an empty field is represented as the empty string ('').
- If you want to treat empty field as database NULLs, you'll need to test the input rows' properties for equaling '' and replace their value with [DBNull]::Value
- You can do this with the help of the intrinsic psobject property property PowerShell exposes on all objects is a rich source of reflection, notably via its .Properties collection.
- See next section.
PowerShell's handling System.Enum-derived types is very flexible in that it allows simple conversion of values to and from strings.
- For flag-based enumerations such as SqlBulkCopyOptions, you can simply specify the symbolic names of the flags in a single string, with the names separated by , (whitespace is ignored:
```
# Shorthand for:
#  [System.Data.SqlClient.SqlBulkCopyOptions]::KeepNulls -bor [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
[System.Data.SqlClient.SqlBulkCopyOptions] 'KeepNulls, TableLock'
```

The following code (PSv5+) populates a DataTable instance from a CSV with matching column names, and replaces all empty CSV fields with [DBNull]::Value:

using namespace System.Data

# Create the data table that will serve as the copy source.
$dt = [DataTable]::new('source')
# Note that columns support NUL ([DBNull]::Value) values by default.
$dt.Columns.AddRange([DataColumn[]] (
  @{ ColumnName = 'FirstName'; DataType = [string] }, 
  @{ ColumnName = 'Lastname'; DataType = [string] }
))

# Populate the table from the input CSV and replace empty fields
# with [DBNull]::Value
# Note: A string literal with ConvertFrom-Csv is used here.
#       In your real code, use Import-Csv with a file path.
@'
FirstName,Lastname
"Bruce","Foxton"
"Paul",""
"Johnny","Marr"
'@ | 
  ConvertFrom-Csv |
  ForEach-Object {
    $dr = $dt.NewRow()
    foreach ($p in $_.psobject.Properties) {
      $dr[$p.Name] = if ('' -eq $p.Value) { [DbNull]::Value } else { $p.Value }
    }
    $dt.Rows.Add($dr)
  }

You should then be able to use $dt as the data source in SqlBulkCopy.WriteToServer calls.