VBA to Convert Excel Table to SQL for mySQL Database

I recently decided that FundamentalFinance.com was long overdue for a design revamp. I decided to use Bootstrap 3 to help with the design. I like Bootstrap because I still have significant control of the layout, but many of the nice details are taken care of.

Since all the pages in FundamentalFinance.com were hard-coded in, I knew that future work on the site might be difficult. I decided to move the content of all 100+ pages into a database and pull the content dynamically. This was a fairly tedious process. I considered coding some sort of screen scraper, but decided that would take longer and likely be more problematic than just doing all 100+ pages by hand.

To make the move easier I made a spreadsheet which basically mirrored a mySQL table which I would ultimately drop the content into. The spreadsheet was much easier to enter data into than directly entering it in phpMyAdmin or a web Form or something. I could sort it, change it, etc. much more easily. Of course, this led to the question of how I would actually get the data from Excel into mySQL.

Upon searching I saw that there were some tools already out there, but I figured it wouldn’t be very difficult to write my own VBA macro to do the work for me. All I would need the macro to do is:

  1. Get the column names from the first line
  2. Read in each line and put it into an INSERT INTO statement
  3. Escape characters that mySQL wouldn’t like, e.g. “\” and ” and ‘ etc.
  4. Output the statements to a text file

I enjoy VBA and have some experience with it, so this wasn’t terribly difficult. The hardest part was dealing with some Excel eccentricities, such as when it automatically replaces ” with “”.

My code follows. Note that you’ll need to edit some of the variables in lines ~10-25 if you want to use this: