Problems importing into database from CSV and using SqlBulkCopy

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

Your issue is that you are relying on the DataTable to implicitly convert the field string representations to their respective .Net types. As your CSV contains empty fields, you will need to process the String array returned by the String.Split function and assign a parseable string value for the empty values. — TnTinMn
– TnTinMn, Commented Jun 8, 2016 at 15:11

Mark · Accepted Answer · 2016-06-08 17:25:11Z

1

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()

answered Jun 8, 2016 at 17:25

Mark

8,1701 gold badge16 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Problems importing into database from CSV and using SqlBulkCopy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related