将CSV导入到SQL server，其中包含空单元格

Question

我使用vb.net将csv数据导入到sql server表，如果csv中的所有单元格都具有适当的值，则一切工作正常，但错误发生为"未能将参数值从字符串转换为Double "。一旦在csv中有空单元格为float。有人经历过吗?能给我点建议吗?我的代码是:

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- 36TESTDB;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

由于数据库中的某些字段允许为空，因此csv中的某些数据为空。我想知道如何处理空的单元格?

Answer 1

你应该这样尝试:(未经测试的代码给你一个想法)

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

希望有帮助!