在 Excel 2010 powerpivot 中访问 MySQL 数据库
我在 hostmonster.com 上有一个 MySQL 数据库，我正在尝试在 Excel powerpivot 中导入数据，但很难连接到它。
有谁知道Excel powerpivot是否可以连接到MySQL？
我看到错误
无法检索数据库列表。原因：无法连接到服务器。原因：登录超时已过 与 SQL Server 建立连接时发生与网络相关或特定于实例的错误。找不到服务器或无法访问服务器。检查实例名称是否正确以及 SQL Server 是否配置为允许远程连接。有关详细信息，请参阅 SQL Server 联机丛书。 命名管道提供程序：无法打开与 SQL Server [53] 的连接。
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].
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
当我发现这个时，我正在寻找一种将数据输入 MySQL 的方法（我知道，有更好的方法......）。因此，我能够通过 odbc 连接到 Excel，并使用 PowerPivot 2010 我能够很好地创建从 MySQL 到 PP 的连接。以下是我采取的步骤：
选择“从其他来源获取外部数据”。 （它是“来自 Azure DataMarket”右侧的数据库符号。）
在出现的“连接到数据源”表导入向导中，选择“其他 (OLEDB/ODBC)”
现在它需要将连接字符串连接到 MySQL 框，单击“构建”。这将打开“数据链接属性”对话框，选择“提供程序”选项卡，然后选择“用于 ODBC 驱动程序的 Microsoft OLE DB 提供程序”。选择“下一步”。现在在“连接”选项卡上，选择“指定数据源： - 使用数据源名称：MySQL。（如果没有这个，则说明您没有安装 MySQL ODBC 驱动程序。）
单击“测试”，如果 MySQL 的用户名和密码正确，测试应该会成功。现在“连接字符串”字段已填充：“Provider=MSDASQL;Persist Security Info=False;User ID=root;DSN=MySQL”就是我的。
下一步对我来说是一个重要的步骤。对话框显示“选择如何导入数据”，但是当我选择“从表和视图列表中选择以选择要导入的数据”时，由于某种原因找不到它们，我选择了“编写一个查询来指定要导入的数据” ”。
这打开了一个查询窗口，我添加了“select * from
MySQL db。table_Sample极限 0, 1000"
它验证了我的 SQL 语句并导入了数据。非常酷。
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:
Select "Get External Data from Other Sources." (It's the db symbol to the right of "From Azure DataMarket.")
In the Connect to a Data Source table import wizard that comes up, select "Others (OLEDB/ODBC)"
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.
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_SampleLIMIT 0, 1000"
It validated my SQL statements and imported the data. Very cool.
首先尝试通过窗口“ODBC 数据源管理器”测试连接。我还特别遇到了 PowerPivot 的问题，但在 Excel 中使用正常的数据连接确实有效。你也可以尝试一下。
编辑：刚刚发现大多数 PowerPivot 连接问题可以通过回退到较旧的 MySQL ODBC 驱动程序来解决。 3.51.30 版本可以解决这个问题。 http://dev.mysql.com/downloads/mirror.php?id=406107
应得的功劳：http://www.joyofdata.de/blog/how-to-set-up-powerpivot-and-make-it-talk-to-mysql
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
我不熟悉 powerpivot，但这里是 MySQL DB 的 ODBC 驱动程序的链接。安装后，我知道您可以从 Excel 连接到 MySQL 数据库。我猜 powerpivot 是一样的。 http://dev.mysql.com/downloads/connector/odbc/5.1.html
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
我尝试过同样的方法但没有成功。具体来说：
连接测试成功
SQL 语法错误（其中
当然不是我写的）。
所以幕后某个地方出了问题。
I've tried the same approach without success. Specifically:
connection test succeeds
an error in the SQL syntax (which of
course i didn't write).
So something's wrong somewhere under the hood.
根据您使用的 Windows 版本，您是否还进入控制面板、管理工具、数据源 (odbc) 添加您安装的新 MySQL 驱动程序？
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?