PowerShell: Importing CSV into SQL Server

http://www.sqlteam.com/article/fast-csv-import-in-powershell-to-sql-server

I was working on the CSV import script in PowerShell, following the first example in the article above: Simple CSV Import using PowerShell.

I had to use Out-DataTable function  from here:
http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd#content

Simple CSV Import using PowerShell

Simple CSV Import using PowerShell

Simple CSV Import using PowerShell

to get DataTable from CSV file. This part worked very well.

But then I started getting an issue at the bulk insert - "The given value of type String from the data source cannot be converted to type int of the specified target column."

After some time of looking for the culprit data row, I realized that this error happens when we try to insert an empty value in the int column.

Thus, I had to change the Out-DataTable function in the following way:

 replace

     $DR.Item($property.Name) = $property.value

by

if($property.value -eq "")
{
        $DR.Item($property.Name) = $null
}
else
{
         $DR.Item($property.Name) = $property.value
}

now, Bulk Insert is satisfied and inserts NULLs when the data is not available in the CSV

Simple CSV Import using PowerShell