none
Cannot connect to MySQL database through Power Query

    Question

  • First, I have got an ODBC connection to MySQL working both from within the 'ODBC sources' program provided within Windows, and from within Excel itself by going through the 'Data Sources' tab on the ribbon and using the wizard.

    I can therefore pull in data from my MySQL into my workbook using this latter method. My issue is however that I cannot get PowerQuery working even following the instructions on the office site.

    1. When I click the Power Query tab I select the option to choose 'From database' > 'From MySQL Database'

    2. At this point I am asked for the Server Name and the Database Name; for the server name I am entering in the IP address at which the database can be reached, for the Database name I am putting the name of the database to which I wish to connect.

    3. I then get asked to enter the user credentials which I do, the same ones that I'm using for the working ODBC connection I already have.

    Once I have down this it opens the query editor window and I get the following error:

    "DataSource. Error: MySQL: Unable to connect to any of the specified MySQL hosts. Details: Message=unable to..., ErrorCode=-2147467259"

    Why might I not be able to connect? Also, once connected what should I see. When I see screenshots of functional PowerQuery windows it appears that the database tables appear in the left hand column of the Query Window, should this be the indication I'm looking for to know that it is successful?

    Tuesday, September 10, 2013 2:48 PM

Answers

  • Hi Dingle, unfortunately I don't think I'm going to be much help since it genuinely just started working all by itself.

    One thing that I did try is instead of putting an IP directly as 94.xx.xxx.x or whatever yours is, I put mine as http://94.xx.xxx.x, I don't think this was actually what fixed it but it is worth trying regardless.

    What is an m script and how do I produce it? I've not had to do that before.

    James

    Wednesday, September 11, 2013 2:05 PM

All replies

  • I have now managed to get the list of tables to appear in the navigator pane, however it is giving me the first 200 tables from all of the databases on my MySQL server and I do not know how to specify either all the tables from one database, or a single table from a single database,

    As for how I fixed the problem? No idea, it just started working!

    Could anyone help me with the filtering?

    Thanks,

    James

    Tuesday, September 10, 2013 3:14 PM
  • I had the same issue with not being able to see all the tables (and views), but haven't had a chance to work out why this is happening.

    I found that I could still connect to the table that I wanted by editing the script and typing in the correct table name.

    Best to start by connecting to an existing table and then editing the script.

    Cheers,

    Nadav

    Wednesday, September 11, 2013 12:58 PM
  • Thanks Nadav, could you tell me where to find the script to edit - I may try that myself.

    James

    Wednesday, September 11, 2013 1:41 PM
  • Do you mind expounding more on how you eventually got your credentials to work? Maybe you could post the m script from power query?? I'm having the exact same problem as what you said below! 

    "3. I then get asked to enter the user credentials which I do, the same ones that I'm using for the working ODBC connection I already have...As for how I fixed the problem? No idea, it just started working!"

    Wednesday, September 11, 2013 1:57 PM
  • Hi Dingle, unfortunately I don't think I'm going to be much help since it genuinely just started working all by itself.

    One thing that I did try is instead of putting an IP directly as 94.xx.xxx.x or whatever yours is, I put mine as http://94.xx.xxx.x, I don't think this was actually what fixed it but it is worth trying regardless.

    What is an m script and how do I produce it? I've not had to do that before.

    James

    Wednesday, September 11, 2013 2:05 PM