5

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].

JewelThief
  • 611
  • 2
  • 10
  • 25

5 Answers5

9

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.

Michael McShane
  • 91
  • 1
  • 2
  • 1
    I tried upto #3 and I am not getting "MySQL" as one of the options in the drop down for Data Source Name. I have installed mySQL ODBC drivers. – JewelThief Jun 13 '11 at 22:06
  • 1
    Just to make sure I have correct bits, here is what I my control panel reads about ODBC driver - MySQL Connector/ODBC 5.1 – JewelThief Jun 13 '11 at 22:09
  • 2
    also make sure that your ODBC driver flavour, 32-bit or 64-bit matches that of your version of excel. For example, I have yet to see a 64-bit application that can use 32-bit ODBC driver. –  Aug 03 '11 at 10:25
  • 1
    Steps 1-4 works fine, but when it tries to import it hangs. Anyone got the solution for that? –  Dec 01 '11 at 16:04
  • There is a mistake in step 3. The list shown in "use data source name" is not a list of drivers, but a list of ODBC connections set up trough windows "ODBC Data Source Administrator". You must first set up an ODBC connection there. Michael probably did that in advance and forgot about it, otherwise it would not make sense that you don't have to specify the MySQL server address. – Wouter Feb 14 '13 at 14:17
  • Jimmi, i think you are facing this problem: http://connect.microsoft.com/SQLServer/feedback/details/766172/error-on-powerpivot-table-import-wizard-when-getting-external-data#tabs i'm still looking for a sollution – Wouter Feb 14 '13 at 14:19
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

Wouter
  • 1,829
  • 3
  • 28
  • 34
0

I'm not familiar with powerpivot, but here is a link to the ODBC drivers for MySQL DB. Once installed I know you can connect to a MySQL database from Excel. I'm guessing powerpivot is the same. http://dev.mysql.com/downloads/connector/odbc/5.1.html

wilbbe01
  • 1,931
  • 1
  • 24
  • 38
  • Try downloading the MySQL gui tools. See if you're able to connect to your database with them. Or are you already sure that you are able to connect to the database from other places, and excel is the only thing where you cannot? http://dev.mysql.com/downloads/gui-tools/5.0.html – wilbbe01 Nov 13 '10 at 19:38
0

I've tried the same approach without success. Specifically:

  • i set up an ODBC connection to MySQL
  • PowerPivot seems to be able to see it when i use the Build wizard, and connection test succeeds
  • when i go to import (via "Select from a list of table"), it fails with an error in the SQL syntax (which of course i didn't write).

So something's wrong somewhere under the hood.

SeanB
  • 910
  • 1
  • 6
  • 8
  • write your own query (see above) and it should work fine. the key seems to be not using the latest ODBC driver (we're using 5.1.12 successfully) – gap Mar 15 '13 at 21:04
0

Depending on what version of Windows you have, did you also go into Control Panel, administrative tools, data sources (odbc) to add the new MySQL driver you installed?

Netbob
  • 1