Writing typical MySQL query in excel

Question

I'm trying to run a MySQL query in excel. I've set up ODBC in excel and the connection to database is solid.

Is there a way to input my usual query (e.g. select * from table where date= 20131212) to populate as a table?

Answer 1

Try the "Microsoft Query". I haven't used MySQL with it but it ought to work if you've set up an ODBC.

In Excel 2007 (the version I'm using), "Microsoft Query" is available under "Data", "Get External Data", "From Other Sources". In later Excel versions there's probably a similar structure.

Select your ODBC connection, and click "OK".

Here's the "non-obvious" part: when the query wizard "Choose Columns" dialog appears, select "Cancel". It will then ask you if you want to continue editing the query in Microsoft Query. Select "Yes".

You'll have a Microsoft Query query builder form show up, probably with an "Add Tables" subform displayed. Close that subform, and then choose the "SQL" button near the top.

At this point you can paste in your SQL statement, click OK, and it should return results after a time. Be sure to save that Microsoft Query ("File", "Save as") with some name other than the default so you can tell what you built later on. (The default name will be something like "Query from (your MySQL ODBC)"; not all that helpful if you plan on setting up a lot of these.)

Then, choose "File", "Return data to Microsoft Office Excel". It will take you back to Excel, and then ask you where and how you want to view your data.

Later on, once you've established the Microsoft Query in your workbook, if the back-end MySql data is ever updated or changed, all you'll need to do in your workbook is right-click anywhere in your dataset, and choose "refresh".

That should get you started; good luck!