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:
- A Menu sheet
- A Contacts sheet
- 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:
- Full Name
- Street Address
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:
- The MySQL database service is indeed running
- 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()
On Error GoTo ErrHandler
Dim wsCtcts As Worksheet
Set wsCtcts = Worksheets("Contacts")
Set rs = New ADODB.Recordset
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
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
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.