bcpCSV.ps1

function l($t){
    "{0}`t-`t{1}" -f (Get-Date), $t
}
function bcpCsv($f,$t,$b,$cs)
{
    l "Importing file $f to table $t using batchsize $b and connection $cs"
    
    l "creating datatable using target table schema"
    $dt = (getData "select top 0 * from $t" $cs)
    $cols = $dt.columns.columnname
    
    l "bulk importing datatable"
    $bcp = new-object Data.SqlClient.SqlBulkCopy($cs, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock) 
    $bcp.bulkcopytimeout = 0 
    $bcp.destinationtablename = $t
    $bcp.batchsize = $b

    l "opening source file as stream"
    $r = New-Object System.IO.StreamReader($f) 

    l "looping through each line, writing files to server at batchsize"
    $i=0
    try{
        while (($l = $r.ReadLine())) {
            $i++
            $rec = ConvertFrom-Csv $l -Header $cols
            $dr = $dt.NewRow()
            foreach($col in $cols) {
                if ($rec.$col.value) {
                    $dr.$col = $rec.$col
                }
            }
            $dt.Rows.Add($dr)
            if (($i % $b) -eq 0) {
                l "writing batch to server. on record $i" 
                $bcp.WriteToServer($dt)
                $dt.Clear()  
            }  
            
        }
        if($dt.Rows.Count -gt 0) {
            l "writing final batch to server. on record $i" 
            $bcp.WriteToServer($dt) 
            $dt.Clear() 
        }  
    }catch{
        l "An error ocurred on record $i. Details below:"
        $_
    }finally{
        $r.Dispose()
        $bcp.Dispose()
        $dt.Dispose()
    }

}