mySQL for SqlBulkCopy

Question

I am new to sql and vb.

I have the following code that works fine for MS SQL EXPRESS.

            If cboServer.Text = "MS SQL Express" Then
            Dim sqlBulk As New SqlBulkCopy(sCONN)
            sqlBulk.BulkCopyTimeout = 500

            sqlBulk.DestinationTableName = sTable
            sqlBulk.WriteToServer(dReader)

        End If

However I am not trying to write the data into MySQL due to the data limitations of MS SQL.

Here is what I have, But I know it is wrong.

            If cboServer.Text = "MySQL" Then
            Dim MyConn As New MySql.Data.MySqlClient.MySqlConnection
            MyConn.ConnectionString = sCONN
            Dim mysqlBulk As New MySql.Data.MySqlClient.MySqlBulkLoader(MyConn)


            mysqlBulk.TableName = sTable
            mysqlBulk.FileName = sFilename
            mysqlBulk.Load()



        End If

Below is the full mod.

  Private Sub ImportFromExcel(ByVal sFilename As String, ByVal sTable As String)

    Try
        Dim excelConnectionString As String = String.Empty

        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFilename & ";Extended Properties=Excel 12.0;Persist Security Info=False"

        Dim excelConnection As New OleDbConnection(excelConnectionString)

        Dim cmd As New OleDbCommand("Select * from [" & sTable & "$]", excelConnection)

        excelConnection.Open()
        Dim dReader As OleDbDataReader
        dReader = cmd.ExecuteReader()


        If cboServer.Text = "MS SQL Express" Then
            Dim sqlBulk As New SqlBulkCopy(sCONN)
            sqlBulk.BulkCopyTimeout = 500

            sqlBulk.DestinationTableName = sTable
            sqlBulk.WriteToServer(dReader)

        End If

        If cboServer.Text = "MySQL" Then
            Dim MyConn As New MySql.Data.MySqlClient.MySqlConnection
            MyConn.ConnectionString = sCONN
            Dim mysqlBulk As New MySql.Data.MySqlClient.MySqlBulkLoader(MyConn)


            mysqlBulk.TableName = sTable
            mysqlBulk.FileName = sFilename
            mysqlBulk.Load()



        End If

        lstError.Items.Clear()
        lstError.Items.Add(Microsoft.VisualBasic.Right(sFilename, 35) & " " & sTable)
        excelConnection.Close()

    Catch ex As Exception
        lstError.Items.Add("Error Reading " & sFilename & " " & sTable)
        lstError.Items.Add(Err.Description)
        lstError.Items.Add("")
        Exit Sub
    End Try

End Sub

How do I get the MySQL part to load the data?

AFAIK the MySQL bulk loader is for importing CSVs. If you have the data in a datatable you should be able to just insert it to either DB using a DataAdapter — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Feb 10, 2017 at 13:48
Hi @Plutonix, This makes sense, I am not able to import CSV files with my code. The challenge is that my Excel file contains 4 sheets. So I assume that I need to open the file, write the data into CSV for the 1st sheet and then load the CSV, kill the CSV and then loop to the next sheet. — Mark Blackburn
– Mark Blackburn, Commented Feb 14, 2017 at 5:26
@Plutonix, how would I do that? that is exactly what I am trying to do — Mark Blackburn
– Mark Blackburn, Commented Feb 15, 2017 at 4:47

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2017-02-15 19:31:39Z

I did some testing and using a DataTable and DataAdapter does work and only takes about 10 lines of code. But due to the nature of the DA, it is pretty slow: ~30 secs to process 10k rows.

Since both bulk tools will work with CSVs and are super fast, I would read in the XL file, export to CSV then use MySqlBulkLoader. CSVHelper makes the middle step easy.

Dim xlConStr = String.Format(OleXLConnstr, "C:\Temp\members.xls")
Dim SQL = "SELECT * FROM [NewMembers$]"
Dim tmpCSVName = "C:\Temp\newmembers.csv"

Dim srcRows As Int32 = 0
Dim dstRows As Int32 = 0

' step one export to CSV - the LCD
Using dbcon As New OleDbConnection(xlConStr),
    cmd As New OleDbCommand(SQL, dbcon)
    Using stw As New StreamWriter(tmpCSVName, False),
         csv = New CsvWriter(stw)

        ' my data can have embedded commas: "Ziggy T. Wilson, Esq."
        csv.Configuration.QuoteAllFields = True

        dbcon.Open()
        Using rdr = cmd.ExecuteReader()
            If rdr.HasRows Then
                rdr.Read()
                ' write out the fieldnames as column headers
                For n As Int32 = 0 To rdr.FieldCount - 1
                    csv.WriteField(rdr.GetName(n))
                Next
                csv.NextRecord()

                ' write the data
                Do
                    For n As Int32 = 0 To rdr.FieldCount - 1
                        If rdr.GetFieldType(n) Is GetType(DateTime) Then
                            ' format dates to ISO
                            ' ToDo: add time as needed
                            csv.WriteField(rdr.GetDateTime(n).
                                                ToString("yyyy-MM-dd"))
                        Else
                            csv.WriteField(rdr(n))
                        End If
                    Next
                    srcRows += 1
                    csv.NextRecord()
                Loop While (rdr.Read() And rdr.IsDBNull(0) = False)
            End If
        End Using
    End Using
End Using

Using dbcon As New MySqlConnection(MySQLConnStr)
    Dim bulk = New MySqlBulkLoader(dbcon)

    bulk.TableName = "Member"
    bulk.FieldTerminator = ","
    bulk.LineTerminator = "\r\n"    ' == CR/LF
    bulk.FileName = tmpCSVName      ' file path name to CSV 
    bulk.NumberOfLinesToSkip = 1    ' has a header
    bulk.FieldQuotationOptional = False
    bulk.FieldQuotationCharacter = Convert.ToChar(34)

    dstRows = bulk.Load()
End Using

For some reason OleDB is reading one more row than is actually in the XLS. It does the same thing if you load a DataTable. The rdr.IsDBNull(0) test in the loop control is to prevent adding such a row to the CSV by testing a column I know should not be blank.
You can compare srcRows and dstRows to see if they were all imported.
You will likely have some changes to make since we have no idea what the data looks like.

It's a lot more code (and a lot more options and control), but it is faster. Where the DataAdapter approach took up to 30 secs this takes about 3 secs for 10k rows. You could keep the code you have for SQL Server using the DataReader with the bulk loader, but that means processing the XLS files differently for one versus the other. Since both will import from CSV, I'd do that.

Thank you for the time that you have spent on this I really appreciate it. I find it strange that MySQL has not made it easy to Import Excel Data files. the way that MSSQL has. the MSSQL Code I use it about 1 sec per 30k rows, really fast. That said I think I need to accept that CSV is the way to go. I will implement your code (Thank you). If I do stumble into an approach to get Excel into Bulk load with MySQL, I will post onto this thread. Once again, thank you for your help.
It is very much in Microsoft's interest to make 2 of their products work together. Now that you can upvote please do so. Upvoting - clicking the UpArrow next to any post you find useful - helps other users find good answers.

Collectives™ on Stack Overflow

mySQL for SqlBulkCopy

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