define('DISABLE_WP_CRON', 'true'); Inserting An Unknown Number Of Excel Rows Into MySQL With VBA - HTNet

Inserting An Unknown Number Of Excel Rows Into MySQL With VBA

I’ve been asked how to insert an unknown number of lines of data from an Excel spreadsheet into MySQL. I thought that rather than answer that question in a comment reply, I might as well create a sample Excel spreadsheet with the solution already in place.

Of course, I will explain the rationale behind my method in this article first 🙂

There are a few things we need to put in place before we get to the details. First of all, you’ll need to have a working MySQL database server set up. This is not within the scope of this article; but I’m sure this won’t be a problem for most of you.

Next, make sure you’ve installed MySQL Connector/ODBC 5.1 because this is what we’ll use (through VBA) to insert the data.

The next thing on the list is to prepare a database to be used in this exercise. You can select an existing database or create one specifically for this guide. Just remember to have a valid user that has table creation and data insertion priviledges.

Now comes the part where we’ll create a table to store our data. Log in to MySQL and select the database to be used. Next, create a table called contacts using the following SQL:

CREATE TABLE `contacts` (
`cid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`fullname` varchar( 255 ) COLLATE utf8_unicode_ci NOT NULL ,
`email` varchar( 255 ) COLLATE utf8_unicode_ci NOT NULL ,
`street` varchar( 255 ) COLLATE utf8_unicode_ci NOT NULL ,
`city` varchar( 255 ) COLLATE utf8_unicode_ci NOT NULL ,
`state` varchar( 255 ) COLLATE utf8_unicode_ci NOT NULL ,
PRIMARY KEY ( `cid` )
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;

Once that’s done, download this zip file which contains my pre-made Excel workbook containing:

  1. A Menu sheet
  2. A Contacts sheet
  3. Prewritten VBA codes to use in this example

I’m absolutely certain that this file is virus free. Nevertheless, I highly recommend that you scan it with a reliable and updated anti-virus software just to be on the safe side.

When opening the file, you might be prompted that the document contains macros which may be disabled depending on your macro security settings. You should enable macros for this document in order to properly use this spreadsheet.

When you open the Excel file, the first spreadsheet you’ll see will contain a simple menu as shown below:


The second sheet called Contacts will contain a list of 100 randomly generated contact list with the following data:

  1. Full Name
  2. Email
  3. Street Address
  4. City
  5. State

This is the data we’ll be inserting into the MySQL database.

You can straightaway play with the Excel spreadsheet by filling in the Database Settings data in the Menu sheet and clicking on the Test Database button.

If an error pops up, verify that:

  1. The MySQL database service is indeed running
  2. You’ve entered the correct hostname (in most cases this should be localhost), username, password and database info

If you receive a Database Connection Test Successful! message; you’re good to go, and the Insert Contacts Data button should now be enabled. Go ahead and click it!

You should be prompted that 100 rows of data has been successfully inserted. The data is from the Contacts spreadsheet. Now delete some rows of the test data at the end of the Contacts sheet and click the Insert Contacts Data button again.

You’ll see that VBA will still insert the correct amount of data rows.

Now how did we do this? Fire up the Visual Basic Editor by pressing Alt-F11. Next, double click on shtMenu (Menu) in Project Explorer. Scroll down a bit and you should see the following block of code:

Private Sub cmdInsertData_Click()
    ConnectDB False
    On Error GoTo ErrHandler
    Dim wsCtcts As Worksheet
    Set wsCtcts = Worksheets("Contacts")
    Set rs = New ADODB.Recordset
    With shtContacts
        Dim rowctr As Integer
        Dim strsql As String
        rowctr = 2
        While Trim(.Cells(rowctr, 1)) <> ""
            strsql = "INSERT INTO contacts (fullname, email, street, city, state) " & _
            "VALUES ('" & esc(Trim(.Cells(rowctr, 1).Value)) & "', '" & _
            esc(Trim(.Cells(rowctr, 2).Value)) & "', '" & _
            esc(Trim(.Cells(rowctr, 3).Value)) & "', '" & _
            esc(Trim(.Cells(rowctr, 4).Value)) & "', '" & _
            esc(Trim(.Cells(rowctr, 5).Value)) & "')"
            rs.Open strsql, oConn, adOpenDynamic, adLockOptimistic
            rowctr = rowctr + 1
    End With
    MsgBox "Successfully inserted " & Trim(Str(rowctr - 2)) & " rows of data", vbInformation, "Done!"
    If Err.Description <> "" And Err.Source <> "" Then
        MsgBox Err.Description, vbCritical, Err.Source
    End If
End Sub

This is the main block of code that does the data insertion. The way that this code knows when to stop is by iterating through the rows of the Contacts sheet starting from the second row (the first row is skipped since it contains headers) until it reaches a row that contains nothing in its first column.

Simple yet effective for rows of data that doesn’t contain any blank first columns 🙂

With some minor modifications you can even do simple validations before inserting the data.

If you have questions regarding this mini-tutorial, please let me know by commenting below.

22 responses to “Inserting An Unknown Number Of Excel Rows Into MySQL With VBA”.

  1. newbie Says:

    Thanks for the code!But lets say there are a few tables, so we insert our data to different tables by using strsql1 , strsql2 and so on to insert to different tables. These tables have their own primary keys how do we capture the error if the data inserted is the primary keys that is already inside the tables instead of showing the ODBC error?Thanks for the help!

  2. Site Admin Azmeen Says:


    You simple need to do write some codes to verify that the primary key to be inserted doesn’t already exist in the tables.

  3. newbie Says:

    i am so sorry im quite new to vba can you show me a simple example??that would be great thanks!

  4. newbie Says:

    I have try out a few examples myself but is of no help so i hope you can help me thanks alot!

  5. march Says:

    I hope there will be a solution for newbie

  6. Site Admin Azmeen Says:

    There will be an example tutorial 🙂

    But I got to clear some projects already in the pipeline. Hope you’ll have some patience 😉

  7. newbie Says:

    is the tutorial out d?if not yet never mind take your time~ 🙂 thanks alot for your help

  8. newbie Says:

    Is it up already?thanks

  9. Site Admin Azmeen Says:


    Yes. It is here.

  10. march8 Says:

    I saw this great post of yours!its good! may i ask u sum questions regarding excel? i hav an excel that have a few columns namely name n email…do u know how to do a vba that grab the email from outlook address book when the name is inserted?n thus show the name n email?ur opinion is highly value!

  11. Jayanta Says:

    Hello site admin,
    First of all, thanks for all these wonderful write ups.I have a small query—and if u would kindly give a response ,I will be obliged—
    I want to import a no of excel csv files in the same mysql table–the line after –all the data of the 1st csv file once impoted in the table , should be the continuation of the data of the next csv file , and so on.All the headers of the csv files has the same fields.I m trying to use load data local infile statement withen a loop—but have been unsuccessful in my effort–Even I have tried to use the Insert into—- as stated above—-but still my endevour is in vain–Even though it is getting successfully compiled —and when executed—the connection to mysql database is getting successful—but when on selection of the files–the code is getting ready to import data—-then its giving an error—–Run time error ‘-2147217887(80040e21)’–ODBC driver does not support the requested properties—-when I m trying to debug–the line — rs.Open strsql, oConn, adOpenDynamic, adLockOptimistic is getting highlighted—I m really struck—If u kindy help, i will be obliged—

  12. Site Admin Azmeen Says:

    Hi Jayanta,

    Actually your first method of using the LOAD DATA LOCAL INFILE statement is the proper way to import your CSV files’ data.

    Bear in mind that to use LOAD DATA LOCAL INFILE, it needs to be done on a MySQL prompt on your database server itself. This means the CSV files must be uploaded onto the server itself.

    Here’s a good reference on how to do this.

    Of course you can alternatively combine all those CSV files into an Excel worksheet and perform the insertion using VBA.

    Without some samples of your data, I’m afraid this is all the help I can offer.

  13. Jayanta Says:

    Hello Azmeen,
    Thanks,again, for the prompt response.Its true that unless I load a few sample data,its next to impossible to provide further help.Can I mail u,the sample data with a more detailed objective of what I actually want?I know ,its asking too much, but if u do kindly say a “yes” — I will be really obliged.
    Thanks ,once again—for all ur great posts—May Nature shower its heartiest blessings to u for all ur good work.

  14. Jayanta Says:

    Hello Azmeen,
    I have mailed u the sample data—Hope u could make some time and look at my problem—I really would love to have a solution or even some light towards it—But then only ,if u r a bit free and could make some time for me—

  15. jokarileo Says:

    Hola, mi pregunta es la siguiente: YA pude insertar registros desde excel a mysql pero lo hace en forma local peeroo necesito hacerlo mediante en la web; por decir que mis clientes de diferentes ciudades me pongan sus datos abriendo el archivo de excel y se inserten en mi servidor. Como lo puedo hacer?.

  16. Mark Says:

    Great script, Is there an way to import all the rows without out the last row?

  17. Paolo Says:

    Hi, I downloaded the Write-To-MySQL-Database-PHP.xls , but the buttons in menu sheet are locked. How can I unlock them? How do you associate these buttons to the code? If I create a new button I can only associate to a macro, not on a specific VBA code.

  18. Paolo Says:

    Sorry, I mean file 😉

  19. Site Admin Azmeen Says:

    Paolo, check your Macro security settings.

  20. Paolo Says:

    Macro security is set to low level. But how can I associate a VBA code to a button?

  21. Site Admin Azmeen Says:


    Go to Design Mode and simply double-click the button.

  22. Rita Says:

    The Excel gives error when I put DB information.The Error is ODBC driver manager (datasource name not found). Could you please tell me what I am missing>

    Thanks a lot!