Search code examples
mysqlexcelmysqlimportpowerpivot

MySQL ODBC Import Fail

I am importing data from MySQL to PowerPivot. I have all of the relevant drivers. When I try to import to PowerPivot I get the following errors:

When I select from a list of tables:

Failed to retrieve data from companies. Reason: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.58]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[companies].* FROM [companies]' at line 1

When I "write a query" that has been validated I receive:

The following system error occurred: Catastrophic failure A connection could not be made to the data source with the DataSourceID of 'dc746dc9-4aaa-4e03-b938-f84a128f5671', Name of 'Custom'. An error occurred while processing the 'Query' table. The operation has been cancelled.

However, I am able to import just fine into Excel. How do I fix the PowerPivot import?

Solution

  • Just today I was faced with this issue. It seems that Excel fails to import a table when it hits on something "indigestable" in a field. In my case the field type was text, so maybe some field just contained to much data. Then the whole import fails.

    So what I did was to choose the other option, to import using an SQL query.

    select field_a, field_b from the_table

    There you just leave out the column that causes the problem, 'Custom' in your case.

    If you need this column, then you might have a heuristic look at its content to find potential trouble makers.