Description:I am getting error while importing CSV file data into SQL table. I am trying to export data from a table into a CSV file and then import that CSV file into another table.The error is due to null values in non string data types columns in a table.
Error:System.InvalidOperationException: The given value of type String from the data source cannot be converted to type int of the specified target column. —> System.FormatException: Failed to convert parameter value from a String to a Int32.
SW stack:Poweshell 4.0,sql server 2012,table schema is same for both source and destination table.
Code: following is stripped code that i am using to import the CSV file.
####### Build the sqlbulkcopy connection, and set the timeout to infinite#########
$BulkCopy = New-Object Data.SqlClient.SqlBulkCopy ($ConnectionString,[System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
$BulkCopy.DestinationTableName = $TableName
$BulkCopy.bulkcopyTimeout = 0
$BulkCopy.batchsize = $BatchSize
# Create the datatable, and autogenerate the columns.
$DataTable = New-Object System.Data.DataTable
# Open the text file from disk
$Reader = New-Object System.IO.StreamReader ($CSVFileFullName)
$Columns = (Get-Content $CSVFileFullName -First 1).Split($CSVDelimiter)
##get Column Names from first line
if ($FirstRowColumnNames -eq $true) {
$null = $Reader.readLine()
}
#create data table with cloumn names read from first line
foreach ($Column in $Columns) {
$null = $DataTable.Columns.Add()
}
# Read in the data, line by line
while (($Line = $Reader.readLine()) -ne $null) {
$null = $DataTable.Rows.Add($Line.Split($CSVDelimiter))
$RowCounter++;
if (($RowCounter % $BatchSize) -eq 0) {
$BulkCopy.WriteToServer($DataTable)
Write-Output "$RowCounter rows have been inserted in $($elapsed.Elapsed.ToString())."
$DataTable.Clear()
}
}
# Add in all the remaining rows since the last clear
if ($DataTable.Rows.Count -gt 0) {
$BulkCopy.WriteToServer($DataTable)
$DataTable.Clear()
}
Write-Output "Script complete. $RowCounter rows have been inserted into the database."
Write-Output "Total Elapsed Time: $($elapsed.Elapsed.ToString())"
}