Import-csv to SQL

 

 i looking @ different scripts loaded csv files sql. found script , changed parameters to
load delimited file local server. how can script changed write records that's unable load
exception log file, continue load rest of file. next can make sure only
load sql schema allows(if it's field in sql defined nvarchar(8) , csv has 10 characters it
truncates on load 8 characters). script errors saying ""received invalid column length bcp client colid 2". maybe case logs error continues load other records.

# database variables  $sqlserver = "(local)"  $database = "sales"  $table = "code_mstr"     # csv variables  $csvfile = "c:\codemstr.csv"  $csvdelimiter = "|"  $firstrowcolumnnames = $false    $i = 0     ################### no need modify below ###################  write-host "script started..."  $elapsed = [system.diagnostics.stopwatch]::startnew()   [void][reflection.assembly]::loadwithpartialname("system.data")  [void][reflection.assembly]::loadwithpartialname("system.data.sqlclient")     # 50k worked fastest , kept memory usage minimum  $batchsize = 1  try  {     # build sqlbulkcopy connection, , set timeout infinite  $connectionstring = "data source=$sqlserver;integrated security=true;initial catalog=$database;"  $bulkcopy = new-object data.sqlclient.sqlbulkcopy($connectionstring, [system.data.sqlclient.sqlbulkcopyoptions]::tablelock)  $bulkcopy.destinationtablename = $table  $bulkcopy.bulkcopytimeout = 0  $bulkcopy.batchsize = $batchsize     # create datatable, , autogenerate columns.  $datatable = new-object system.data.datatable     # open text file disk  $reader = new-object system.io.streamreader($csvfile)  $columns = (get-content $csvfile -first 1).split($csvdelimiter)  if ($firstrowcolumnnames -eq $true) { $null = $reader.readline() }    [void]$datatable.columns.add(“code_fldname”,[string])  [void]$datatable.columns.add(“code_value”,[string])  [void]$datatable.columns.add(“code_cmmt”,[string])  [void]$datatable.columns.add(“code_user1”,[string])  [void]$datatable.columns.add(“code_user2”,[string])  [void]$datatable.columns.add(“code_desc”,[string])  [void]$datatable.columns.add(“code__qadc01”,[string])       # read in data, line line  while (($line = $reader.readline()) -ne $null)  {   $null = $datatable.rows.add($line.split($csvdelimiter))   $i++; if (($i % $batchsize) -eq 0) {    $bulkcopy.writetoserver($datatable)    #write-host "$i rows have been inserted in $($elapsed.elapsed.tostring())."   $datatable.clear()    }   }      # add in remaining rows since last clear  if($datatable.rows.count -gt 0) {   $bulkcopy.writetoserver($datatable)   #write-host "$i rows have been inserted in $($elapsed.elapsed.tostring())."   $datatable.clear()  }    }    catch   {       $_.exception.message    }     # clean  $reader.close(); $reader.dispose()  $bulkcopy.close(); $bulkcopy.dispose()  $datatable.dispose()     write-host "script complete. $i rows have been inserted database."  write-host "total elapsed time: $($elapsed.elapsed.tostring())"  # garbage collector takes long clear huge datatable.  [system.gc]::collect()

 thanks.

 i added [system.data.sqlclient.sqlbulkcopyoptions]::checkconstraints) script above but
still had error.

thanks.

Windows Server  >  Windows PowerShell