import csv to sql server with empty cells

Question

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?

I agree @NeverHopeless but he has to check if the field is empty ..... which help him to prevent to thrown in execption.So IIF(param,true part,false part). — makemoney2010
– makemoney2010, Commented Jul 22, 2014 at 4:21

NeverHopeless · Accepted Answer · 2014-07-22 04:29:59Z

2

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!

answered Jul 22, 2014 at 4:29

NeverHopeless

11.2k4 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user30643 Over a year ago

Hi NeverHopeless, thanks for your answer, it handles the format of string (varchar) field in the sql server table, but for the float field, the empty cell in csv file will cause the following error: "Failed to convert parameter value from a String to a Double."

NeverHopeless Over a year ago

For that you should first read schema of the table, you are going to insert in and assign value based on type specified in DB. May be this can help you: mssqltips.com/sqlservertutorial/183/informationschemacolumns

Collectives™ on Stack Overflow

import csv to sql server with empty cells

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related