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 Booksworksheet 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:

  1. Connect to the local MySQL database server
  2. Use the demo database
  3. 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:

  1. MySQL Connector/ODBC
  2. Microsoft ActiveX Data Objects (ADO) Library
  3. 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 yourdatabaseyourdbusername 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.

Happy Birthday to Me!

Photo Credit: emma.c

I was born 31 years ago on this date. Three decades on this earth… wow! I’ve met so many interesting people and done so many deeds that I’m very proud of.

To commemorate my 31st birthday, I’ve decided to list down 31 things that I believe in. I try to run my life as closely as I can to these 31 points. I’m sure there will be revisions to this list just as I’m sure there are things to be added to it.

But as it is, here are The 31 Things That I Believe Everyone Should Know.

  • Not everything goes as planned, but things will go a lot better with at least some level of planning
  • Money isn’t everything but everything needs money
  • There are things you can teach children but they are better educators when it comes to appreciation
  • Perfect timing is a myth, especially when it comes to doing anything life changing
  • There’s only so far you can go by playing safe
  • Always be willing to try anything at least once, it’s the only way to be sure
  • When you’re not happy with what you’re doing; stop!
  • The only reason you’re not seeing a better potential is because you stopped looking
  • Everyone gets the same amount of time in a day, the successful ones utilize it better
  • Sometimes we need a break… everyone does!
  • Taking action and analyzing potential can be a tricky thing; when stuck, trust your gut instinct
  • Sometimes when you have no control over the situation, just try to get by safely and gracefully
  • Crazy people can give you great ideas and inspirations, it’s the psychotic ones you should watch out for
  • If you worry too much about making something that breaks you won’t be making anything
  • Improvise. Always. Even when you’re not told to do so!
  • Go out of your way to meet people. It’s almost always profitable
  • Stop lamenting about the “good old days”, they’re never coming back
  • There’s nothing you can do about your past, but the future starts now. Try not to mess it up!
  • Sleep cures a lot of pain, but don’t get overdosed on it
  • Small things usually give the greatest benefits
  • Always listen to people when they are angry with you, the tend to be most honestthen
  • Trust the evidence, even when emotions tell you it can’t be true
  • Appreciate nature. You grandchildren might never see the things you’re seeing
  • Take one step at a time, as quickly as you can. Because the world isn’t going to wait for you!
  • Don’t fear failure because it’s just a signal to get back on track
  • You can gain knowledge by doing something somewhere at some time. Always!
  • Jealousy is normal, just don’t let it consume you.
  • There’s a difference between being competitive and being a saboteur; competitors have honour, saboteurs don’t
  • The only reason to let an undertaking run on auto-pilot is when it’s stable and self-sustaining
  • Opportunities don’t pop up, you have to find them
  • Nobody can make it by himself. Your destiny is decided by those you have around you.

Happy birthday to me 🙂

Change is an incident beyond norm. There are certain changes that are caused by you and others are totally independent of your actions. Some changes are positive and others are negative. One thing that’s sure is that changes happen around us all the time.

Humans are generally change averse creatures. Most of us prefer the familiar over discovering new ways to do thing. If it’s not for the explorers, tinkerers, geeks and boffins we lot would have gone nowhere! We’d still be living in caves and hunting for food.

The fact is that the world around us keeps changing. Our ancestors realize that some days are colder than the norm. Out of necessity they discovered fire, used fur and leather as clothes, reared animals and farmed. Why? Because keeping the status quo will ultimately lead to their doom.

I’m setting myself up for change too. Soon my days of being an employee will end. I decided to take the entrepreneurial path as my next move. It might seem that I’m bringing this change to myself, but there are external factors that have guided me this way as well.

I see some changes on how businesses in the 21st century are operating. These changes will inevitably come to Malaysian companies too and those who don’t (or won’t) transform themselves will fold. My task is to ensure that this won’t happen to my clients.

Enough about me. Do you realize that things are changing all around us? Information travels much faster and virtually unfiltered nowadays. Turnaround time of product delivery is now calculated in minutes and seconds. The line between home and office aren’t that clear anymore (perhaps even non-existent).

So are you ready for these changes and more? You should, because the world sure as heck won’t be waiting for you.

My Brother’s Wedding

On Sunday, our family held a kenduri kahwin (wedding reception/banquet) for my brother, Azlan and my sister in law Fadila (yes they own blogs too… way too many if you ask me!).

Sensing the opportunity to get some hands on wedding photography experience, I got the green light from my parents to be the photographer for the event.

Bride and Groom Arrival

Bride and Groom Heading to Reception

The bride and groom arrives along with escorting entourage.

All smiles!

Smiling Bride and Groom

It’s smiles all around from Azlan and Fadila.

Flying flower petals: In your face!

Flower Petals Thrown at Bride and Groom

My grandma literally throwing flower petals at my brother and sister in law!

Time for food

Wedding Lunch

Also known as makan berdamai in Malay.

The happy couple

Bride, Groom and Flowers

Close up photo of Azlan and Fadila. This photo is taken after the event is almost over.

The happy couple (2)

Bride and Groom Posing at Garden

Full length photo of Azlan and Fadila.

What do you think of the pictures? Would you hire me to take your wedding photos?

Wow, that was quick… You can download WordPress 2.6 “Tyner” from the usual location.

You can read more about this release from WordPress.org’s announcement post.

Update: And HTNet’s running it 🙂 Upgrade is smooth as you’d expect from any WordPress build.