从 CSV 导入数据库和使用 SqlBulkCopy 时出现问题

Question

我有一个Windows表单应用程序，用户在其中选择一个.csv文件并单击导入。此时，该文件应使用 SqlBulkCopy 导入到 SQL Server 中。问题是我收到一些错误，我认为这是由于导入时字段为空。我收到此错误：

Input string was not in a correct format.Couldn't store <> in ScranDrugQuantity Column.  Expected type is Int32.

这是 CSV 的示例

974524,416194,131179,For information only. ,17/05/2016 16:43:27,17/05/2016 16:43:27,1,xxxx xxxxx,xxxx xxxxx,1,Information about,930720,,320139002,1,3317411000001100,,1252,,5143,17/05/2016 16:42:51,17/05/2016 00:00:00,17/05/2016 16:43:27,0051431705161623286,3348074428,HS21X,,,
974522,416192,153168,information ,17/05/2016 16:42:54,17/05/2016 16:42:54,500,xxxxx xxxxxx,xxxxxxxx ,80,more information,930718,,,,,,,737,,4256,17/05/2016 16:42:13,17/05/2016 00:00:00,17/05/2016 16:42:54,31944C7C3AC04C4D92204775BBCEA365,3418645550,XX21,,,
974521,416192,153168,xxxxxxxxxxxxx ,17/05/2016 16:42:49,17/05/2016 16:42:49,500,xxxxxxxx xxxxxxxxx,xxxxxxxx,80,xxxxxxxx,930717,,,,,,,737,,4256,17/05/2016 16:42:13,17/05/2016 00:00:00,17/05/2016 16:42:49,31944C7C3AC04C4D92204775BBCEA365,3418645550,HS21,,,

这是我用来尝试导入的代码：

 Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
    Dim oTimer As New System.Diagnostics.Stopwatch
    oTimer.Start()
    Dim iRows As Int64 = 0
    ' & My.Settings.dbServer & ";uid=" & My.Settings.dbUsername & ";pwd=" & My.Settings.dbPassword & ";database=" & My.Settings.dbDatabase
    Using oBulk As New Data.SqlClient.SqlBulkCopy("server=" & My.Settings.dbServer & ".;database=" & My.Settings.dbDatabase & ";trusted_connection=yes;uid=" & My.Settings.dbUsername & ";pwd=" & My.Settings.dbPassword, SqlClient.SqlBulkCopyOptions.TableLock) With
    {.DestinationTableName = "Scripts", .BulkCopyTimeout = 0, .BatchSize = 100}
        Using oSR As New IO.StreamReader(strFilename.Text)
            Using oDT As New DataTable
                With oDT.Columns
                    .Add("scriptID", Type.GetType("System.Int32"))
                    .Add("PrescriptionID", Type.GetType("System.Int32"))
                    .Add("StockID", Type.GetType("System.Int32"))
                    .Add("CautionsExpanded", Type.GetType("System.String"))
                    .Add("ScriptDateDispensed", Type.GetType("System.DateTime"))
                    .Add("ScriptDateLastChanged", Type.GetType("System.DateTime"))
                    .Add("PackSize", Type.GetType("System.Int32"))
                    .Add("PatientName", Type.GetType("System.String"))
                    .Add("PrescriberName", Type.GetType("System.String"))
                    .Add("Quantity", Type.GetType("System.Int32"))
                    .Add("ScriptDispensedAs", Type.GetType("System.String"))
                    .Add("ScriptNumber", Type.GetType("System.Int32"))
                    .Add("ScriptWrittenAs", Type.GetType("System.String"))
                    .Add("ScranDrugDmadCode", Type.GetType("System.String"))
                    .Add("ScranDrugQuantity", Type.GetType("System.Int32"))
                    .Add("ScanSnomedQuantity", Type.GetType("System.String"))
                    .Add("ScanSnomedUnit", Type.GetType("System.String"))
                    .Add("ScanDrugDosage", Type.GetType("System.String"))
                    .Add("GMSNo", Type.GetType("System.Int32"))
                    .Add("GMSPrice", Type.GetType("System.String"))
                    .Add("PrescriberNumber", Type.GetType("System.String"))
                    .Add("PrescriptionDispensed", Type.GetType("System.DateTime"))
                    .Add("PrescriptionDatePrescribed", Type.GetType("System.DateTime"))
                    .Add("PrescriptionDateLastChanged", Type.GetType("System.DateTime"))
                    .Add("EPESPrescriptionID", Type.GetType("System.String"))
                    .Add("PersonCodePPSN", Type.GetType("System.String"))
                    .Add("FormName", Type.GetType("System.String"))
                    .Add("ExportedOn", Type.GetType("System.String"))
                    .Add("Completed", Type.GetType("System.String"))
                    .Add("Spare", Type.GetType("System.String"))
                End With
                Dim iBatchsize As Integer = 0
                Do While Not oSR.EndOfStream
                    Dim sLine As String() = oSR.ReadLine.Split(CChar(","))
                    oDT.Rows.Add(sLine)
                    iBatchsize += 1
                    If iBatchsize = 100 Then
                        oBulk.WriteToServer(oDT)
                        oDT.Rows.Clear()
                        iBatchsize = 0
                        Console.WriteLine("Flushing 100,000 rows")
                    End If
                    iRows += 1
                Loop
                oBulk.WriteToServer(oDT)
                oDT.Rows.Clear()
            End Using
        End Using
    End Using
    oTimer.Stop()
    Console.WriteLine(iRows.ToString & "Records imported in " & oTimer.Elapsed.TotalSeconds & " seconds.")
    Console.ReadLine()
End Sub

有

没有办法插入默认值作为正在读取的CSV，或者有没有办法阻止此错误？

谢谢

Answer 1

您应该将

sLine 数组中的空字符串转换为 null，否则DataTable将尝试将值转换为为列定义的数据类型。 例如，使用 LINQ：

Dim sLine As String() = oSR.ReadLine() _
    .Split(CChar(",")) _
    .Select(Function(x) If(String.IsNullOrEmpty(x), Nothing, x)) _
    .ToArray()