访问MySQL数据库到Excel 2010的powerpivot [英] Accessing MySQL DB into Excel 2010 powerpivot

问题描述

有没有人知道Excel中是否有Excel？ powerpivot甚至可以连接到MySQL？

错误我看到

无法检索数据库列表。原因：无法连接到服务器。原因：登录超时已过期
与SQL Server建立连接时发生与网络相关或实例特定的错误。找不到或无法访问服务器。检查实例名称是否正确，并且SQL Server配置为允许远程连接。有关更多信息，请参阅SQL Server联机丛书。
命名管道提供程序：无法打开与SQL Server的连接[53]。

解决方案

我正在寻找一种将数据输入MySQL的方式（我知道有更好的方法...）发现这个。所以我能够通过odbc连接到Excel并使用PowerPivot 2010我能够创建从MySQL到PP的连接很好。以下是我采取的步骤：

1. 选择从其他来源获取外部数据。 （它是从Azure DataMarket右侧的db符号。）




2. 在连接到数据源表导入向导中，选择其他（OLEDB / ODBC）




3. 现在，您希望连接字符串到您的MySQL框，点击Build。这将打开数据链接属性对话框，选择提供者选项卡，然后选择Microsoft OLE DB提供程序的ODBC驱动程序。选择下一步。现在在连接选项卡上，选择指定数据源： - 使用数据源名称：MySQL（如果没有，您没有安装MySQL ODBC驱动程序。）
   点击测试，如果您的MySQL用户和密码正确，则应该成功。现在，连接字符串字段已填充：Provider = MSDASQL; Persist Security Info = False; User ID = root; DSN = MySQL是我的




4. 下一步对我来说是一个很大的一个。该对话框显示选择如何导入数据，但是当我选择从表和视图列表中选择要导入的数据时，由于某种原因找不到它们，我选择了编写一个将指定数据导入。
   这提出了一个查询窗口，我添加了select * from MySQL db 。 table_Sample
   LIMIT 0，1000
   它验证了我的SQL语句并导入了数据。非常酷。

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

解决方案

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.

这篇关于访问MySQL数据库到Excel 2010的powerpivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！