R
Rod
OK, I'm completely stumped on this one.
I'm using SqlClient.SqlBulkCopy to try and bulk copy a csv file into a
database. I am getting the following error after calling the
..WriteToServer method.
"The given value of type String from the data source cannot be
converted to type bit of the specified target column."
Now I know that this means that you cannot shove a string or other
incompatible datatype into a bit field, however when examine the data
going into the .WriteToServer method, it only contains 1's or 0's.
Surely these qualify as acceptable values for a bit field!?
At any rate, the bit column accepts nulls also, however even using
nulls, it still errors out with the same messgae.
Can someone please give me some fresh ideas on this one? Here's my
code so far....
' Declare the streamreader objects and read the CSV file into
memory
Dim strFilePath As String = "c:\temp\tblDateLine.hms"
Dim sr As System.IO.StreamReader = New
System.IO.StreamReader(strFilePath)
Dim strImportCSVFile As String = sr.ReadToEnd
' Destroy the Streamreader objects
sr.Close()
sr.Dispose()
' Split into rows
Dim strDelimiter As String = "\n" & vbCrLf
Dim strInputFileRows() As String
strInputFileRows = Split(strImportCSVFile, strDelimiter)
' Split into columns
Dim dt As DataTable = New DataTable
Dim strInputFileColumns() As String
' Create the columns in the data table that correspond to the
number of columns in the input file
strInputFileColumns = Split(strInputFileRows(0), "|,") ' Just
the first row only
For Each s As String In strInputFileColumns
dt.Columns.Add(New DataColumn)
Next
' Now split the rows into columns and add them to the data
table
Dim row As DataRow
Dim finalLine As String = ""
For Each line As String In strInputFileRows
row = dt.NewRow
finalLine = line.Replace(Convert.ToString(vbCr), "")
'finalLine = line.Replace("|,|", "|,NULL")
finalLine = line.Replace("|,|", "|,")
If Microsoft.VisualBasic.Right(finalLine, 1) = "|" Then
finalLine = Microsoft.VisualBasic.Left(finalLine,
Microsoft.VisualBasic.Len(finalLine) - 1)
row.ItemArray = Split(finalLine, "|,")
dt.Rows.Add(row)
Next
' Do the bulk copy
m_strOLEDBConnectionString =
BuildOLEDBConnectionString(g_udtDatabaseSetupOpts.strBlankDatabaseName)
Dim sqlSQLServerBulkCopy As System.Data.SqlClient.SqlBulkCopy =
New System.Data.SqlClient.SqlBulkCopy(m_strOLEDBConnectionString,
SqlClient.SqlBulkCopyOptions.KeepNulls) ' Or
System.Data.SqlClient.SqlBulkCopyOptions.TableLock Or
SqlClient.SqlBulkCopyOptions.Default)
sqlSQLServerBulkCopy.BatchSize = dt.Rows.Count
sqlSQLServerBulkCopy.DestinationTableName = "tblDateLine"
sqlSQLServerBulkCopy.WriteToServer(dt)
sqlSQLServerBulkCopy.Close()
================
Here's a sample of the CSV I'm using
2004-01-01 23:59:59.000|,2004-01-01 00:00:00.000|,9.0|,2004-01-01
08:00:00.000|,2004-01-01 17:00:00.000|,0|\n
The row delimiter is |\n & crlf
The column delimeter is |,
Many thanks and best regards,
Rod
I'm using SqlClient.SqlBulkCopy to try and bulk copy a csv file into a
database. I am getting the following error after calling the
..WriteToServer method.
"The given value of type String from the data source cannot be
converted to type bit of the specified target column."
Now I know that this means that you cannot shove a string or other
incompatible datatype into a bit field, however when examine the data
going into the .WriteToServer method, it only contains 1's or 0's.
Surely these qualify as acceptable values for a bit field!?
At any rate, the bit column accepts nulls also, however even using
nulls, it still errors out with the same messgae.
Can someone please give me some fresh ideas on this one? Here's my
code so far....
' Declare the streamreader objects and read the CSV file into
memory
Dim strFilePath As String = "c:\temp\tblDateLine.hms"
Dim sr As System.IO.StreamReader = New
System.IO.StreamReader(strFilePath)
Dim strImportCSVFile As String = sr.ReadToEnd
' Destroy the Streamreader objects
sr.Close()
sr.Dispose()
' Split into rows
Dim strDelimiter As String = "\n" & vbCrLf
Dim strInputFileRows() As String
strInputFileRows = Split(strImportCSVFile, strDelimiter)
' Split into columns
Dim dt As DataTable = New DataTable
Dim strInputFileColumns() As String
' Create the columns in the data table that correspond to the
number of columns in the input file
strInputFileColumns = Split(strInputFileRows(0), "|,") ' Just
the first row only
For Each s As String In strInputFileColumns
dt.Columns.Add(New DataColumn)
Next
' Now split the rows into columns and add them to the data
table
Dim row As DataRow
Dim finalLine As String = ""
For Each line As String In strInputFileRows
row = dt.NewRow
finalLine = line.Replace(Convert.ToString(vbCr), "")
'finalLine = line.Replace("|,|", "|,NULL")
finalLine = line.Replace("|,|", "|,")
If Microsoft.VisualBasic.Right(finalLine, 1) = "|" Then
finalLine = Microsoft.VisualBasic.Left(finalLine,
Microsoft.VisualBasic.Len(finalLine) - 1)
row.ItemArray = Split(finalLine, "|,")
dt.Rows.Add(row)
Next
' Do the bulk copy
m_strOLEDBConnectionString =
BuildOLEDBConnectionString(g_udtDatabaseSetupOpts.strBlankDatabaseName)
Dim sqlSQLServerBulkCopy As System.Data.SqlClient.SqlBulkCopy =
New System.Data.SqlClient.SqlBulkCopy(m_strOLEDBConnectionString,
SqlClient.SqlBulkCopyOptions.KeepNulls) ' Or
System.Data.SqlClient.SqlBulkCopyOptions.TableLock Or
SqlClient.SqlBulkCopyOptions.Default)
sqlSQLServerBulkCopy.BatchSize = dt.Rows.Count
sqlSQLServerBulkCopy.DestinationTableName = "tblDateLine"
sqlSQLServerBulkCopy.WriteToServer(dt)
sqlSQLServerBulkCopy.Close()
================
Here's a sample of the CSV I'm using
2004-01-01 23:59:59.000|,2004-01-01 00:00:00.000|,9.0|,2004-01-01
08:00:00.000|,2004-01-01 17:00:00.000|,0|\n
The row delimiter is |\n & crlf
The column delimeter is |,
Many thanks and best regards,
Rod