Problems importing into database from CSV and using SqlBulkCopy

csv sql sqlbulkcopy vb.net

Question

I have a windows form application in which a user chooses a .csv file and clicks import. At this point the file should import into a SQL Server using SqlBulkCopy. Problem is I am getting some errors and I think its due to fields being empty on the import. Im getting this error:

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

This is a sample of the 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,,,

Heres the code Im using to try to import:

 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

Is there any way to insert a default value as the CSV being read in or is there any way to stop this error?

Thanks

Accepted Answer

You should convert empty strings in the sLine array to nulls, otherwise the DataTable is going to try and convert the values to the data type defined for the column. e.g. Using LINQ:

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



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why