This article has been long overdue; so I apologize to newbie who orignally asked how to do duplicate key checking before inserting Excel data into a MySQL database.
I got a few minutes of free time today, so I decided to draft a sample workbook that will demonstrate this process. You can download the sample workbook here. Standard disclaimer: This file has been found to be virus-free when uploaded. However, you are highly recommended to scan the file again with an up to date anti-virus software before opening it.
Also, please make sure you’ve got MySQL Connector/ODBC 5.1 installed on your computer.
Once you’ve downloaded the workbook, start the MySQL service and log on. Select a database where you have priviledges to create tables and insert data. Next, run the following SQL statement inside the selected database to create the table we will use in this exercise:
CREATE TABLE `maillist` (
`did` INT NOT NULL ,
`firstname` VARCHAR( 255 ) NOT NULL ,
`surname` VARCHAR( 255 ) NOT NULL ,
`email` VARCHAR( 255 ) NOT NULL ,
) ENGINE = InnoDB
Once you’ve done that, extract the zip I linked earlier. It should contain an Excel workbook called MySQL-Key-Check.xls. Extract it somewhere easily accessable. Before opening the workbook, please make sure that Excel’s macro security setting is set to Medium. Already there? Good! Now open the file. At the security warning window, click on the Enable Macros button and you should be ready to go!
The first sheet you’ll see is named Menu and will look as follows:
The second sheet will contain a list of data categorized into did (data id), firstname, surname and email. This is the data that we will insert into MySQL.
Now fill in the Database Settings data and click 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!
After a while, you’ll be shown the following prompt:
You’ll also see that the Data sheet is now selected and the duplicate IDs have all been highlighted in red.
To see how the magic is done, fire up the Visual Basic Editor by pressing Alt-F11 and select the shtMenu (Menu) object in Project Explorer. In its code window, scroll down until you see the
You’ll see that the first SQL statement performs the key check. If the key is already in use, it highlights the did column red and goes to the next line. If the key is not in use, an SQL statement to perform the data insertion is constructed and executed.
This process is repeated until an empty did column is found. When this stage is reached, a prompt reports the number of data rows inserted along with the ones skipped due to the did key being already used.
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.