Accessing MySQL DB into Excel 2010 powerpivot

Question

I have a MySQL DB on hostmonster.com and I am trying import data in excel powerpivot, but having hard time connecting to it.

Does anyone know if Excel powerpivot can even connect to MySQL?

Error I am seeing

Unable to retrieve list of databases. Reason: Failed to connect to the server. Reason: Login timeout expired A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. Named Pipes Provider: Could not open a connection to SQL Server [53].

Answer 1

I was looking for a way to feed data into MySQL (I know, there are better ways...) when I found this. So I was able to connect via odbc to Excel and using PowerPivot 2010 I was able to create a connection from MySQL to PP just fine. Here are the steps I took:

1. Select "Get External Data from Other Sources." (It's the db symbol to the right of "From Azure DataMarket.")

2. In the Connect to a Data Source table import wizard that comes up, select "Others (OLEDB/ODBC)"

3. Now it wants your connection string to your MySQL box, click on "Build." This brings up the "Data Link Properties" dialog, select the "Provider" tab, then the "Microsoft OLE DB Provider for ODBC Drivers." Select "Next." Now on the "Connection" tab, select "Specify the source of data: - use data source name: MySQL. (if you don't have this, you did not install the MySQL ODBC drivers.) Click on "Test" and this should succeed if you have your user and password to MySQL correct. Now the "Connection String field is populated: "Provider=MSDASQL;Persist Security Info=False;User ID=root;DSN=MySQL" is what mine was.

4. The next step was a big one for me. The dialog says "Choose how to import the data" but when I selected "Select from a list of tables and views to choose the data to import" it could not find them for some reason, I selected "Write a query that will specify the data to import." This brought up a query window and I added "select * from MySQL db.table_Sample LIMIT 0, 1000" It validated my SQL statements and imported the data. Very cool.

Answer 2

Try testing the connection trough windows "ODBC Data Source Administrator" first. I'm also having problems with PowerPivot specifically, but using normal Data Connection in Excel -does- work. You could also try that.

EDIT: Just found out that most PowerPivot connection issues get solved by falling back to an older MySQL ODBC driver. Version 3.51.30 does the trick. http://dev.mysql.com/downloads/mirror.php?id=406107

Credit where credit is due: http://www.joyofdata.de/blog/how-to-set-up-powerpivot-and-make-it-talk-to-mysql