CODEWITHSUNDEEP
sql-servervb.netcsv
user30643
user30643

Reputation: 207

import csv to sql server with empty cells

I am using vb.net to import csv data to sql server table, all works fine if all cell in the csv has proper value, but error occurs as "Failed to convert parameter value from a String to a Double." once there has empty cell in the csv for a float. anybody has experienced this? can advise me? my code is:

Dim table As New DataTable()
Dim parser As New FileIO.TextFieldParser("D:\test_1.csv")

table.Columns.Add("OBJECTID")
table.Columns.Add("FIELD1")
table.Columns.Add("FIELD2")
table.Columns.Add("FIELD3")
table.Columns.Add("FIELD4")
table.Columns.Add("FIELD5")
table.Columns.Add("FIELD6")
table.Columns.Add("FIELD7")
table.Columns.Add("FIELD8")
table.Columns.Add("FIELD9")


parser.Delimiters = New String() {","} 
parser.HasFieldsEnclosedInQuotes = True 
parser.TrimWhiteSpace = True
parser.ReadLine()

Do Until parser.EndOfData = True
table.Rows.Add(parser.ReadFields())
Loop

Dim strSql As String = "INSERT INTO BQInfoTest (OBJECTID, FIELD1, FIELD2, FIELD3,     FIELD4, FIELD5, FIELD6, FIELD7, FIELD8, FIELD9) VALUES (@OBJECTID, @FIELD1, @FIELD2,    @FIELD3, @FIELD4, @FIELD5, @FIELD6,@FIELD7,@FIELD8, @FIELD9)"

Using SqlconnectionString As New SqlConnection("Server=PC- 36\TESTDB;Database=test_db;User Id=testuser;Password=testuser;")
Dim cmd As New SqlClient.SqlCommand(strSql, SqlconnectionString) 
With cmd.Parameters
.Add("@OBJECTID", SqlDbType.Int, 8, "OBJECTID")
.Add("@FIELD1", SqlDbType.VarChar, 15, "FIELD1")
.Add("@FIELD2", SqlDbType.VarChar, 200, "FIELD2")
.Add("@FIELD3", SqlDbType.VarChar, 20, "FIELD3")
.Add("@FIELD4", SqlDbType.Int, 8, "FIELD4")
.Add("@FIELD5", SqlDbType.Date, 20, "FIELD5")
.Add("@FIELD6", SqlDbType.Date, 20, "FIELD6")
.Add("@FIELD7", SqlDbType.Date, 20, "FIELD7")
.Add("@FIELD8", SqlDbType.Float, 8, "FIELD8")
.Add("@FIELD9", SqlDbType.Float, 8, "FIELD9")

End With

Dim adapter As New SqlClient.SqlDataAdapter()
adapter.InsertCommand = cmd

Dim iRowsInserted As Int32 = adapter.Update(table)
End Using

Since some fields in database allow null, some data in the csv is empty. I want to know how can I handle the empty cell?

Upvotes: 1

Views: 1539

Answers (1)

NeverHopeless
NeverHopeless

Reputation: 11233

You should try like this: (untested code to give you an idea)

Do Until parser.EndOfData = True

dim data as string() = parser.ReadFields()

for i as integer = 0 to data.Length - 1
   if(string.isNullOrEmpty(data(i))) then
       data(i) = DBNull.Value  ' <-------- Here you go
   end if 
next 

table.Rows.Add(data)

Loop

Hope it helps!

Upvotes: 2

Related Questions