Inserting Data Into MySQL From Excel Using VBA

Posted: 22 Aug 2008 | Tags: Microsoft, MySQL, Smoking

seun asked in an old post of mine, Interfacing with MySQL via Excel; whether it’s possible to submit data from Excel into MySQL.

Yes, it is very much possible to do this. In fact, I’ve written a tutorial on how to do this with the help of VBA.

First, we create the table that we will use to store the data. The SQL statement below will create our sample table called tutorial. Just copy and paste it into your favourite MySQL IDE or phpMyAdmin.

CREATE TABLE IF NOT EXISTS `tutorial` (

	`id` int(11) NOT NULL auto_increment,

	`title` varchar(255) NOT NULL,

	`author` varchar(255) NOT NULL,

	`price` float(4,2) NOT NULL,

	PRIMARY KEY  (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Now that we’ve created the table, it’s time to store some data in it. The data must come from a source. In this example, I’ve created a list of book titles, along with the author and price of each book. Just copy the table below and paste it into an Excel worksheet.

Title	Author	Price
How to Win Friends and Influence People	Dale Carnegie	7.99
The Magic of Thinking Big	David Schwartz	10.17
The Richest Man in Babylon	George S. Clason	6.99
As a Man Thinketh	James Allen	9.95
The Power of Your Subconcious Mind	Dr. Joseph Murphy	7.49
The Magic of Believing	Claude M. Bristol	6.99
It Works	R.H. Jarrett	3.00
Write It Down, Make It Happen: Knowing What You Want and Getting It	Henriette Anne Klauser	10.52
The Attractor Factor: 5 Easy Steps For Creating Wealth (Or Anything Else) From the Inside Out	Joe Vitale	11.53
The Science of Getting Rich	Wallace D. Wattles	11.20

Now rename that sheet as Books. If you haven’t displayed your Visual Basic and Control Toolbox toolbars yet, now is a good time to do so. Go to the View top menu and navigate to Toolbars. Make sure Visual Basic and Control Toolbox are checked.

I prefer to place the toolbars at the top panel area so they’re out of the way of my Excel spreadsheet. You might want to do the same.

Next, click on the Properties icon of the Control Box toolbar and set the name of the Books worksheet as wsBooks. You’ll get something like the screenshot below:

Now it’s on to some programming. But before we even start typing a single line of code, we need to understand what we’re trying to achieve with our code:

Connect to the local MySQL database server
Use the demo database
Insert each line of the Books table into the tutorial database we created earlier

Connecting to the Database Server and Selecting the Correct Database

As we will be using VBA to perform our data insertion, we need a method to connect to the database server and ensure we’ve selected the correct database where the data will be inserted into.

There are three components necessary for us to perform MySQL database manipulation using VBA:

MySQL Connector/ODBC
Microsoft ActiveX Data Objects (ADO) Library
The correct connection string to access the MySQL database

For this tutorial, I’m using MySQL Connector/ODBC 5.1. You can download it here. For Windows users, grab the MSI Installer version and just double click the file and go through the installation screen.

You’ll also need to reference to the Microsoft ActiveX Data Objects Library in your Excel Workbook. Fire up the Visual Basic Editor using Alt-F11. Go to the Tools top menu item of the Visual Basic Editor and choose References.

In the list of references, check the Microsoft ActiveX Data Objects 2.8 Library. Refer to the following screenshot:

Next, we will then start coding. The first thing we should do is to write a Sub procedure to connect to the database. This will be done using an ADODB connection.

First we need to declare the variable for the ADODB connection. Make sure you’ve double-clicked the wsBooks worksheet in the Project Explorer (if you can’t see it, press Ctrl-R). It should bring out a code window for that sheet.

In that sheet, enter the following code:

Dim oConn As ADODB.Connection

Private Sub ConnectDB()

	Set oConn = New ADODB.Connection

	oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _

		"SERVER=localhost;" & _

		"DATABASE=yourdatabase;" & _

		"USER=yourdbusername;" & _

		"PASSWORD=yourdbpassword;" & _

		"Option=3"

End Sub

You should replace yourdatabase, yourdbusername and yourdbpassword with your database name, your database username and the database password accordingly.

Test your ConnectDB Sub procedure by putting the cursor anywhere between the Sub statement and press F5 (this runs the Sub procedure). If an error popup appears, check that your MySQL service is running and that you’ve referenced to the Microsoft ActiveX Data Objects 2.8 Library.

Once the ConnectDB Sub procedure is working, we will code the Sub procedure to perform data insertion. Before that, we need to create a data sanitizing function to escape single quotes before inserting them into the database. This is necessary because improper quote escaping will cause data insertion to fail.

Here are the codes for the esc function that will escape single quotes from data to be inserted:

Function esc(txt As String)

	esc = Trim(Replace(txt, "'", "\'"))

End Function

And now for the Sub procedure to perform data insertion:

Dim rs As ADODB.Recordset

Private Sub InsertData()

	Set rs = New ADODB.Recordset

	ConnectDB

	With wsBooks

		For rowCursor = 2 To 11

			strSQL = "INSERT INTO tutorial (author, title, price) " & _

				"VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _

				"'" & esc(.Cells(rowCursor, 2)) & "', " & _

				esc(.Cells(rowCursor, 3)) & ")"

			rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

		Next

	End With

End Sub

Now run the InsertData Sub procedure and you will see that it will insert the data from line 2 to line 11 of the Books worksheet into the tutorial table.

Hope this tutorial has given you a basic understanding on how to insert data into a MySQL database from Microsoft Excel using VBA. If you have any questions, just drop a comment below.

Comment Trackback URL Comments Feed

33 responses to “Inserting Data Into MySQL From Excel Using VBA”.

Dabido Says:
August 22nd, 2008 at 12:28 pm
Aww man! I paid too much for my books!!!!
Azmeen Says:
August 22nd, 2008 at 1:00 pm
Heh Dabido, I doubt that this mini tutorial can compete with a professionally written book
Mikael Nyberg Says:
October 29th, 2008 at 9:23 pm
Is this actually viable for everyday modification of a table?
Peter Wright Says:
October 30th, 2008 at 12:12 pm
Great work! Very useful and easy.
Azmeen Says:
October 30th, 2008 at 2:19 pm
Mikael Nyberg,

From my tests, I’d say it is viable depending on the amount of data you wanted to insert. I’ve tested the insertion of thousands of lines of data and it works as intended.

The only time I ran into problems was when the data contained foreign language characters. This is because it mismatches the collation of my database table.

In conclusion, my response is: “It depends.”

Peter Wright,

Thank you
Mark Ruiter Says:
November 2nd, 2008 at 10:32 pm
The connection works fine but the data is not added to the database. Can you explain the locations of the code a bit more.
I have all 3 codes in the same page right now. Is that correct?
Azmeen Says:
November 3rd, 2008 at 10:04 am
Mark Ruiter,

Yes, everything should be on one page.

To troubleshoot data insertion failure, you could try to MsgBox the SQL statement before it performs the actual insertion.

This will show if there’s a problem with the SQL statement itself.

Just stick the line:

MsgBox strSQL

…just before this:

rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
Sarah Says:
November 18th, 2008 at 4:11 am
I am getting a compilation error at this statement:
‘” & esc(.Cells(rowCursor, 1)) & “‘

The message says: “Invalid or unqualified reference”.

then it hightlights the .Cells part. All I am trying to do is copy values from a cell that is length 4 with contents “1234″ and put it into a sql server.
Please help.
sarah Says:
November 18th, 2008 at 4:19 am
this is a continuation of the previous comment:

the code that I have now is:
For Row = 2 To 14 Step 1

connection.Execute “INSERT INTO Customer(Company, Contact,” & _
“Address1, Address2, City, State, Country, Zip, Zip4, ” & _
“Email, Phone, Status)VALUES(’” & esc(.Cells(Row, 2)) & “‘,” & _
“‘” & esc(.Cells(Row, 3)) & “‘, ‘” & esc(.Cells(Row, 4)) & “‘, ” & _
“‘” & esc(.Cells(Row, 5)) & “‘,’” & esc(.Cells(Row, 6)) & “‘, ” & _
“‘” & esc(.Cells(Row, 7)) & “‘,’” & esc(.Cells(Row, 8)) & “‘,” & _
“‘” & esc(.Cells(Row, 9)) & “‘,’” & esc(.Cells(Row, 10)) & “‘,” & _
“‘” & esc(.Cells(Row, 11)) & “‘,’” & esc(.Cells(Row, 12)) & “‘,” & _
“‘” & esc(.Cells(Row, 1)) & “‘)”

Next Row

and it only takes the pre-assigned legth-1. So if I populate a cell to it max capacity it will tell me that the string will get truncated. Ex: cell length=5. I populate with “Hello” which is length 5 but it can only import a length of 4 into the server. If I modify it to “Hell” then it works.

Please help.
Thanks!
Pleas helo
Azmeen Says:
November 18th, 2008 at 10:06 am
sarah,

My suggestion is to use the VBA function Left to only pick the first four characters of the column. For example:

INSERT INTO table (fourcharacteronly) VALUES ('" & esc(Left(.Cells(Row, Column), 4)) & "')

What this does is store only the first four characters of the cell if it’s longer than four.
sarah Says:
November 19th, 2008 at 9:43 pm
Thank you for your response but I have a table with multiple columns with different lengths and the code I have is taking the max length-1. So when it comes to the smallest length 4, its only taking 3 characters but the field needs to be populated with 4. This will tell me that it will truncate the value. Here is my original code:

Sub dbInsert()
‘
‘ dbInsert Macro
‘ Macro recorded 3/20/2007 by steve-dalessandro
‘
‘ Keyboard Shortcut: Ctrl+m
‘
‘declare connection
Dim connection As ADODB.connection
Set connection = New ADODB.connection

‘create database connection
Dim connectionString As String

connectionString = “Provider = SQLOLEDB; Data Source = SAINTDEVSQL1; Initial Catalog = dbTraining; User Id = bouska1992; Password = 12floz/355ml;”
connection.connectionString = connectionString
connection.Open

‘insert data into database
Dim numRows As Integer

RowsCount = ActiveSheet.UsedRange.Rows.Count

For Row = 2 To 14 Step 1

connection.Execute “INSERT INTO Customer(Company, Contact,” & _
“Address1, Address2, City, State, Country, Zip, Zip4, ” & _
“Email, Phone, Status)VALUES(’” & Cells(Row, 2) & ” ‘,” & _
“‘” & Cells(Row, 3) & ” ‘, ‘” & Cells(Row, 4) & ” ‘, ” & _
“‘” & Cells(Row, 5) & ” ‘,’ ” & Cells(Row, 6) & ” ‘, ” & _
“‘ ” & Cells(Row, 7) & ” ‘,’ ” & Cells(Row, & ” ‘,” & _
“‘ ” & Cells(Row, 9) & ” ‘,’ ” & Cells(Row, 10) & ” ‘,” & _
“‘ ” & Cells(Row, 11) & ” ‘,’ ” & Cells(Row, 12) & ” ‘,” & _
“‘ ” & Cells(Row, 13) & “‘)”

Next Row

‘close database connection
connection.Close
Set connection = Nothing

End Sub

please help.
Azmeen Says:
November 20th, 2008 at 9:08 am
Hi Sarah,

I think the problem is with your table design then. Take a look at your Customer table using phpMyAdmin or whatever MySQL database management tool you’re using.

See the properties of the column that is giving you problems. Chances are it would be a VARCHAR typed column.

Set the column size to 4 and it should now accept data that is four characters long or less.
Jerad Says:
January 16th, 2009 at 12:10 am
Here’s my problem. I am creating the Insert statement in one field. So when I’m calling the above code, it works with just saying

strSQL = ActiveCell.Value
and then executing the statement with the above. My only problem is this. If my statement has more than 3 columns that I’m trying to insert statement into, then it bombs and either tells me the syntax is wrong or that the ODBC driver doesn’t support the property. I know the syntax is correct,and I know the field type is correct. Has anyone experienced this problem.

The code would work all day long if I was only inserting a max of three values but once I try to add the fourth, it errors.

THANKS!!
-Jerad
Azmeen Says:
January 16th, 2009 at 6:14 am
Jerad: It would help if we can see a sample of the SQL statement.
mr excel Says:
January 30th, 2009 at 3:19 pm
I been searching the web for easy code examples toooooo long. I found a site where you can download ready files. Not so good descriptions but the files worked.

http://vbaexcel.eu/vba-macro-code/extract-get-data-from-mysql-php

http://vbaexcel.eu/vba-macro-code/update-mysql-database-php

http://vbaexcel.eu/vba-macro-code/write-to-mysql-database-php
mustang Says:
March 12th, 2009 at 4:38 pm
i found out that the column inside excel with date cannot be updated to the database or is there any other way to do it?
Azmeen Says:
March 12th, 2009 at 5:11 pm
mustang,

Assuming the date value is in cell A1 on Sheet1; you can grab the individual values of the year, month and date as follows:

yearval = Str(Year(Sheet1.Cells(1, 1).Value)) monthval = Str(Month(Sheet1.Cells(1, 1).Value)) dayval = Str(Day(Sheet1.Cells(1, 1).Value))

If Left(monthval, 1) = " " Then monthval = "0" + Right(monthval, 1) End If

If Left(dayval, 1) = " " Then dayval = "0" + Right(dayval, 1) End If

mysqldate = yearval + "-" + monthval + "-" + dayval

You can then use the value in mysqldate to insert into your database. The format will be in yyyy-mm-dd.
mustang Says:
March 13th, 2009 at 10:30 am
Thanks alot for the quick response!!!
Azmeen Says:
March 13th, 2009 at 10:33 am
Glad to help
mustang Says:
March 13th, 2009 at 3:38 pm
Let’s say we have two/three tables can we insert the data into all three tables using just one excel sheet?Thanks for the help again!
Azmeen Says:
March 13th, 2009 at 3:49 pm
mustang,

Of course; just generate different SQL statements to insert data in the different tables and execute them. eg:

strSQL1 = "INSERT INTO tutorial (author, title, price) " & _ "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _ "'" & esc(.Cells(rowCursor, 2)) & "', " & _ esc(.Cells(rowCursor, 3)) & ")" rs.Open strSQL1, oConn, adOpenDynamic, adLockOptimistic

strSQL2 = "INSERT INTO tutorialbackup (author, title, price) " & _ "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _ "'" & esc(.Cells(rowCursor, 2)) & "', " & _ esc(.Cells(rowCursor, 3)) & ")" rs.Open strSQL2, oConn, adOpenDynamic, adLockOptimistic
mustang Says:
March 13th, 2009 at 4:00 pm
oh sorry what i meant is…there are four colums in the excel sheet and the first and second column belongs to table A then third and fourth column belongs to table B…how do we split it to enter their respective tables?is it the same code use?thanks alot again!
Azmeen Says:
March 13th, 2009 at 4:07 pm
mustang,

Similar code; you just need to plug in your values according to which table you want them inserted in. eg:

strSQL = "INSERT INTO table1 (val1, val2) " & _ "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _ "'" & esc(.Cells(rowCursor, 2)) & ")" rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

strSQL = "INSERT INTO table2 (val3, val4) " & _ "VALUES ('" & esc(.Cells(rowCursor, 3)) & "', " & _ "'" & esc(.Cells(rowCursor, 4)) & ")" rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
mustang Says:
March 13th, 2009 at 4:28 pm
Thanks!!!!!
newbie Says:
March 17th, 2009 at 9:37 am
Let’s say the file have 13 line of data so we need to change the rowcursor = 2 to 14…can we automate the rowcursor so that whenever we insert a new data to the excel file it will automatically load the data to the database when we run the code?lets say we insert 23 line of data then it automatically insert the 23 data without changing the rowcursor = 2 to 24?
newbie Says:
March 17th, 2009 at 3:43 pm
another question is there a way to show the error msg box if the data inserted is a duplicate primary key instead of showing the runtime error?thank you for your help.
Johan Says:
March 18th, 2009 at 2:54 am
Here is a link to a program for updating a mysql database, good when updating alot of data to cms systems etc!

http://vbaexcel.eu/vba-macro-code/update-mysql-database-php
newbie Says:
March 23rd, 2009 at 10:23 am
thanks johan for the link but i wanted to insert data to different table and i hope the rowcursor can be automated…thanks for the help!
Azmeen Says:
March 23rd, 2009 at 10:25 am
newbie,

Check out this post which also has a pre-built Excel file with the codes.
Jnaneshwar Says:
March 23rd, 2009 at 1:39 pm
Hi.We use Open Office.Calc please any buddy help on the same.
Leon Says:
May 12th, 2009 at 6:05 pm
Thanks for the useful stuff.
Ivanildo Says:
June 8th, 2009 at 7:43 am
Here’s my problem:
I’m trying to insert data that containe foreign language characters. It must be some collation problem of my database table. Can anybody help me on how to solve this, im using UTF-8 for my table collation
guna Says:
June 26th, 2009 at 7:25 am
I tried this way, but I am getting “Run Time Error 28″ Object required.

Can you help me out?

Thanks