Error on PowerPivot table import wizard when getting external data - by Katherine Fraser

Status : 


Sign in
to vote
ID 766172 Comments
Status Active Workarounds
Type Bug Repros 2
Opened 10/4/2012 12:59:30 PM
Access Restriction Public


I am using:
 * Windows 7 Professional, 4.00 GB RAM, 32-bit operating system
 * Microsoft Office Professional Plus 2010, v14.0.6112 (32-bit)
 * MS SQL Server 2012 PowerPivot for Excel 32-bit, v 11.0.2100.60
 * MySQL Connector/ODBC 5.2

I can create an ODBC connection to MySQL with no problems. And I can even use this ODBC connection from the Data tab in Excel (not PowerPivot) to get data from Other Source, either by pulling a single table from MySQL database or editing the connection properties/definition to provide a query (command text) that joins multiple tables.

However, in PowerPivot...
1) If I try to get data from a SQL Server connection, I get an error: 
    Cannot connect to the data source because the SqlServer provider is not installed
2) If try to get data from a text file, I get an error: 
    Details: Failed to connect to the server. 
    Reason: Provider information is missing from the connection string
3) If I try to create a connection From Other Source/ODBC, I get an error:
    ERROR [HY010] [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.25a-27.1-log] or
    Test connection failed b/c of an error in initializing provider. Catastrophic failure.
**When I click for the dropdown list of databases in the Data Link Properties, the connection is made and list is shown.

On a different machine with the same versions of Windows, Office and PowerPivot, I am able to get PowerPivot to work just fine.  Note that in both the ODBC connection and Data Link in PowerPivot, the Initial Catalog must be left blank for the MySQL data pull to work.

Machine that works is 64-bit virtual image with MySQL ODBC 5.1 driver and user has admin rights.  Machine that does not work is 32-bit laptop with MySQL ODBC 5.2 driver and user does not have admin rights.
Sign in to post a comment.
Posted by ankit gujjar on 9/16/2014 at 4:24 AM
Hi All,

I'm having the same problem in using powerpivot for MySQL data source.

Repro steps:

1. Install mysql-connector-odbc-5.1.13-winx64.msi.
2. open "C:\Windows\SysWOW64\odbcad32.exe"
3. Click Add under user DSN
4. select MySQL ODBC 5.3 Unicode Driver
5. click Finish
6. in Popup window fill the data and click ok.
7. open excel 2010 version
8. click on PowerPivot tab ( this is an add-in, download this from Microsoft site for office 2010 and install. It will show you in a tab in excel)
9. click on PowerPivot window option given in very left side below the file tab.
10. Now click on "From other source" option
11. from list select "Others (OLEDB/ODBC) and next
12. click on build
13. fill the data that you used while creating DSN name
14. click Ok button than finish.
15. Now window will show you list of tables.
16. Select and table and click on Preview and filter option.
17. It will show you error like "Failed to retrieve data from kimai_evt. Reason: ERROR [42000] [MySQL][ODBC 5.3(w) Driver][mysqld-5.5.27]You have an error in your SQL syntax"

Expected: data should be visible there to filter.
Actual: Error coming there as I mentioned in step 17.

Hope this is simplest form to make you it verify and resolve it so we can further proceed with the same.
Posted by Wayne Robertson [MSFT] on 3/25/2013 at 6:42 PM
Take a look at the following post for one possible cause and the solution:
Posted by Wouter1231 on 2/14/2013 at 5:53 AM
I'm getting the same issue, upgrading the PowerPivot version does not solve it.

* Windows 7 Pro, 32 bit
* Office 2010 v14.0.6129
* PowerPivot v 11.0.3129.0
* MySQL connector ODBC 5.2.4

Also for me, I can pull data from the DB using the Data tab in Excel, query validation and data preview work in PowerPivot, only when clicking finish, the error pops up.
Posted by Microsoft on 1/23/2013 at 5:04 PM

Could you try uninstalling PowerPivot and installing the newer version of Microsoft® SQL Server® 2012 PowerPivot for Microsoft® Excel® from here:

SQL Analysis Services