Query MySQL data from Excel (or vice-versa)

Question

I'm trying to automate a tedious problem. I get large Excel (.xls or .csv, whatever's more convenient) files with lists of people. I want to compare these against my MySQL database.*

At the moment I'm exporting MySQL tables and reading them from an Excel spreadsheet. At that point it's not difficult to use =LOOKUP() and such commands to do the work I need, and of course the various text processing I need to do is easy enough to do in Excel.

But I can't help but think that this is more work than it needs to be. Is there some way to get at the MySQL data directly from Excel? Alternately, is there a way I could access a reasonably large (~10k records) csv file in a sql script?

This seems to be rather basic, but I haven't managed to make it work so far. I found an ODBC connection for MySQL but that doesn't seem to do what I need.

• In particular, I'm testing whether the name matches or whether any of four email addresses match. I also return information on what matched for the benefit of the next person to use the data, something like "Name 'Bob Smith' not found, but 'Robert Smith' matches on email address robert.smith@foo".

Answer 1

Why don't you load your CSV data into a dedicated table and perform your searches using MySQLs functions?
You could even do the logic from within excel (VBA or dotNET, depending on release)

No matter what you do, you will have to write a bunch of code, if you wan't to detect Robert Smith...

Answer 2

You can use ADO and SQL. This example is an insert query, but any query will work:

http://stackoverflow.com/questions/2821718/excel-vba-writing-to-mysql-database/2821911#2821911