从外部数据源导入数据 (Power Query)

从外部数据源导入数据 (Power Query)

使用 Excel 的"&转换 (Power Query) 从 各种数据源将数据导入到 Excel 中。 然后,可以使用查询编辑器编辑查询步骤以形成或转换数据。 有关详细信息,请参阅"形状数据"。

“数据”>“获取和转换”>“获取数据选项”

注意: 如果在 Excel 选项中启用了旧版向导, ("数据"部分) 。 有关步骤,请参阅本文的 Office 2007 选项卡。

连接到数据源

从 Excel 2016 开始,使用"&转换"连接到外部数据并执行高级查询。 它的工作方式主要与 Power Query 相同,但它不是加载项 - 它已安装,你将在功能区的"数据"选项卡上找到它。 以下部分提供连接到数据源的步骤 - 网页、文本文件、数据库、联机服务和 Excel 文件、表和范围。

使用查询编辑器

注意: 使用 "获取 和转换"加载、编辑或创建新查询时,& 编辑器。 若要在不加载或编辑现有工作簿查询的情况下查看查询编辑器,请在"数据"功能区选项卡的"获取 &转换数据"部分中,单击"从其他源获取数据>"或"空白查询>数据"。

Excel 365 中的查询编辑器

  1. 单击"数据"选项卡,然后从>获取数据>选择"从文本/CSV"。 如果看不到"获取数据"按钮,请单击"从文件>查询">选择"从CSV"或"从文本"。

    注意: 还可以还原旧连接器以模拟早期行为。 请参阅以下文章中的"如何还原旧的获取外部数据体验?"部分:统一获取数据& 转换。

  2. "逗号分隔值浏览 "对话框中,浏览或键入要查询的文件的路径。

  3. 单击“打开”。

注意: 如果要从 CSV 文件导入数据,Power Query 将自动检测列分隔符,包括列名称和类型。 例如,如果导入以下示例 CSV 文件,Power Query 会自动使用第一行作为列名,并更改每个列数据类型。

示例 CSV 文件

CSV 文件的图像

Power Query 会自动更改每个列数据类型:

  • 订单 ID 对数字的更改

  • 订单日期更改日期

  • 类别将保留 (默认列类型的)

  • 产品名称将保留 (默认列类型的文本)

  • 对数字的销售更改

在查询编辑器中,Power Query 会自动应用 FirstRowAsHeader 步骤和 ChangeType 步骤。 这些自动操作等效于手动 提升行 和手动更改每个列类型。

Power Query 自动检测列后,还可使用查询 编辑器Power Query编写公式。 例如:

= Csv.Document(File.Contents("C:\Examples\Products Categories and Orders.csv"),null,",",null,1252)

= Table.PromoteHeaders(Source)

= Table.TransformColumnTypes(FirstRowAsHeader,{{"OrderID", type number}, {"CustomerID", type text}, {"EmployeeID", type number}, {"OrderDate", type date}, {"RequiredDate", type date}, {"ShipName", type text}})

注意: 查询 编辑器 仅在加载、编辑或创建新查询时显示。 若要查看查询 编辑器 而不加载或编辑现有工作簿查询,请在"获取& 功能 区选项卡上,单击"获取数据">启动 Power Query 编辑器

  1. 选择数据区域内的任意单元格。

  2. 单击"数据"选项卡,>"表/区域"。

  3. 出现提示时,可在"从表"对话框中单击"范围选择"按钮,选择要用作数据源的特定区域。

    “从表”对话框

  4. 如果表或数据区域具有列标题,可以检查" 我的表"是否包含标题。 标头单元格用于定义查询的列名称。

  5. 在查询编辑器中,单击" 关闭并&加载"。

注意: 如果数据区域已定义为命名区域,或位于 Excel 表中,Excel 将自动感知整个区域,并加载到查询编辑器中。 将纯数据加载到查询编辑器中时,会自动将其转换为表。

可以使用查询 编辑器 为查询编写公式。

= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

查询编辑器公式示例

注意: 查询 编辑器 仅在加载、编辑或创建新查询时显示。 若要查看查询 编辑器 而不加载或编辑现有工作簿查询,请在"获取& 功能 区选项卡上,单击"获取数据">启动 Power Query 编辑器

  1. 单击"数据"选项卡,然后选择"从>获取数据>工作簿中。 如果看不到"获取数据"按钮,请单击"从文件>查询">"从工作簿"。

    注意: 还可以还原旧连接器以模拟早期行为。 请参阅以下文章中的"如何还原旧的获取外部数据体验?"部分:统一获取数据& 转换。

  2. "Excel 浏览" 对话框中,浏览或键入要查询的文件的路径。

  3. 单击“打开”。

    如果源工作簿具有命名区域,则区域的名称将用作数据集。

还可使用查询 编辑器 为查询编写公式。 例如:

= Excel.Workbook
 (File.Contents("C:\Example\Products and Orders.xlsx"))

注意: 查询 编辑器 仅在加载、编辑或创建新查询时显示。 若要查看查询 编辑器 而不加载或编辑现有工作簿查询,请在"获取& 功能 区选项卡上,单击"获取数据">启动 Power Query 编辑器

Surface Book 设备照片

使用 Excel 的"&转换"体验连接到网页并导入不同表中的信息。

  1. 单击“数据”选项卡,然后单击“新建查询”>“自其他来源”>“自网站”。

    注意: 如果看不到"新建查询"按钮,请单击"数据"选项卡,然后单击"从Web"。

  2. 在"从 Web"对话框中,输入网页URL,然后单击"确定"。

    Power Query > 自网站 >“输入 URL”对话框

    在本例中,我们将使用:http://en.wikipedia.org/wiki/UEFA_European_Football_Championship.

    如果网页需要用户凭据:

    • 在“访问网页”对话框中,单击凭据选项,然后提供身份验证值。

    • 单击“保存”。

  3. 单击“确定”。

  4. Power Query 将分析网页,并在“表格视图”中加载“导航器”窗格。

    如果你知道要连接到哪个表,请从列表中选择它。 在本例中,我们选择了“结果”表格。

    Power Query > 自网站 > 导航器表格视图

    否则,可切换到“Web 视图”并手动选择相应的表格。 在本例中,我们选择了“结果”表格。

    Power Query > 自网站 > 导航器 > Web 视图
  5. 单击“加载”,随后 Power Query 会将所选的 Web 数据加载到 Excel 中。

  1. 单击"数据 "选项卡 ,然后 数据库> 数据库 > 获取SQL Server数据。 如果看不到"获取数据"按钮,请单击"从数据库>查询">"从SQL Server查询"。

  2. "Microsoft SQL 数据库 "对话框中,SQL Server"服务器名称"框中 指定要连接到的服务器 。 (可选)也可以指定 数据库名称

  3. 如果要使用本机数据库查询导入数据,在"语句"框中指定SQL查询。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

    Power Query SQL Server 数据库连接对话框
  4. 选择“确定”。

  5. 选择身份验证模式以连接到 SQL Server 数据库。

    Power Query SQL Server 连接登录凭据
    1. Windows:这是默认选项。 如果要使用 Windows 身份验证进行连接,请选择此选项。

    2. 数据库: 如果要使用身份验证进行连接,SQL Server此选项。 选择此选项后,请指定用户名和密码以连接到 SQL Server 实例。

  6. 默认情况下, 选中 "加密连接"复选框以表示 Power Query 使用加密连接连接到数据库。 如果不希望使用加密连接进行连接,请清除此复选框,然后单击"连接"。

    如果未使用加密SQL Server建立到客户端的连接,Power Query 会提示使用未加密的连接进行连接。 在 消息 中单击"确定"以使用未加密的连接进行连接。

公式示例

您也可以使用查询 编辑器Power Query编写公式。

= Sql.Databases(".")
= Sql.Database(".","Contoso")

  1. 单击"数据"选项卡,然后从 ODBC>"其他源>数据" 如果看不到"获取数据"按钮,请转到"来自其他源>从 ODBC >查询"

  2. 在"从 ODBC"对话框中,选择数据源名称 (DSN) 。

  3. 输入连接字符串,然后按"确定"。

  4. 在"下一个"对话框中,从"默认"或"自定义"、Windows或"数据库连接"选项中选择,输入凭据,然后按Connect。

  5. "导航器"窗格中,选择要连接到的表或查询,然后按"加载"或"编辑"。

  1. 单击"数据"选项卡,然后从 Microsoft Access >数据库>获取数据。 如果未看到"获取数据"按钮,请单击"从数据库>查询> Access。

  2. "导入数据" 对话框中,浏览或键入要导入或链接到文件的文件 URL。

  3. 按照导航器 对话框中 的步骤连接到选择的表或查询。

  4. 单击"加载"或"编辑"。

  1. 单击"数据"选项卡,然后从 XML >文件>数据。 如果看不到"获取数据"按钮,请单击"从文件">"从 XML >查询"。

  2. 在" 从 XML 浏览 "对话框中,浏览或键入要导入或链接到文件的文件 URL。

  3. 单击“打开”。

    连接成功后,可以使用"导航器"窗格以表格形式浏览和预览 XML 文件中项目的集合。

您也可以使用查询 编辑器Power Query编写公式。 例如:

= Xml.Tables(File.Contents("C:\Downloads\XML Example.xml"))

注意: 查询 编辑器 仅在加载、编辑或创建新查询时显示。 若要查看查询 编辑器 而不加载或编辑现有工作簿查询,请在"获取& 功能 区选项卡上,单击"获取数据">启动 Power Query 编辑器

  1. 转到"数据>从数据库>从>外部Analysis Services。 如果看不到"获取数据"按钮,请单击"从其他源>外部数据">源Analysis Services。

    将显示数据连接向导。 此向导有三个窗格。

    • 连接到数据库服务器

    • 选择数据库和表

    • 保存数据连接文件并完成

  2. 在" 连接到数据库服务器 "窗格的"服务器 名称 "框中,键入 OLAP 服务器数据库服务器。

    提示: 如果你知道要连接到的脱机多维数据集文件的名称,可以键入完整的文件路径、文件名和扩展名。

  3. "登录凭据"下,执行下列操作之一,然后单击"下一步":

    • 若要使用当前的 Windows 用户名和密码,请单击"使用Windows 身份验证"。

    • 若要输入数据库用户名和密码,请单击"使用以下用户名和密码",然后在相应的"用户名和密码"框中键入用户名和密码

  4. "选择包含需要的数据的数据库"窗格中,选择一个数据库,然后单击"下一步"。

    若要连接到数据库中的 多维数据集 ,请确保已选择"连接到特定多维数据集或表",然后从列表中选择一个多维数据集。

  5. 在"保存数据连接文件"和"完成"窗格中的"文件名"框中,根据需要修改默认文件名 (可选) 。

  6. 单击 " 浏览"以更改"我的数据源" 的默认文件位置,或检查现有文件名。

  7. 在"说明"、"友好名称"和"搜索关键字"框中,键入文件说明、友好名称和常用搜索 (都是可选的) 。

  8. 若要确保在刷新数据透视表时使用该连接文件,请单击"始终"尝试使用此文件 刷新此数据

    选中此复选框可确保使用该连接文件的所有工作簿始终使用对连接文件的更新。

  9. 单击 " 完成"关闭数据连接向导。

  10. "导入数据"对话框中,在"选择要如何查看工作簿中的此数据"下,执行下列操作之一:

    • 若要仅创建数据透视表,请单击"数据透视表"。

    • 若要创建数据透视表和数据透视图,请单击"数据透视图"和"数据透视表"。

    • 若要将所选连接存储在工作簿中供以后使用,请单击"仅创建连接"。 此复选框可确保连接由包含您创建的多维数据集函数的公式使用,并且您不希望创建数据透视表。

  11. "要将数据放在何处"下,执行下列操作之一:

    • 若要将数据透视表放在现有工作表中,请选择"现有工作表",然后在要查找数据透视表的单元格区域键入第一个单元格的单元格引用。

      也可以单击"折叠对话框 按钮图像 以临时隐藏对话框,选择工作表上想要使用的开始单元格,然后按"展开对话框" 按钮图像

    • 若要将数据透视表放在从单元格 A1 开始的新工作表中,请单击"新建工作表"。

    • 若要验证或更改连接属性,请单击"属性",在"连接属性"对话框中进行必要的更改,然后单击"确定"。

在 Microsoft 365 专属 Excel :

  1. 在"数据"选项卡,单击"从> JSON获取>数据"

    "从 JSON 文件获取数据" 按钮

  2. 浏览到 JSON 文件位置,将其选中,并单击"打开"。

  3. 在查询编辑器加载数据后,单击"将>转换为表",并单击"关闭&加载"。

在 Excel 2016 中:

  1. 在"数据"选项卡,单击">源"中的"新建查询>空白查询

  2. 在查询编辑器中,单击"高级编辑器"。

  3. 按如下所示输入查询字符串,将"C:\Users\Name\Desktop\JSONTest.json"替换为 JSON 文件的路径。

    let
    
        Source = Json.Document(File.Contents("C:\Users\Name\Desktop\JSONTest.json")),
        #"Converted to Table" = Record.ToTable(Source)
    
    in
    
        #"Converted to Table"
    

在 Microsoft 365 专属 Excel :

  1. 在"数据"选项卡,单击"从文件>获取数据>PDF。

    从 "文件" 菜单的 "获取数据" 中的 PDF 连接器

  2. 选择 PDF 文件,然后单击"打开"。 导航器窗体将打开 PDF 并显示可用表。

    用于导入 PDF 数据的 "导航器" 对话框

  3. 选择要导入的表,然后执行下列操作之一:

    • 若要直接在 Excel 中查看数据,请单击"加载"。

    • 若要首先在 Power Query 中处理数据,请单击"转换数据"。

注意: 在使用 Power Query连接到 Oracle 数据库之前,计算机上需要 Oracle 客户端软件 v8.1.7 或更高。 若要安装 Oracle 客户端软件,请通过适用于 Visual Studio (12.1.0.2.4) 的 Oracle 开发人员工具转到 32 位 Oracle 数据访问组件 (ODAC) 以安装 32 位 Oracle 客户端, 或到 64 位 ODAC 12c 版本 4 (12.1.0.2.4) Xcopy for Windows x64 以安装 64 位 Oracle 客户端。

  1. 单击"数据"选项卡,然后Oracle >数据库>获取数据。 如果未看到"获取数据"按钮,请单击"从数据库>查询>从 Oracle数据库。

  2. "Oracle 数据库 "对话框中的服务器 名称中 ,指定要连接到的 Oracle 服务器。 如果需要 SID,可以"ServerName/SID"的形式指定此 SID。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 单击“确定”。

  5. 如果 Oracle 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 单击“连接”。

  1. 单击"数据"选项卡,然后Sharepoint 列表中>>"数据源"。 如果看不到"获取数据"按钮,请单击"从 Sharepoint 列表>>查询"

  2. 在出现的"Microsoft SharePoint 列表"对话框中,输入 SharePoint 网站的 URL。

    注意: 连接到 SharePoint 列表时,请输入网站 URL 而不是列表 URL。 在 Access SharePoint 对话框中,选择要正确针对网站进行身份验证的最常规 URL。 默认情况下,将选择最常规的 URL。

  3. 选择“”以继续操作。

  4. 在接下来 出现的 Access SharePoint 对话框中,选择凭据选项:

    1. 如果 SharePoint Server 不需要任何凭据,请选择"匿名"。

    2. 如果 SharePoint Server 需要 Windows 凭据,请选择 Windows。

    3. 如果 SharePoint Server 需要组织帐户凭据,请选择组织帐户。

  5. 选择"连接"。

    Excel Power Query 连接到 Sharepoint 列表连接对话框

  1. 单击"数据 "选项卡 ,然后 OData > 获取 来自其他> 数据源的数据。 如果未看到"获取数据"按钮,请单击"新建查询">OData 源>源中。

  2. "OData 源 "对话框中, 输入 OData 源的 URL。

  3. 选择“确定”。

  4. 如果 OData 源需要用户凭据,在 "访问 OData 源" 对话框中:

    1. 如果OData源需要 Windows 身份验证,请选择Windows。

    2. 如果 OData 源需要用户名和密码,请选择"基本"。

    3. 如果 OData 源需要市场帐户密钥,请选择"市场密钥"。 可以选择"获取 市场帐户密钥 "以订阅 Microsoft Azure OData 源。 还可以从"访问 OData Microsoft Azure "对话框注册市场。

    4. 如果 OData 源需要联合访问凭据,请单击"组织帐户"。 对于Windows Live™ ID,请登录到帐户。

    5. 选择“保存”。

注意: 连接到 OData 源支持 JSON 光数据服务格式。

  1. 单击"数据"选项卡,然后从 OLEDB >其他源>数据。 如果看不到"获取数据 " 按钮,请按照下面的 数据连接向导 说明进行操作。

  2. 在"从 OLE DB"对话框中,输入连接字符串,然后按"确定"。

  3. OLEDB提供程序对话框中,从"默认"或"自定义"、Windows或数据库连接选项中选择,输入相应的凭据,然后单击"连接"。

  4. "导航器"对话框中,选择要连接到的数据库和表或查询,然后按"加载"或"编辑"。

  5. Power Query 编辑器中,按 Close &加载

  1. 单击"数据"选项卡,然后从MySQL>数据库>获取数据。 如果未看到"获取数据"按钮,请单击"从数据库>查询"> MySQL 数据库

  2. 单击"数据"选项卡,然后从MySQL>数据库>获取数据。 如果未看到"获取数据"按钮,请单击"从数据库>查询"> MySQL 数据库

  3. "MySQL 数据库 "对话框中的服务器 名称中 ,指定要连接到的 MySQL 数据库服务器。

  4. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  5. 单击“确定”。

  6. 如果 MySQL 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 单击“连接”。

  1. 单击" 数据" 选项卡, 然后从 Microsoft Exchange >" 从> 数据源获取数据"。 如果未看到"获取数据"按钮,请单击"新建查询">来自Microsoft Exchange的其他>查询。

  2. "访问Exchange Server对话框中,指定电子邮件地址和密码

  3. 单击“保存”。

  4. "Microsoft Exchange 自动发现服务"对话框中,选择"允许"以允许 Exchange 服务信任你的凭据。

  1. 单击"数据"选项卡,然后Active Directory >其他源>数据源。 如果未看到"获取数据"按钮,请单击"从 Active Directory >>查询"

  2. 在"Active Directory"对话框中输入域。

  3. 域的"Active Directory域"对话框中,单击"使用我的当前凭据"或"使用备用凭据"。 对于使用备用证书身份验证,请输入用户名和密码

  4. 单击“连接”。

  5. 连接成功后,可以使用"导航器"窗格浏览 Active Directory 中所有可用的域,并向下钻取 Active Directory 信息,包括用户、帐户和计算机。

注意: 

  • 只有在拥有 Office 2019 或Microsoft 365订阅时,Excel for Windows 中才提供此功能。 如果你是 Microsoft 365 订阅者,请确保拥有最新版本的 Office

  • 在使用 Power Query 连接到 SAP HANA 数据库之前,需要 在计算机上安装 SAP HANA ODBC 驱动程序。 选择与 Power Query 安装匹配的驱动程序 (32 位或 64 位) 。

  • 需要一个 SAP 帐户才能登录到网站并下载驱动程序。 如果不确定,请联系你组织的 SAP 管理员。

连接到 SAP HANA 数据库:

  1. SAP HANA >数据库>查询>"新建查询"。

    "数据" 选项卡中的 "SAP HANA 数据库" 选项
  2. "SAP HANA 数据库 "对话框中,指定要连接到的服务器。 服务器名称应遵循 ServerName:Port 格式

    "SAP HANA 数据库" 对话框
  3. (可选)如果要使用本机数据库查询导入数据,请单击"高级"选项,SQL"语句"框中输入查询。

  4. 单击“确定”。

  5. 如果 SAP HANA 服务器需要数据库用户凭据,然后在" 访问 SAP HANA 数据库 "对话框中执行以下操作:

    1. 单击" 数据库" 选项卡,并输入用户名和密码。

    2. 单击“连接”。

警告: 

  • 在连接到 IBM DB2 数据库之前,需要在计算机上安装IBM DB2 数据服务器驱动程序 (最低要求是 IBM Data Server Driver Package (DS Driver) ) 。 选择与 Power Query 安装匹配的驱动程序 (32 位或 64 位) 。

  • IBM 在 Windows 8 上安装 IBM DB2 数据服务器驱动程序时报告了已知问题如果使用的是 Windows 8,并且想要使用 Power Query 连接到 IBM DB2,则需要执行其他安装步骤。 查找有关 Windows 8 上的 IBM DB2 数据服务器驱动程序的信息。.

  1. 单击"数据 "选项卡 ,然后 从 IBM DB2 > 数据库 > 获取数据。 如果看不到"获取数据"按钮,请单击"从数据库>查询"> IBM DB2 数据库

  2. "IBM DB2 数据库 "对话框中的服务器 名称中 ,指定要连接到的 IBM DB2 数据库服务器。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 单击“确定”。

  5. 如果 IBM DB2 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 单击“连接”。

注意: 在 Power Query 中连接到 PostgreSQL 数据库之前,需要在计算机上安装 用于 PostgreSQL 的 Ngpsql 数据提供程序。 选择与 Office 版本匹配的驱动程序 (32 位或 64 位) 。 有关详细信息,请参阅:我使用的是哪个版本的 Office? 另请确保已在计算机配置中注册提供程序,使其与设备上的最新 .NET 版本匹配。

  1. 单击"数据"选项卡,然后从>数据库>获取数据。 如果未看到"获取数据"按钮,请单击"从数据库>查询>从 PostgreSQL 数据库

  2. "PostgreSQL 数据库"对话框中,在"服务器名称"部分中指定要连接到的 PostgreSQL 数据库 服务器。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 选择“确定”。

  5. 如果 PostgreSQL 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 选择"连接"。

  1. 导航到https://web.powerapps.com/

  2. 选择要连接到 的环境

  3. 在菜单中,选择"高级自定义>开发人员资源>图标

  4. 复制 实例 Web API 值。 

    注意: 

    • URL 格式将类似https://<tenant>.crm.dynamics.com/api/data/v9.0.

    • 用于连接的 URL 的确切格式取决于你的区域以及你使用的应用的 CDS 版本。 有关详细信息,请参阅:Web API URL 和版本。

  5. 选择"数据"选项卡,然后从Dynamics 365 & Online > >Online Services获取数据>,然后 (转换数据) 。

  6. 在对话框中,选中"基本"选项,输入 CDS for Apps 连接的Web API URL,并单击"确定"。

    • 如果选择" 高级" 选项,可以将某些附加参数追加到查询,以控制返回的数据。 有关详细信息,请参阅:使用 Web API 查询数据

  7. 选择"组织帐户"。

    • 如果未使用用于访问 CDS for Apps 的 Microsoft 工作或学校帐户登录,请单击"登录"并输入帐户用户名和密码。

  8. 单击“连接”。

  9. "导航器" 对话框中,选择要检索的数据。

  10. 如果数据可以按现有方式导入,请选择"加载"选项,否则请选择"编辑"选项以打开Power Query 编辑器

    注意: Power Query 编辑器提供多个选项来修改返回的数据。 例如,你可能希望导入的列数少于源数据包含的列数。 在这种情况下,请转到"开始"选项卡>管理列>选择列,选择要保留的列,然后单击"确定"。 准备就绪后,单击"关闭& 加载 ",将修改后的数据返回到 Excel。

注意: 在连接到 Teradata 数据库之前,需要计算机上用于 Teradata 的 .NET 数据提供程序。

  1. 单击"数据"选项卡,&转换数据>数据库>从Teradata>获取数据。

  2. "Teradata 数据库 "对话框中的服务器名称 ,指定要连接到的 Teradata 服务器。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 选择“确定”。

  5. 如果 Teradata 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 单击“保存”。

重要: Facebook 数据连接器停用通知   从 Excel 中的 Facebook 导入和刷新数据将在 2020 年 4 月停止工作。 在此之前,仍可以使用 Facebook Get & Transform (Power Query) 连接器,但从 2020 年 4 月开始,将无法连接到 Facebook 并收到错误消息。 我们建议尽快修改或删除使用 Facebook 连接器&转换 (Power Query) 查询,以避免意外的结果。

注意: 如果这是第一次连接到 Facebook,将要求你提供凭据。 使用 Facebook 帐户登录,并允许访问 Power Query 应用程序。 可以通过单击"不要对此连接器选项再次发出警告"来 关闭将来的 提示。

  1. 在" 数据" 选项卡上 ,单击" 从 Online Services > 从 Facebook 获取> 数据" 如果未看到"获取数据"按钮,请单击"新建查询">来自Facebook 的其他>查询

  2. Facebook 对话框中,使用 "Me"、" 用户名"或"对象 ID"连接到 Facebook。

    注意: Facebook 用户名不同于登录电子邮件。

  3. 从"连接"下拉列表 选择要连接到的 类别。 例如,选择 "好友 "可让你访问 Facebook 好友类别中 提供的所有 信息。

  4. 单击“确定”。

  5. 如有必要,请在Access Facebook对话框中单击"登录",然后输入 Facebook 电子邮件或电话号码以及密码。 可以检查保持登录状态的选项。 登录后,单击"连接"。

  6. 连接成功后,可以预览包含所选类别相关信息的表。 例如,如果选择"好友"类别,Power Query 将按名称呈现包含 Facebook 好友的表。

  7. 单击"加载"或"编辑"。

可以使用 查询编辑器为 Power Query 编写 公式

= Facebook.Graph("https://graph.facebook.com/v2.8/me/friends")

带有 Facebook 公式的 Power Query 编辑器

注意: 查询 编辑器 仅在加载、编辑或创建新查询时显示。 若要查看查询 编辑器 而不加载或编辑现有工作簿查询,请在"获取& 功能 区选项卡上,单击"获取数据">启动 Power Query 编辑器

注意: 

  • 在连接到 SAP SQL Anywhere 数据库之前,需要在计算机上 SQL SAP SQL Anywhere 驱动程序。 选择与 Excel 安装匹配的驱动程序 (32 位或 64 位) 。

  1. 单击"数据"选项卡,然后从>数据库>从 Sybase 数据库获取数据。 如果未看到"获取数据"按钮,请单击"从数据库>查询>从 Sybase 数据库

  2. "Sybase 数据库 "对话框中,在"服务器名称"框中指定要连接到 Sybase 服务器。 (可选)也可以指定 数据库名称

  3. 如果要使用本机数据库查询导入数据,在"语句"框中指定SQL查询。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 单击“确定”。

  5. 默认情况下,将 选中 "加密连接"复选框,以便 Power Query 使用简单的加密连接连接到数据库。

  6. 单击“连接”。

Microsoft Azure Blob 存储是一种用于存储大量非结构化数据(如图像、视频、音频和文档)的服务,这些数据可以通过 HTTP 或 HTTPS 从世界上的任何位置访问。 有关 Azure Blob 存储服务的信息,请参阅"如何使用 Blob 存储"。

  1. 单击" 数据" 选项卡,然后& 转换 数据> Azure > 从 Azure Blob > 获取数据。 如果未看到"获取数据"按钮,请单击"从 Azure >从 Microsoft Azure Blob >新建查询"

  2. "Microsoft Azure Blob 存储"对话框中,输入 Microsoft Azure 存储帐户名称或 URL,并单击"确定"。

  3. 如果是首次连接到 Blob 存储服务,系统会提示输入并保存存储访问密钥。 在"访问 Microsoft Azure Blob 存储"对话框中,在"帐户密钥"框中输入存储访问密钥,并单击"保存"。

    注意: 如果需要检索存储访问密钥,请浏览到Microsoft Azure 门户,选择存储帐户,然后单击页面底部的"管理访问密钥"图标。 单击主键右边的复制图标,然后将值粘贴到"帐户密钥 " 框中。

  4. 查询 编辑器 列出 Blob 存储中 Microsoft Azure 容器。 在导航器中,选择要从中导入数据的容器,然后单击"应用&关闭"。

  1. 单击"数据"选项卡,然后从Azure > HDFS > Azure HDInsight (获取) 。 如果未看到"获取数据"按钮,请单击"从Azure >从>HDInsight Microsoft Azure 查询"

  2. 输入HDInsight 群集关联的 Microsoft Azure Blob 存储帐户的帐户名或URL,并单击"确定"。

  3. Access Microsoft Azure HDInsight对话框中,输入帐户密钥,并单击"连接"。

  4. 在"导航器"对话框中 选择群集 ,然后查找并选择内容文件。

  5. 单击 " 加载"以加载所选表,或 单击" 编辑"以在加载之前执行其他数据筛选器和转换。

可以使用 Power Query 加载项连接到外部数据源并执行高级数据分析。 以下部分提供连接到数据源的步骤 - 网页、文本文件、数据库、联机服务和 Excel 文件、表和范围。

重要: 在 Excel 2013 中可使用 Power Query 之前,必须激活它:单击"文件">"选项>加载项。 在底部的“管理”部分中,从下拉列表中选择“COM 加载项”选项,然后单击“Go”。 单击“Power Query”复选框,再单击“确定”。 Power Query 功能区应会自动显示,但如果未显示,请关闭并重新启动 Excel。

使用查询编辑器

注意: 使用 Power Query 加载、编辑或创建新查询时,将显示 查询编辑器。 以下视频显示在 Excel 工作簿中编辑查询后显示的“查询编辑器”窗口。 若要查看查询编辑器而不加载或编辑现有工作簿查询,请从Power Query功能区选项卡的"获取外部数据"部分选择"从其他源">空白查询"。 以下视频介绍一种显示查询编辑器的方法。

如何在 Excel 中查看查询编辑器

  1. Power Query 功能区选项卡中,单击"从文件">"从CSV"或"从文本"。

  2. "逗号分隔值浏览 "对话框中,浏览或键入要查询的文件的路径。

  3. 单击“打开”。

注意: 如果要从 CSV 文件导入数据,Power Query 将自动检测列分隔符,包括列名称和类型。 例如,如果导入以下示例 CSV 文件,Power Query 会自动使用第一行作为列名,并更改每个列数据类型。

示例 CSV 文件

CSV 文件的图像

Power Query 会自动更改每个列数据类型:

  • 订单 ID 对数字的更改

  • 订单日期更改日期

  • 类别将保留 (默认列类型的)

  • 产品名称将保留 (默认列类型的文本)

  • 对数字的销售更改

在查询编辑器中,Power Query 会自动应用 FirstRowAsHeader 步骤和 ChangeType 步骤。 这些自动操作等效于手动 提升行 和手动更改每个列类型。

Power Query 自动检测列后,还可使用查询 编辑器Power Query编写公式。 例如:

= Csv.Document(File.Contents("C:\Examples\Products Categories and Orders.csv"),null,",",null,1252)

= Table.PromoteHeaders(Source)

= Table.TransformColumnTypes(FirstRowAsHeader,{{"OrderID", type number}, {"CustomerID", type text}, {"EmployeeID", type number}, {"OrderDate", type date}, {"RequiredDate", type date}, {"ShipName", type text}})

注意: 仅当您使用 Power Query 加载、编辑或创建新查询时,“查询编辑器”才会显示。 以下视频显示从Excel 工作簿编辑查询后出现的 Excel 2013 中的"查询编辑器"窗口。 若要查看查询编辑器而不加载或编辑现有工作簿查询,请从Power Query功能区选项卡的"获取外部数据"部分选择"从其他源">空白查询"。 以下视频介绍一种显示查询编辑器的方法。

如何在 Excel 中查看查询编辑器

  1. 选择数据区域内的任意单元格。

  2. Power Query 功能区选项卡中,单击"从表"。

    连接到 Excel 模拟运算表

  3. 出现提示时,可在"从表"对话框中单击"范围选择"按钮,选择要用作数据源的特定区域。

    “从表”对话框

  4. 如果数据区域包含列标题,可以检查"表包含标题"。 区域标题单元格用于设置查询的列名称。

  5. 在查询编辑器中,单击" 关闭并&加载"。

注意: 如果数据区域已定义为命名区域,或位于 Excel 表中,则 Power Query 将自动感知整个区域,并加载到查询编辑器中。 将纯数据加载到查询编辑器中时,会自动将其转换为表。

可以使用 查询编辑器为 Power Query 编写 公式

= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

查询编辑器公式示例

注意: 仅当您使用 Power Query 加载、编辑或创建新查询时,“查询编辑器”才会显示。 以下视频显示在 Excel 工作簿中编辑查询后显示的“查询编辑器”窗口。 若要查看查询编辑器而不加载或编辑现有工作簿查询,请从Power Query功能区选项卡的"获取外部数据"部分选择"从其他源">空白查询"。 以下视频介绍一种显示查询编辑器的方法。

如何在 Excel 中查看查询编辑器

  1. Power Query 功能区 选项卡中,单击 "从文件 > Excel 中

  2. "Excel 浏览" 对话框中,浏览或键入要查询的文件的路径。

  3. 单击“打开”。

    如果源工作簿具有命名区域,则区域的名称将用作数据集。

您也可以使用查询 编辑器Power Query编写公式。 例如:

= Excel.Workbook
 (File.Contents("C:\Example\Products and Orders.xlsx"))

注意: 仅当您使用 Power Query 加载、编辑或创建新查询时,“查询编辑器”才会显示。 以下视频显示从Excel 工作簿编辑查询后出现的 Excel 2013 中的"查询编辑器"窗口。 若要查看查询编辑器而不加载或编辑现有工作簿查询,请从Power Query功能区选项卡的"获取外部数据"部分选择"从其他源">空白查询"。 以下视频介绍一种显示查询编辑器的方法。

如何在 Excel 中查看查询编辑器

注意: 在某些设置中尝试从旧版 Excel 文件或 Access 数据库导入数据时,可能会遇到 Microsoft Access 数据库引擎 (Microsoft.ACE.OLEDB.12.0 提供程序) 未在本地计算机上注册的错误。 此错误发生在仅安装了 Office 2013 的系统上。 若要解决此错误,请下载以下资源,确保可以继续尝试访问的数据源。

  1. 单击“Power Query”功能区,然后单击“自网站”。

  2. 在“自网站”对话框中,输入网页 URL,然后按“确定”。

    Power Query > 自网站 >“输入 URL”对话框

    在本例中,我们将使用:http://en.wikipedia.org/wiki/UEFA_European_Football_Championship.

    如果网页需要用户凭据:

    • 在“访问网页”对话框中,单击凭据选项,然后提供身份验证值。

    • 单击“保存”。

  3. 单击“确定”。

  4. Power Query 将分析网页,并在“表格视图”中加载“导航器”窗格。

    如果知道要连接到哪个表格,则从列表中单击它。 在本例中,我们选择了“结果”表格。

    Power Query > 自网站 > 导航器表格视图

    否则,可切换到“Web 视图”并手动选择相应的表格。 在本例中,我们选择了“结果”表格。

    Power Query > 自网站 > 导航器 > Web 视图
  5. 单击“加载”,随后 Power Query 会将所选的 Web 数据加载到 Excel 中。

  1. Power Query 功能区 选项卡中,单击 "从 数据库">" 从SQL Server数据库"。

    数据库选项中的 Power Query
  2. "Microsoft SQL 数据库 "对话框中,SQL Server"服务器名称"框中 指定要连接到的服务器 。 (可选)也可以指定 数据库名称

  3. 如果要使用本机数据库查询导入数据,在"语句"框中指定SQL查询。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

    Power Query SQL Server 数据库连接对话框
  4. 选择“确定”。

  5. 选择身份验证模式以连接到 SQL Server 数据库。

    Power Query SQL Server 连接登录凭据
    1. Windows:这是默认选项。 如果要使用 Windows 身份验证进行连接,请选择此选项。

    2. 数据库:如果要使用身份验证进行连接,SQL Server此选项。 选择此选项后,请指定用户名和密码以连接到 SQL Server 实例。

  6. 默认情况下, 选中 "加密连接"复选框以表示 Power Query 使用加密连接连接到数据库。 如果不希望使用加密连接进行连接,请清除此复选框,然后单击"连接"。

    如果未使用加密SQL Server建立到客户端的连接,Power Query 会提示使用未加密的连接进行连接。 在 消息 中单击"确定"以使用未加密的连接进行连接。

公式示例

您也可以使用查询 编辑器Power Query编写公式。

= Sql.Databases(".")
= Sql.Database(".","Contoso")

  1. 单击功能区上的"Power Query"选项卡,然后选择"从 ODBC >>外部数据"。

  2. 在"从 ODBC"对话框中,选择数据源名称 (DSN) 。

  3. 输入连接字符串,然后按"确定"。

  4. 在"下一个"对话框中,从"默认"或"自定义"、Windows或"数据库连接"选项中选择,输入凭据,然后按Connect。

  5. "导航器"窗格中,选择要连接到的表或查询,然后按"加载"或"编辑"。

  1. Power Query 功能区选项卡中,选择"从数据库> Access 数据库"

    从 "数据库" 对话框获取数据

  2. "浏览 "对话框中,浏览或键入文件 URL 以导入或链接到文件。

  3. 按照导航器 对话框中 的步骤连接到选择的表或查询。

  4. 单击"加载"或"编辑"。

  1. Power Query 功能区选项卡中,单击"从文件"> XML"。

    "从文件中查询 Power Query" 对话框
  2. 在" 从 XML 浏览 "对话框中,浏览或键入要导入或链接到文件的文件 URL。

  3. 单击“打开”。

    连接成功后,可以使用"导航器"窗格以表格形式浏览和预览 XML 文件中项目的集合。

您也可以使用查询 编辑器Power Query编写公式。 例如:

= Xml.Tables(File.Contents("C:\Downloads\XML Example.xml"))

注意: 仅当您使用 Power Query 加载、编辑或创建新查询时,“查询编辑器”才会显示。 以下视频显示在 Excel 工作簿中编辑查询后显示的“查询编辑器”窗口。 若要查看查询编辑器而不加载或编辑现有工作簿查询,请从Power Query功能区选项卡的"获取外部数据"部分选择"从其他源">空白查询"。 以下视频介绍一种显示查询编辑器的方法。

  1. 在"数据"选项卡上的"获取外部数据"组中,单击"从其他源",然后单击"从Analysis Services。

    Excel 功能区图像

    将显示数据连接向导。 此向导有三个窗格。

    • 连接到数据库服务器

    • 选择数据库和表

    • 保存数据连接文件并完成

  2. 在" 连接到数据库服务器 "窗格的"服务器 名称 "框中,键入 OLAP 服务器数据库服务器。

    提示: 如果你知道要连接到的脱机多维数据集文件的名称,可以键入完整的文件路径、文件名和扩展名。

  3. "登录凭据"下,执行下列操作之一,然后单击"下一步":

    • 若要使用当前的 Windows 用户名和密码,请单击"使用Windows 身份验证"。

    • 若要输入数据库用户名和密码,请单击"使用以下用户名和密码",然后在相应的"用户名和密码"框中键入用户名和密码

  4. "选择包含需要的数据的数据库"窗格中,选择一个数据库,然后单击"下一步"。

    若要连接到数据库中的 多维数据集 ,请确保已选择"连接到特定多维数据集或表",然后从列表中选择一个多维数据集。

  5. 在"保存数据连接文件"和"完成"窗格中的"文件名"框中,根据需要修改默认文件名 (可选) 。

    单击 " 浏览"以更改"我的数据源" 的默认文件位置,或检查现有文件名。

  6. 在"说明"、"友好名称"和"搜索关键字"框中,键入文件说明、友好名称和常用搜索 (都是可选的) 。

  7. 若要确保在刷新数据透视表时使用该连接文件,请单击"始终"尝试使用此文件 刷新此数据

    选中此复选框可确保使用该连接文件的所有工作簿始终使用对连接文件的更新。

  8. 单击 " 完成"关闭数据连接向导。

  9. "导入数据"对话框中,在"选择要如何查看工作簿中的此数据"下,执行下列操作之一:

    • 若要仅创建数据透视表,请单击"数据透视表"。

    • 若要创建数据透视表和数据透视图,请单击"数据透视图"和"数据透视表"。

    • 若要将所选连接存储在工作簿中供以后使用,请单击"仅创建连接"。 此复选框可确保连接由包含您创建的多维数据集函数的公式使用,并且您不希望创建数据透视表。

  10. "要将数据放在何处"下,执行下列操作之一:

    • 若要将数据透视表放在现有工作表中,请选择"现有工作表",然后在要查找数据透视表的单元格区域键入第一个单元格的单元格引用。

      也可以单击"折叠对话框 按钮图像 以临时隐藏对话框,选择工作表上想要使用的开始单元格,然后按"展开对话框" 按钮图像

  11. 若要将数据透视表放在从单元格 A1 开始的新工作表中,请单击"新建工作表"。

  12. 若要验证或更改连接属性,请单击"属性",在"连接属性"对话框中进行必要的更改,然后单击"确定"。

  1. "Power Query" 选项卡上,单击 "从其他源 "> 空白查询"。

  2. 在查询编辑器中,单击"高级编辑器"。

  3. 按如下所示输入查询字符串,将"C:\Users\Name\Desktop\JSONTest.json"替换为 JSON 文件的路径。

    let
    
        Source = Json.Document(File.Contents("C:\Users\Name\Desktop\JSONTest.json")),
        #"Converted to Table" = Record.ToTable(Source)
    
    in
    
        #"Converted to Table"
    

注意: 在使用 Power Query连接到 Oracle 数据库之前,计算机上需要 Oracle 客户端软件 v8.1.7 或更高。 若要安装 Oracle 客户端软件,请通过适用于 Visual Studio (12.1.0.2.4) 的 Oracle 开发人员工具转到 32 位 Oracle 数据访问组件 (ODAC) 以安装 32 位 Oracle 客户端, 或到 64 位 ODAC 12c 版本 4 (12.1.0.2.4) Xcopy for Windows x64 以安装 64 位 Oracle 客户端。

  1. Power Query 功能区选项卡中,单击"从数据库>从 Oracle 数据库。

    数据库选项中的 Power Query
  2. "Oracle 数据库 "对话框中的服务器 名称中 ,指定要连接到的 Oracle 服务器。 如果需要 SID,可以"ServerName/SID"的形式指定此 SID。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 单击“确定”。

  5. 如果 Oracle 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 单击“连接”。

  1. Power Query 功能区选项卡中,选择"从SharePoint >源"。

    Power Query 从其他源获取数据对话框
  2. 在出现的"Microsoft SharePoint 列表"对话框中,输入 SharePoint 网站的 URL。

    注意: 连接到 SharePoint 列表时,请输入网站 URL 而不是列表 URL。 在 Access SharePoint 对话框中,选择要正确针对网站进行身份验证的最常规 URL。 默认情况下,将选择最常规的 URL。

  3. 选择“”以继续操作。

  4. 在接下来 出现的 Access SharePoint 对话框中,选择凭据选项:

    1. 如果 SharePoint Server 不需要任何凭据,请选择"匿名"。

    2. 如果 SharePoint Server 需要 Windows 凭据,请选择 Windows。

    3. 如果 SharePoint Server 需要组织帐户凭据,请选择组织帐户。

  5. 选择"连接"。

    Excel Power Query 连接到 Sharepoint 列表连接对话框

  1. Power Query 功能 区选项卡中 ,选择" 从其他源> 从 OData 源

    Power Query 从其他源获取数据对话框
  2. "OData 源 "对话框中, 输入 OData 源的 URL。

  3. 选择“确定”。

  4. 如果 OData 源需要用户凭据,在 "访问 OData 源" 对话框中:

    1. 如果OData源需要 Windows 身份验证,请选择Windows。

    2. 如果 OData 源需要用户名和密码,请选择"基本"。

    3. 如果 OData 源需要市场帐户密钥,请选择"市场密钥"。 可以选择"获取 市场帐户密钥 "以订阅 Microsoft Azure OData 源。 还可以从"访问 OData Microsoft Azure "对话框注册市场。

    4. 如果 OData 源需要联合访问凭据,请单击"组织帐户"。 对于Windows Live™ ID,请登录到帐户。

    5. 选择“保存”。

注意: 连接到 OData 源支持 JSON 光数据服务格式。

  1. 单击"数据"选项卡,然后从 OLEDB >其他源>数据。 如果看不到"获取数据 " 按钮,请按照下面的 数据连接向导 说明进行操作。

  2. 在"从 OLE DB"对话框中,输入连接字符串,然后按"确定"。

  3. OLEDB提供程序对话框中,从"默认"或"自定义"、Windows或数据库连接选项中选择,输入相应的凭据,然后单击"连接"。

  4. "导航器"对话框中,选择要连接到的数据库和表或查询,然后按"加载"或"编辑"。

  5. Power Query 编辑器中,按 Close &加载

  1. Power Query 功能 区选项卡中,单击 " 从数据库> MySQL 数据库

    数据库选项中的 Power Query
  2. "MySQL 数据库 "对话框中的服务器 名称中 ,指定要连接到的 MySQL 数据库服务器。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 单击“确定”。

  5. 如果 MySQL 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 单击“连接”。

  1. Power Query 功能区选项卡中,选择"来自其他源>来自Microsoft Exchange。

    Power Query 数据源
  2. "访问Exchange Server对话框中,指定电子邮件地址和密码

  3. 单击“保存”。

  4. "Microsoft Exchange 自动发现服务"对话框中,选择"允许"以允许 Exchange 服务信任你的凭据。

重要: 请确保已下载并安装 Power Query 加载项。

  1. Power Query 功能区选项卡中,单击"从 Active Directory >源"。

    Power Query 从其他源获取数据对话框

  2. 在"Active Directory"对话框中输入域。

  3. 域的"Active Directory域"对话框中,单击"使用我的当前凭据"或"使用备用凭据"。 对于使用备用凭据身份验证,请输入用户名和密码

  4. 单击“连接”。

  5. 连接成功后,可以使用"导航器"窗格浏览 Active Directory 中所有可用的域,并向下钻取 Active Directory 信息,包括用户、帐户和计算机。

警告: 

  • 在连接到 IBM DB2 数据库之前,需要在计算机上安装IBM DB2 数据服务器驱动程序 (最低要求是 IBM Data Server Driver Package (DS Driver) ) 。 选择与 Power Query 安装匹配的驱动程序 (32 位或 64 位) 。

  • IBM 在 Windows 8 上安装 IBM DB2 数据服务器驱动程序时报告了已知问题如果使用的是 Windows 8,并且想要使用 Power Query 连接到 IBM DB2,则需要执行其他安装步骤。 查找有关 Windows 8 上的 IBM DB2 数据服务器驱动程序的信息。

  1. Power Query 功能区 选项卡中 ,单击" 从数据库> 从 IBM DB2 数据库

    数据库选项中的 Power Query
  2. "IBM DB2 数据库 "对话框中的服务器 名称中 ,指定要连接到的 IBM DB2 数据库服务器。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 单击“确定”。

  5. 如果 IBM DB2 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 单击“连接”。

注意: 在 Power Query 中连接到 PostgreSQL 数据库之前,需要在计算机上安装 用于 PostgreSQL 的 Ngpsql 数据提供程序。 选择与 Office 版本匹配的驱动程序 (32 位或 64 位) 。 有关详细信息,请参阅:我使用的是哪个版本的 Office? 另请确保已在计算机配置中注册提供程序,使其与设备上的最新 .NET 版本匹配。

  1. Power Query 功能 区选项卡中 ,选择" 从数据库> 从 PostgreSQL 数据库

    数据库选项中的 Power Query
  2. "PostgreSQL 数据库"对话框中,在"服务器名称"部分中指定要连接到的 PostgreSQL 数据库 服务器。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 选择“确定”。

  5. 如果 PostgreSQL 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 选择"连接"。

  1. 导航到https://web.powerapps.com/

  2. 选择要连接到 的环境

  3. 在菜单中,选择"高级自定义>开发人员资源>图标

  4. 复制 实例 Web API 值。 

    注意: 

    • URL 格式将类似https://<tenant>.crm.dynamics.com/api/data/v9.0.

    • 用于连接的 URL 的确切格式取决于你的区域以及你使用的应用的 CDS 版本。 有关详细信息,请参阅:Web API URL 和版本。

  5. 选择"数据"选项卡,然后从 Online Services >从联机>从Dynamics 365 (联机) 。

    • 如果未看到"获取数据"按钮,请单击"从其他源>查询">从Dynamics 365 (联机) 。

  6. 在对话框中,选中"基本"选项,输入 CDS for Apps 连接的Web API URL,并单击"确定"。

    • 如果选择" 高级" 选项,可以将某些附加参数追加到查询,以控制返回的数据。 有关详细信息,请参阅:使用 Web API 查询数据

  7. 选择"组织帐户"。

    • 如果未使用用于访问 CDS for Apps 的 Microsoft 工作或学校帐户登录,请单击"登录"并输入帐户用户名和密码。

  8. 单击“连接”。

  9. "导航器" 对话框中,选择要检索的数据。

  10. 如果数据可以按现有方式导入,请选择"加载"选项,否则请选择"编辑"选项以打开Power Query 编辑器

    注意: Power Query 编辑器提供多个选项来修改返回的数据。 例如,你可能希望导入的列数少于源数据包含的列数。 在这种情况下,请转到"开始"选项卡>管理列>选择列,选择要保留的列,然后单击"确定"。 准备就绪后,单击"关闭& 加载 ",将修改后的数据返回到 Excel。

注意: 在连接到 Teradata 数据库之前,需要计算机上用于 Teradata 的 .NET 数据提供程序。

  1. Power Query 功能区 选项卡中 ,选择" 从数据库> 从 Teradata 数据库

    数据库选项中的 Power Query
  2. "Teradata 数据库 "对话框中的服务器名称 ,指定要连接到的 Teradata 服务器。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 选择“确定”。

  5. 如果 Teradata 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 单击“保存”。

重要: Facebook 数据连接器停用通知   从 Excel 中的 Facebook 导入和刷新数据将在 2020 年 4 月停止工作。 在此之前,仍可以使用 Facebook Get & Transform (Power Query) 连接器,但从 2020 年 4 月开始,将无法连接到 Facebook 并收到错误消息。 我们建议尽快修改或删除使用 Facebook 连接器&转换 (Power Query) 查询,以避免意外的结果。

注意: 如果这是第一次连接到 Facebook,将要求你提供凭据。 使用 Facebook 帐户登录,并允许访问 Power Query 应用程序。 可以通过单击"不要对此连接器选项再次发出警告"来 关闭将来的 提示。

  1. Power Query 功能区选项卡中,单击"来自其他源>来自 Facebook。

  2. Facebook 对话框中,使用 "Me"、" 用户名"或"对象 ID"连接到 Facebook。

    注意: Facebook 用户名不同于登录电子邮件。

  3. 从"连接"下拉列表 选择要连接到的 类别。 例如,选择 "好友 "可让你访问 Facebook 好友类别中 提供的所有 信息。

  4. 单击“确定”。

  5. 如有必要,请在Access Facebook对话框中单击"登录",然后输入 Facebook 电子邮件或电话号码以及密码。 可以检查保持登录状态的选项。 登录后,单击"连接"。

  6. 连接成功后,可以预览包含所选类别相关信息的表。 例如,如果选择"好友"类别,Power Query 将按名称呈现包含 Facebook 好友的表。

  7. 单击"加载"或"编辑"。

可以使用 查询编辑器为 Power Query 编写 公式

= Facebook.Graph("https://graph.facebook.com/v2.8/me/friends")

带有 Facebook 公式的 Power Query 编辑器

注意: 仅当您使用 Power Query 加载、编辑或创建新查询时,“查询编辑器”才会显示。 以下视频显示在 Excel 工作簿中编辑查询后显示的“查询编辑器”窗口。 若要查看查询编辑器而不加载或编辑现有工作簿查询,请从Power Query功能区选项卡的"获取外部数据"部分选择"从其他源">空白查询"。 以下视频介绍一种显示查询编辑器的方法。

如何在 Excel 中查看查询编辑器

注意: 

  • 在连接到 SAP SQL Anywhere 数据库之前,需要在计算机上 SQL SAP SQL Anywhere 驱动程序。 选择与 Power Query 安装匹配的驱动程序 (32 位或 64 位) 。

  1. POWER QUERY 功能区选项卡中,选择"从数据库>从SAP Sybase SQL Anywhere。

    从数据库中获取外部数据
  2. "Sybase 数据库 "对话框中,在"服务器名称"框中指定要连接到 Sybase 服务器。 (可选)也可以指定 数据库名称

  3. 如果要使用本机数据库查询导入数据,在"语句"框中指定SQL查询。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 单击“确定”。

  5. 默认情况下,将 选中 "加密连接"复选框,以便 Power Query 使用简单的加密连接连接到数据库。

  6. 单击“连接”。

Microsoft Azure Blob 存储是一种用于存储大量非结构化数据(如图像、视频、音频和文档)的服务,这些数据可以通过 HTTP 或 HTTPS 从世界上的任何位置访问。 有关 Azure Blob 存储服务的信息,请参阅"如何使用 Blob 存储"。

  1. Power Query 功能区 选项卡中,选择"从 Azure > 从 Microsoft Azure Blob 存储

    Power Query 从 Azure 对话框导入
  2. "Microsoft Azure Blob 存储"对话框中,输入 Microsoft Azure 存储帐户名称或 URL,并单击"确定"。

  3. 如果是首次连接到 Blob 存储服务,系统会提示输入并保存存储访问密钥。 在"访问 Microsoft Azure Blob 存储"对话框中,在"帐户密钥"框中输入存储访问密钥,并单击"保存"。

    注意: 如果需要检索存储访问密钥,请浏览到Microsoft Azure 门户,选择存储帐户,然后单击页面底部的"管理访问密钥"图标。 单击主键右边的复制图标,然后将值粘贴到"帐户密钥 " 框中。

  4. 查询 编辑器 列出 Blob 存储中 Microsoft Azure 容器。 在导航器中,选择要从中导入数据的容器,然后单击"应用&关闭"。

  1. POWER QUERY 功能区选项卡中,选择"从Azure >从Microsoft Azure HDInsight。

    从 Azure 获取外部数据
  2. "Microsoft Azure HDInsight"对话框中,输入帐户名称并单击"确定"。

  3. 接下来,输入帐户密钥,并单击"连接"。

    注意: 如果需要检索密钥,请返回到Microsoft Azure门户,选择存储帐户,然后单击页面底部的"管理访问密钥"图标。 单击主键右边的复制图标,将值粘贴到向导中。

  4. 在"导航器"对话框中 选择群集 ,然后查找并选择内容文件。

  5. 单击 " 加载"以加载所选表,或 单击" 编辑"以在加载之前执行其他数据筛选器和转换。

可以使用 Power Query 加载项连接到外部数据源并执行高级数据分析。 以下部分提供使用 Power Query 连接到数据源的步骤 - 网页、文本文件、数据库、联机服务和 Excel 文件、表和范围。

重要: 

  • 确保已下载、安装并激活 Power Query 加载项。

  • 若要激活Power Query加载项,请单击"文件>选项>加载项。 在底部的“管理”部分中,从下拉列表中选择“COM 加载项”选项,然后单击“Go”。 单击“Power Query”复选框,再单击“确定”。 Power Query 功能区应会自动显示,但如果未显示,请关闭并重新启动 Excel。

使用查询编辑器

注意: 使用 Power Query 加载、编辑或创建新查询时,将显示 查询编辑器。 以下视频显示在 Excel 工作簿中编辑查询后显示的“查询编辑器”窗口。 若要查看查询编辑器而不加载或编辑现有工作簿查询,请从Power Query功能区选项卡的"获取外部数据"部分选择"从其他源">空白查询"。 以下视频介绍一种显示查询编辑器的方法。

如何在 Excel 中查看查询编辑器

  1. Power Query 功能区选项卡中,单击"从文件">"从 CSV"或"从文本"。

  2. "逗号分隔值浏览 "对话框中,浏览或键入要查询的文件的路径。

  3. 单击“打开”。

注意: 如果要从 CSV 文件导入数据,Power Query 将自动检测列分隔符,包括列名称和类型。 例如,如果导入以下示例 CSV 文件,Power Query 会自动使用第一行作为列名,并更改每个列数据类型。

示例 CSV 文件

CSV 文件的图像

Power Query 会自动更改每个列数据类型:

  • 订单 ID 对数字的更改

  • 订单日期更改日期

  • 类别将保留 (默认列类型的)

  • 产品名称将保留 (默认列类型的文本)

  • 对数字的销售更改

在查询编辑器中,Power Query 会自动应用 FirstRowAsHeader 步骤和 ChangeType 步骤。 这些自动操作等效于手动 提升行 和手动更改每个列类型。

Power Query 自动检测列后,还可使用查询 编辑器Power Query编写公式。 例如:

= Csv.Document(File.Contents("C:\Examples\Products Categories and Orders.csv"),null,",",null,1252)

= Table.PromoteHeaders(Source)

= Table.TransformColumnTypes(FirstRowAsHeader,{{"OrderID", type number}, {"CustomerID", type text}, {"EmployeeID", type number}, {"OrderDate", type date}, {"RequiredDate", type date}, {"ShipName", type text}})

  1. 选择数据区域内的任意单元格。

  2. Power Query 功能区选项卡中,单击"从表"。

    连接到 Excel 模拟运算表

  3. 出现提示时,可在"从表"对话框中单击"范围选择"按钮,选择要用作数据源的特定区域。

    “从表”对话框

  4. 如果数据区域包含列标题,可以检查"表包含标题"。 区域标题单元格用于设置查询的列名称。

  5. 在查询编辑器中,单击" 关闭并&加载"。

注意: 如果数据区域已定义为命名区域,或位于 Excel 表中,则 Power Query 将自动感知整个区域,并加载到查询编辑器中。 将纯数据加载到查询编辑器中时,会自动将其转换为表。

注意: 仅当您使用 Power Query 加载、编辑或创建新查询时,“查询编辑器”才会显示。 以下视频显示在 Excel 工作簿中编辑查询后显示的“查询编辑器”窗口。 若要查看查询编辑器而不加载或编辑现有工作簿查询,请从Power Query功能区选项卡的"获取外部数据"部分选择"从其他源">空白查询"。 以下视频介绍一种显示查询编辑器的方法。

如何在 Excel 中查看查询编辑器

可以使用 查询编辑器为 Power Query 编写 公式

= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

查询编辑器公式示例

  1. Power Query 功能区 选项卡中,单击 "从文件 > Excel 中

  2. "Excel 浏览" 对话框中,浏览或键入要查询的文件的路径。

  3. 单击“打开”。

    如果源工作簿具有命名区域,则区域的名称将用作数据集。

您也可以使用查询 编辑器Power Query编写公式。 例如:

= Excel.Workbook
 (File.Contents("C:\Example\Products and Orders.xlsx"))

注意: 仅当您使用 Power Query 加载、编辑或创建新查询时,“查询编辑器”才会显示。 以下视频显示从Excel 工作簿编辑查询后出现的 Excel 2013 中的"查询编辑器"窗口。 若要查看查询编辑器而不加载或编辑现有工作簿查询,请从Power Query功能区选项卡的"获取外部数据"部分选择"从其他源">空白查询"。 以下视频介绍一种显示查询编辑器的方法。

如何在 Excel 中查看查询编辑器

  1. 单击“Power Query”功能区,然后单击“自网站”。

  2. 在“自网站”对话框中,输入网页 URL,然后按“确定”。

    Power Query > 自网站 >“输入 URL”对话框

    在本例中,我们将使用:http://en.wikipedia.org/wiki/UEFA_European_Football_Championship.

    如果网页需要用户凭据:

    • 在“访问网页”对话框中,单击凭据选项,然后提供身份验证值。

    • 单击“保存”。

  3. 单击“确定”。

  4. Power Query 将分析网页,并在“表格视图”中加载“导航器”窗格。

    如果知道要连接到哪个表格,则从列表中单击它。 在本例中,我们选择了“结果”表格。

    Power Query > 自网站 > 导航器表格视图

    否则,可切换到“Web 视图”并手动选择相应的表格。 在本例中,我们选择了“结果”表格。

    Power Query > 自网站 > 导航器 > Web 视图
  5. 单击“加载”,随后 Power Query 会将所选的 Web 数据加载到 Excel 中。

  1. Power Query 功能区 选项卡中,单击 "从 数据库">" 从SQL Server数据库"。

    数据库选项中的 Power Query
  2. "Microsoft SQL 数据库 "对话框中,SQL Server"服务器名称"框中 指定要连接到的服务器 。 (可选)也可以指定 数据库名称

  3. 如果要使用本机数据库查询导入数据,在"语句"框中指定SQL查询。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

    Power Query SQL Server 数据库连接对话框
  4. 选择“确定”。

  5. 选择身份验证模式以连接到 SQL Server 数据库。

    Power Query SQL Server 连接登录凭据
    1. Windows:这是默认选项。 如果要使用 Windows 身份验证进行连接,请选择此选项。

    2. 数据库:如果要使用身份验证进行连接,SQL Server此选项。 选择此选项后,请指定用户名和密码以连接到 SQL Server 实例。

  6. 默认情况下, 选中 "加密连接"复选框以表示 Power Query 使用加密连接连接到数据库。 如果不希望使用加密连接进行连接,请清除此复选框,然后单击"连接"。

    如果未使用加密SQL Server建立到客户端的连接,Power Query 会提示使用未加密的连接进行连接。 在 消息 中单击"确定"以使用未加密的连接进行连接。

公式示例

您也可以使用查询 编辑器Power Query编写公式。

= Sql.Databases(".")
= Sql.Database(".","Contoso")

  1. 单击功能区上的"Power Query"选项卡,然后选择"从 ODBC >>外部数据"。

  2. 在"从 ODBC"对话框中,选择数据源名称 (DSN) 。

  3. 输入连接字符串,然后按"确定"。

  4. 在"下一个"对话框中,从"默认"或"自定义"、Windows或"数据库连接"选项中选择,输入凭据,然后按Connect。

  5. "导航器"窗格中,选择要连接到的表或查询,然后按"加载"或"编辑"。

  1. Power Query 功能区选项卡中,选择"从数据库> Access 数据库"

    从 "数据库" 对话框获取数据

  2. "浏览 "对话框中,浏览或键入文件 URL 以导入或链接到文件。

  3. 按照导航器 对话框中 的步骤连接到选择的表或查询。

  4. 单击"加载"或"编辑"。

  1. Power Query 功能区选项卡中,单击"从文件"> XML"。

    "从文件中查询 Power Query" 对话框
  2. 在" 从 XML 浏览 "对话框中,浏览或键入要导入或链接到文件的文件 URL。

  3. 单击“打开”。

    连接成功后,可以使用"导航器"窗格以表格形式浏览和预览 XML 文件中项目的集合。

您也可以使用查询 编辑器Power Query编写公式。 例如:

= Xml.Tables(File.Contents("C:\Downloads\XML Example.xml"))

注意: 仅当您使用 Power Query 加载、编辑或创建新查询时,“查询编辑器”才会显示。 以下视频显示在 Excel 工作簿中编辑查询后显示的“查询编辑器”窗口。 若要查看查询编辑器而不加载或编辑现有工作簿查询,请从Power Query功能区选项卡的"获取外部数据"部分选择"从其他源">空白查询"。 以下视频介绍一种显示查询编辑器的方法。

  1. 在"数据"选项卡上的"获取外部数据"组中,单击"从其他源",然后单击"从Analysis Services。

    Excel 功能区图像

    将显示数据连接向导。 此向导有三个窗格。

    • 连接到数据库服务器

    • 选择数据库和表

    • 保存数据连接文件并完成

  2. 在" 连接到数据库服务器 "窗格的"服务器 名称 "框中,键入 OLAP 服务器数据库服务器。

    提示: 如果你知道要连接到的脱机多维数据集文件的名称,可以键入完整的文件路径、文件名和扩展名。

  3. "登录凭据"下,执行下列操作之一,然后单击"下一步":

    • 若要使用当前的 Windows 用户名和密码,请单击"使用Windows 身份验证"。

    • 若要输入数据库用户名和密码,请单击"使用以下用户名和密码",然后在相应的"用户名和密码"框中键入用户名和密码

  4. "选择包含需要的数据的数据库"窗格中,选择一个数据库,然后单击"下一步"。

    若要连接到数据库中的 多维数据集 ,请确保已选择"连接到特定多维数据集或表",然后从列表中选择一个多维数据集。

  5. 在"保存数据连接文件"和"完成"窗格中的"文件名"框中,根据需要修改默认文件名 (可选) 。

    单击 " 浏览"以更改"我的数据源" 的默认文件位置,或检查现有文件名。

  6. 在"说明"、"友好名称"和"搜索关键字"框中,键入文件说明、友好名称和常用搜索 (都是可选的) 。

  7. 若要确保在刷新数据透视表时使用该连接文件,请单击"始终"尝试使用此文件 刷新此数据

    选中此复选框可确保使用该连接文件的所有工作簿始终使用对连接文件的更新。

  8. 单击 " 完成"关闭数据连接向导。

  9. "导入数据"对话框中,在"选择要如何查看工作簿中的此数据"下,执行下列操作之一:

    • 若要仅创建数据透视表,请单击"数据透视表"。

    • 若要创建数据透视表和数据透视图,请单击"数据透视图"和"数据透视表"。

    • 若要将所选连接存储在工作簿中供以后使用,请单击"仅创建连接"。 此复选框可确保连接由包含您创建的多维数据集函数的公式使用,并且您不希望创建数据透视表。

  10. "要将数据放在何处"下,执行下列操作之一:

    • 若要将数据透视表放在现有工作表中,请选择"现有工作表",然后在要查找数据透视表的单元格区域键入第一个单元格的单元格引用。

      也可以单击"折叠对话框 按钮图像 以临时隐藏对话框,选择工作表上想要使用的开始单元格,然后按"展开对话框" 按钮图像

  11. 若要将数据透视表放在从单元格 A1 开始的新工作表中,请单击"新建工作表"。

  12. 若要验证或更改连接属性,请单击"属性",在"连接属性"对话框中进行必要的更改,然后单击"确定"。

  1. "Power Query" 选项卡上,单击 "从其他源 "> 空白查询"。

  2. 在查询编辑器中,单击"高级编辑器"。

  3. 按如下所示输入查询字符串,将"C:\Users\Name\Desktop\JSONTest.json"替换为 JSON 文件的路径。

    let
    
        Source = Json.Document(File.Contents("C:\Users\Name\Desktop\JSONTest.json")),
        #"Converted to Table" = Record.ToTable(Source)
    
    in
    
        #"Converted to Table"
    

注意: 在使用 Power Query连接到 Oracle 数据库之前,计算机上需要 Oracle 客户端软件 v8.1.7 或更高。 若要安装 Oracle 客户端软件,请通过适用于 Visual Studio (12.1.0.2.4) 的 Oracle 开发人员工具转到 32 位 Oracle 数据访问组件 (ODAC) 以安装 32 位 Oracle 客户端, 或到 64 位 ODAC 12c 版本 4 (12.1.0.2.4) Xcopy for Windows x64 以安装 64 位 Oracle 客户端。

  1. Power Query 功能区选项卡中,单击"从数据库>从 Oracle 数据库。

    数据库选项中的 Power Query
  2. "Oracle 数据库 "对话框中的服务器 名称中 ,指定要连接到的 Oracle 服务器。 如果需要 SID,可以"ServerName/SID"的形式指定此 SID。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 单击“确定”。

  5. 如果 Oracle 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 单击“连接”。

  1. Power Query 功能区选项卡中,选择"从SharePoint >源"。

    Power Query 从其他源获取数据对话框
  2. 在出现的"Microsoft SharePoint 列表"对话框中,输入 SharePoint 网站的 URL。

    注意: 连接到 SharePoint 列表时,请输入网站 URL 而不是列表 URL。 在 Access SharePoint 对话框中,选择要正确针对网站进行身份验证的最常规 URL。 默认情况下,将选择最常规的 URL。

  3. 选择“”以继续操作。

  4. 在接下来 出现的 Access SharePoint 对话框中,选择凭据选项:

    1. 如果 SharePoint Server 不需要任何凭据,请选择"匿名"。

    2. 如果 SharePoint Server 需要 Windows 凭据,请选择 Windows。

    3. 如果 SharePoint Server 需要组织帐户凭据,请选择组织帐户。

  5. 选择"连接"。

    Excel Power Query 连接到 Sharepoint 列表连接对话框

  1. Power Query 功能 区选项卡中 ,选择" 从其他源> 从 OData 源

    Power Query 从其他源获取数据对话框
  2. "OData 源 "对话框中, 输入 OData 源的 URL。

  3. 选择“确定”。

  4. 如果 OData 源需要用户凭据,在 "访问 OData 源" 对话框中:

    1. 如果OData源需要 Windows 身份验证,请选择Windows。

    2. 如果 OData 源需要用户名和密码,请选择"基本"。

    3. 如果 OData 源需要市场帐户密钥,请选择"市场密钥"。 可以选择"获取 市场帐户密钥 "以订阅 Microsoft Azure OData 源。 还可以从"访问 OData Microsoft Azure "对话框注册市场。

    4. 如果 OData 源需要联合访问凭据,请单击"组织帐户"。 对于Windows Live™ ID,请登录到帐户。

    5. 选择“保存”。

注意: 连接到 OData 源支持 JSON 光数据服务格式。

  1. 单击"数据"选项卡,然后从 OLEDB >其他源>数据。 如果看不到"获取数据 " 按钮,请按照下面的 数据连接向导 说明进行操作。

  2. 在"从 OLE DB"对话框中,输入连接字符串,然后按"确定"。

  3. OLEDB提供程序对话框中,从"默认"或"自定义"、Windows或数据库连接选项中选择,输入相应的凭据,然后单击"连接"。

  4. "导航器"对话框中,选择要连接到的数据库和表或查询,然后按"加载"或"编辑"。

  5. Power Query 编辑器中,按 Close &加载

  1. Power Query 功能 区选项卡中,单击 " 从数据库> MySQL 数据库

    数据库选项中的 Power Query
  2. "MySQL 数据库 "对话框中的服务器 名称中 ,指定要连接到的 MySQL 数据库服务器。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 单击“确定”。

  5. 如果 MySQL 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 单击“连接”。

  1. Power Query 功能区选项卡中,选择"来自其他源>来自Microsoft Exchange。

    Power Query 数据源
  2. "访问Exchange Server对话框中,指定电子邮件地址和密码

  3. 单击“保存”。

  4. "Microsoft Exchange 自动发现服务"对话框中,选择"允许"以允许 Exchange 服务信任你的凭据。

重要: 请确保已下载并安装 Power Query 加载项。

  1. Power Query 功能区选项卡中,单击"从 Active Directory >源"。

    Power Query 从其他源获取数据对话框

  2. 在"Active Directory"对话框中输入域。

  3. 域的"Active Directory域"对话框中,单击"使用我的当前凭据"或"使用备用凭据"。 对于使用备用证书身份验证,请输入用户名和密码

  4. 单击“连接”。

  5. 连接成功后,可以使用"导航器"窗格浏览 Active Directory 中所有可用的域,并向下钻取 Active Directory 信息,包括用户、帐户和计算机。

警告: 

  • 在连接到 IBM DB2 数据库之前,需要在计算机上安装IBM DB2 数据服务器驱动程序 (最低要求是 IBM Data Server Driver Package (DS Driver) ) 。 选择与 Power Query 安装匹配的驱动程序 (32 位或 64 位) 。

  • IBM 在 Windows 8 上安装 IBM DB2 数据服务器驱动程序时报告了已知问题如果使用的是 Windows 8,并且想要使用 Power Query 连接到 IBM DB2,则需要执行其他安装步骤。 查找有关 Windows 8 上的 IBM DB2 数据服务器驱动程序的信息。.

  1. Power Query 功能区 选项卡中 ,单击" 从数据库> 从 IBM DB2 数据库

    数据库选项中的 Power Query
  2. "IBM DB2 数据库 "对话框中的服务器 名称中 ,指定要连接到的 IBM DB2 数据库服务器。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 单击“确定”。

  5. 如果 IBM DB2 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 单击“连接”。

注意: 在 Power Query 中连接到 PostgreSQL 数据库之前,需要在计算机上安装 用于 PostgreSQL 的 Ngpsql 数据提供程序。 选择与 Office 版本匹配的驱动程序 (32 位或 64 位) 。 有关详细信息,请参阅:我使用的是哪个版本的 Office? 另请确保已在计算机配置中注册提供程序,使其与设备上的最新 .NET 版本匹配。

  1. Power Query 功能 区选项卡中 ,选择" 从数据库> 从 PostgreSQL 数据库

    数据库选项中的 Power Query
  2. "PostgreSQL 数据库"对话框中,在"服务器名称"部分中指定要连接到的 PostgreSQL 数据库 服务器。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 选择“确定”。

  5. 如果 PostgreSQL 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 选择"连接"。

  1. 导航到https://web.powerapps.com/

  2. 选择要连接到 的环境

  3. 在菜单中,选择"高级自定义>开发人员资源>图标

  4. 复制 实例 Web API 值。 

    注意: 

    • URL 格式将类似https://<tenant>.crm.dynamics.com/api/data/v9.0.

    • 用于连接的 URL 的确切格式取决于你的区域以及你使用的应用的 CDS 版本。 有关详细信息,请参阅:Web API URL 和版本。

  5. 选择"数据"选项卡,然后从 Online Services >从联机>从Dynamics 365 (联机) 。

    • 如果未看到"获取数据"按钮,请单击"从其他源>查询">从Dynamics 365 (联机) 。

  6. 在对话框中,选中"基本"选项,输入 CDS for Apps 连接的Web API URL,并单击"确定"。

    • 如果选择" 高级" 选项,可以将某些附加参数追加到查询,以控制返回的数据。 有关详细信息,请参阅:使用 Web API 查询数据

  7. 选择"组织帐户"。

    • 如果未使用用于访问 CDS for Apps 的 Microsoft 工作或学校帐户登录,请单击"登录"并输入帐户用户名和密码。

  8. 单击“连接”。

  9. "导航器" 对话框中,选择要检索的数据。

  10. 如果数据可以按现有方式导入,请选择"加载"选项,否则请选择"编辑"选项以打开Power Query 编辑器

    注意: Power Query 编辑器提供多个选项来修改返回的数据。 例如,你可能希望导入的列数少于源数据包含的列数。 在这种情况下,请转到"开始"选项卡>管理列>选择列,选择要保留的列,然后单击"确定"。 准备就绪后,单击"关闭& 加载 ",将修改后的数据返回到 Excel。

注意: 在连接到 Teradata 数据库之前,需要计算机上用于 Teradata 的 .NET 数据提供程序。

  1. Power Query 功能区 选项卡中 ,选择" 从数据库> 从 Teradata 数据库

    数据库选项中的 Power Query
  2. "Teradata 数据库 "对话框中的服务器名称 ,指定要连接到的 Teradata 服务器。

  3. 如果要使用本机数据库查询导入数据,在"语句"框中 指定SQL查询 。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 选择“确定”。

  5. 如果 Teradata 服务器需要数据库用户凭据:

    1. "访问数据库" 对话框中,输入用户名和密码。

    2. 单击“保存”。

重要: Facebook 数据连接器停用通知   从 Excel 中的 Facebook 导入和刷新数据将在 2020 年 4 月停止工作。 在此之前,仍可以使用 Facebook Get & Transform (Power Query) 连接器,但从 2020 年 4 月开始,将无法连接到 Facebook 并收到错误消息。 我们建议尽快修改或删除使用 Facebook 连接器&转换 (Power Query) 查询,以避免意外的结果。

注意: 如果这是第一次连接到 Facebook,将要求你提供凭据。 使用 Facebook 帐户登录,并允许访问 Power Query 应用程序。 可以通过单击"不要对此连接器选项再次发出警告"来 关闭将来的 提示。

  1. Power Query 功能区选项卡中,单击"来自其他源>来自 Facebook。

  2. Facebook 对话框中,使用 "Me"、" 用户名"或"对象 ID"连接到 Facebook。

    注意: Facebook 用户名不同于登录电子邮件。

  3. 从"连接"下拉列表 选择要连接到的 类别。 例如,选择 "好友 "可让你访问 Facebook 好友类别中 提供的所有 信息。

  4. 单击“确定”。

  5. 如有必要,请在Access Facebook对话框中单击"登录",然后输入 Facebook 电子邮件或电话号码以及密码。 可以检查保持登录状态的选项。 登录后,单击"连接"。

  6. 连接成功后,可以预览包含所选类别相关信息的表。 例如,如果选择"好友"类别,Power Query 将按名称呈现包含 Facebook 好友的表。

  7. 单击"加载"或"编辑"。

可以使用 查询编辑器为 Power Query 编写 公式

= Facebook.Graph("https://graph.facebook.com/v2.8/me/friends")

带有 Facebook 公式的 Power Query 编辑器

注意: 仅当您使用 Power Query 加载、编辑或创建新查询时,“查询编辑器”才会显示。 以下视频显示在 Excel 工作簿中编辑查询后显示的“查询编辑器”窗口。 若要查看查询编辑器而不加载或编辑现有工作簿查询,请从Power Query功能区选项卡的"获取外部数据"部分选择"从其他源">空白查询"。 以下视频介绍一种显示查询编辑器的方法。

如何在 Excel 中查看查询编辑器

注意: 

  • 在连接到 SAP SQL Anywhere 数据库之前,需要在计算机上 SQL SAP SQL Anywhere 驱动程序。 选择与 Power Query 安装匹配的驱动程序 (32 位或 64 位) 。

  1. POWER QUERY 功能区选项卡中,选择"从数据库>从SAP Sybase SQL Anywhere。

    从数据库中获取外部数据
  2. "Sybase 数据库 "对话框中,在"服务器名称"框中指定要连接到 Sybase 服务器。 (可选)也可以指定 数据库名称

  3. 如果要使用本机数据库查询导入数据,在"语句"框中指定SQL查询。 有关更多信息,请参阅使用本地数据库查询从数据库导入数据

  4. 单击“确定”。

  5. 默认情况下,将 选中 "加密连接"复选框,以便 Power Query 使用简单的加密连接连接到数据库。

  6. 单击“连接”。

Microsoft Azure Blob 存储是一种用于存储大量非结构化数据(如图像、视频、音频和文档)的服务,这些数据可以通过 HTTP 或 HTTPS 从世界上的任何位置访问。 有关 Azure Blob 存储服务的信息,请参阅"如何使用 Blob 存储"。

  1. Power Query 功能区 选项卡中,选择"从 Azure > 从 Microsoft Azure Blob 存储

    Power Query 从 Azure 对话框导入
  2. "Microsoft Azure Blob 存储"对话框中,输入 Microsoft Azure 存储帐户名称或 URL,并单击"确定"。

  3. 如果是首次连接到 Blob 存储服务,系统会提示输入并保存存储访问密钥。 在"访问 Microsoft Azure Blob 存储"对话框中,在"帐户密钥"框中输入存储访问密钥,并单击"保存"。

    注意: 如果需要检索存储访问密钥,请浏览到Microsoft Azure 门户,选择存储帐户,然后单击页面底部的"管理访问密钥"图标。 单击主键右边的复制图标,然后将值粘贴到"帐户密钥 " 框中。

  4. 查询 编辑器 列出 Blob 存储中 Microsoft Azure 容器。 在导航器中,选择要从中导入数据的容器,然后单击"应用&关闭"。

  1. POWER QUERY 功能区选项卡中,选择"从Azure >从Microsoft Azure HDInsight。

    从 Azure 获取外部数据
  2. "Microsoft Azure HDInsight"对话框中,输入帐户名称并单击"确定"。

  3. 接下来,输入帐户密钥,并单击"连接"。

    注意: 如果需要检索密钥,请返回到Microsoft Azure门户,选择存储帐户,然后单击页面底部的"管理访问密钥"图标。 单击主键右边的复制图标,将值粘贴到向导中。

  4. 在"导航器"对话框中 选择群集 ,然后查找并选择内容文件。

  5. 单击 " 加载"以加载所选表,或 单击" 编辑"以在加载之前执行其他数据筛选器和转换。

注意: HDInsight 具有默认的 Hive 表HiveSampleData.txt,可用于了解如何使用 Power Query 将数据导入 Excel。 有关如何从 HDInsight 导入数据的分步指南,请参阅"如何使用 Power Query 将 Excel 连接到Microsoft Azure HDInsight"。

Power Query 在 Excel 2007 中不可用。 但是,仍可以连接到外部数据源。 请注意,体验没有 Power Query 的等效"获取和转换&可靠。 请参阅: 统一获取&转换

数据连接向导

步骤 1:创建与另一个工作簿的连接

  1. 在“数据”选项卡上,单击“连接”。

    连接

  2. 在"工作簿连接"对话框中,单击"添加"。

  3. 在“现有连接”对话框的底部附近,单击“浏览更多”。

  4. 查找您的工作簿,然后单击“打开”。

  5. 在“选择表”对话框中,选择表(工作表),然后单击“确定”。

    注意: 

    • 工作表在"选择表格"对话框中称为" "

    • 一次只能添加一个表。

    • 可以通过单击"属性" 按钮重命名表 。 还可以添加说明。

  6. 若要添加更多表,请重复步骤 2 到步骤 5。

  7. 单击“关闭”。

步骤 2:将标签添加到工作表

  1. 单击“现有连接”,选择表,然后单击“打开”。

  2. "导入数据"对话框中,选择将数据放入工作簿的位置以及是否以表、数据透视表或数据透视图查看数据

可以使用数据连接向导连接到 Access 数据库。

  1. 在“数据”选项卡上的“获取外部数据”组中,单击“自 Access”。

    “数据”选项卡上的“获取外部数据”组

  2. "选择数据源 "对话框中,浏览到 Access 数据库

  3. 在“选择表”对话框中,选择要使用的表或查询,然后单击“确定”。

  4. 您可以单击“完成”或单击“下一步”以更改连接的详细信息。

  5. 在“导入数据”对话框中,选择在何处放置您的工作簿中的数据以及作为表格、数据透视表还是数据透视图查看数据。

  6. 单击“属性”按钮以设置连接的高级属性,例如用于刷新连接的数据的选项。

  7. 或者,您可以将数据添加到数据模型,以便您可以将数据与来自其他来源的其他表格或数据进行合并,创建表之间的关系以及执行比可以对基本数据透视表执行的更多操作。

  8. 单击“确定”以完成。

转到"数据"选项卡>"从文本>外部数据"。 然后,在" 导入文本文件 "对话框中,双击要导入的文本文件,将打开"文本导入向导"对话框。

步骤 1/3

原始数据类型    如果文本文件中的项目由制表符、冒号、分号、空格或其他字符分隔,请选择"以分隔符"。 如果每列的所有项长度相同,请选择"固定宽度"。

在行中开始导入    键入或选择行号以指定要导入的数据的第一行。

文件来源    选择文本文件中使用的字符集。 在大多数情况下,可以将此设置保留为默认值。 如果你知道该文本文件是使用与计算机上使用的字符集不同的字符集创建的,应更改此设置以匹配该字符集。 例如,如果计算机设置为使用字符集 1251 (西里尔文、Windows) ,但你知道该文件是使用字符集 1252 (西欧、Windows) 生成的,则应该将"文件原点"设置为 1252。

文件预览    此框显示文本,就像在工作表中分隔为列时显示的文本一样。

第 2 步(第 3 步 (分隔的数据)

分隔符    选择分隔文本文件中的值的字符。 如果未列出字符,请选中"其他"复选框,然后在包含光标的框中键入字符。 如果宽度固定,则数据类型不可用。

将连续分隔符视为一个    如果数据在数据字段之间包含多个字符的分隔符,或者数据包含多个自定义分隔符,请选中此复选框。

文本限定符    选择将值括在文本文件中的字符。 当 Excel 遇到文本限定符字符时,该字符之后以及该字符下一次出现之前的所有文本将导入为一个值,即使文本包含分隔符字符。 例如,如果分隔符是逗号 (,则) 文本限定符是引号 (") ,"达拉斯,德克萨斯州"将导入到德克萨斯州达拉斯的一个单元格中。 如果没有字符或撇号 ( ) 指定为文本限定符,则"达拉斯,德克萨斯州"将导入到两个相邻单元格中,作为"达拉斯德克萨斯州"。

如果分隔符字符出现在文本限定符之间,Excel 将省略导入的值中的限定符。 如果文本限定符之间没有分隔符字符,Excel 在导入的值中包含限定符字符。 因此,"达拉斯德克萨斯州 (引号文本限定符) 导入到一个单元格中,作为"达拉斯德克萨斯州"。

数据预览    查看此框中的文本,验证文本是否将按需要分隔为工作表中的列。

步骤 2(3) (固定宽度)

数据预览    在此部分中设置字段宽度。 单击预览窗口以设置以垂直线表示的分栏符。 双击分栏符将其删除,或拖动分栏符移动。

步骤 3(3)

单击 "高级 "按钮执行下列一项或多项操作:

  • 指定文本文件中使用的小数分隔符和千位分隔符的类型。 将数据导入到 Excel 中时,分隔符将匹配在"Windows 控制面板" (中为位置指定的分隔符) 。

  • 指定一个或多个数值可能包含尾随减号。

列数据格式    单击"数据预览"部分所选列 的数据 格式。 如果不想导入所选列,请单击"不导入列" (跳过) 。

为所选列选择数据格式选项后,数据预览下的列 标题 将显示格式。 如果选择" 日期",请在"日期"框中选择 日期 格式。

选择与预览数据紧密匹配的数据格式,以便 Excel 可以正确转换导入的数据。 例如:

  • 若要将包含所有货币数字字符的列转换为 Excel 货币格式,请选择"常规"。

  • 若要将包含所有数字字符的列转换为 Excel 文本格式,请选择"文本"。

  • 若要将包含所有日期字符的列(按年、月、日的顺序)转换为 Excel 日期格式,请选择"日期",然后在"日期"框中选择YMD的日期类型。

如果转换可能导致意外 结果 ,Excel 将导入"常规"列。 例如:

  • 如果列包含各种格式(如字母和数字字符)的组合,Excel 会将列转换为"常规"。

  • 如果在日期列中,每个日期都按年、月、日期排序,并且选择 " 日期"以及日期类型 为 MDY,Excel会将列转换为"常规"格式。 包含日期字符的列必须与 Excel 内置日期或自定义日期格式紧密匹配。

如果 Excel 未将列转换为您需要的格式,您可以在导入后转换数据。

选择想要的选项后,单击"完成"打开"导入数据"对话框,然后选择数据的放置位置。

导入数据

设置这些选项以控制数据导入过程的运行方式,包括要使用哪些数据连接属性,以及要使用导入的数据填充的文件和范围。

  • 只有在准备好数据模型并选择将此导入添加到该模型的选项时,"选择要如何在工作簿中查看此数据"下的选项才可用 (查看此列表列表中的第三) 。

  • 指定目标工作簿:

    • 如果选择" 现有工作表",请单击工作表中的单元格以放置导入数据的第一个单元格,或单击并拖动以选择区域。

    • 选择要 导入到 新工作表的新工作表 (单元格 A1 开始)

  • 如果已设置数据模型,请单击 "将此数据 添加到数据模型"以将此导入包括在模型中。 有关详细信息,请参阅在 Excel 中创建数据模型

    请注意,选择此选项将解锁"选择要如何在工作簿中查看 此数据"下的选项

  • 单击 " 属性"以设置任何需要的外部数据范围属性。 有关详细信息,请参阅"管理外部数据范围及其属性"。

  • 准备好 导入数据后,单击"确定"。

  1. 在"数据"选项卡上的"获取外部数据"组中,单击"从 Web"。

  2. 在"新建 Web 查询"对话框中,在"地址"框中输入要查询的网页的地址,然后单击"转到"。

    网页将在"新建 Web 查询"对话框中打开。

  3. 在网页中,单击要查询的每个表旁有一个红色箭头的小黄色框。

  4. 设置任何需要的选项,然后单击"导入"。

设置 Web 查询选项

  1. 在"新建 Web 查询"对话框的右上角,单击"选项"。

  2. 在"Web 查询选项"对话框中,设置以下任何选项:

    选项

    “效果”

    格式设置

    •    Web 数据将导入为纯文本。 不会导入任何格式,并且仅从任何超链接导入链接文本。

    • 仅格式文本格式   Web 数据将导入为格式文本,但仅从任何超链接导入链接文本。

    • 完整 HTML 格式   将导入所有格式,并且导入的超链接将正常工作。

    将<预>块导入到列中

    如果选中此选项,则<预>块都将作为列导入。

    将连续分隔符视为一个

    此选项仅在选中上述选项时适用。 如果选择此选项,在导入过程中,在两者之间没有任何文本的分隔符将被视为一个分隔符。

    对整个部分使用相同的导入设置

    此选项仅在选中上述选项时适用。 如果选中此选项,则导入过程中一<指定页面上>预标记中来自 HTML 和 PRE 的数据将一并处理。 如果未选中,数据将导入连续行块中,以便按此类识别标头行。

    禁用日期识别

    如果选中,则日期将导入为文本。 如果未选中,日期将导入为日期/时间值。

    禁用 Web 查询重定向

    如果选中,重定向将被忽略。 如果未选择,将处理重定向。

"获取&转换"体验在 Excel 2007 中不可用,因此您可以使用 Office 数据连接 (.odc) 文件从 Excel 2007 工作簿连接到 Microsoft SQL Server 数据库。 SQL Server是一个全功能的关系数据库计划,专为需要最佳性能、可用性、可伸缩性和安全性的企业范围数据解决方案设计。

  1. 在" 数据" 选项卡上的" 获取外部数据 "组中,单击"从 其他源",然后单击" SQL Server。

    Excel 功能区图像

    数据连接向导启动。 此向导有三个页面。

    第 1 页:连接到数据库服务器    

  2. 在步骤 1 中,在"服务器SQL Server框中 键入计算机的名称

  3. 在步骤 2 中, 在"登录凭据"下,执行下列操作之一:

    • 若要使用当前的 Microsoft Windows 用户名和密码,请单击"使用Windows 身份验证"。

    • 若要输入数据库用户名和密码,请单击"使用以下用户名和密码",然后在相应的"用户名和密码"框中键入用户名和密码

      安全说明: 

      • 使用由大写字母、小写字母、数字和符号组合的强密码。 弱密码不混合使用这些元素。 强密码:Y6dh!et5。 弱密码:house1。 密码应至少包含 8 个字符。 最好使用包含 14 个或更多字符的密码。

      • 记住密码是非常重要的。 如果您忘记了密码,Microsoft 无法为您找回。 请将记好的密码保存在安全位置,远离密码所要保护的信息。

      第 2 页:选择数据库和表    

  4. "选择包含需要的数据的数据库"下,选择一个数据库。 在 "连接到特定表"下,选择特定的表或视图。

    或者,可以清除"连接到特定表"复选框,以便系统会提示使用此连接文件的其他用户输入表和视图列表。

    第 3 页:保存数据连接文件并完成    

  5. (可选)在" 文件名" 框中,修改建议的文件名。 单击 " 浏览"以更改"我的 (的默认文件) 。

  6. (可选)在"说明、友好名称"和"搜索关键字"框中键入文件说明、友好名称和常用搜索词。

  7. 若要确保在更新数据时始终使用连接文件,请单击"始终尝试使用此文件 刷新此数据 "复选框。 此复选框可确保使用该连接文件的所有工作簿始终使用对连接文件的更新。

  8. 若要指定在工作簿保存到 Excel Services 且使用 Excel Services 打开时如何访问数据透视表的外部数据源,请单击"身份验证设置",然后选择以下选项之一登录到数据源:

    • Windows 身份验证     选择此选项可以使用当前用户的 Windows 用户名和密码。 这是最安全的方法,但在许多用户连接到服务器时可能会影响性能。

    • SSO     选择此选项以使用 SSO (单一) ,然后在 SSO ID 框中输入相应的标识字符串。 网站管理员可以将 Windows SharePoint Services 配置为使用单一登录数据库,可在其中存储用户名和密码。 当许多用户连接到服务器时,此方法可能最高效。

    •     选择此选项以在连接文件中保存用户名和密码。

      安全说明: 在连接到数据源时避免保存登录信息。 此信息可能以纯文本格式存储,恶意用户可以访问该信息,以损害数据源的安全性。

      注意: 身份验证设置仅由 Excel Services 使用,而 Excel 则不由 Excel 使用。

  9. 单击“确定”。

  10. 单击 " 完成"关闭数据连接向导。

    将显示 "导入 数据"对话框。

  11. "选择要如何在工作簿中查看此数据"下,执行下列操作之一:

    • 若要创建 Excel 表格, 请单击" 表 (这是默认) 。

    • 若要创建数据透视表,请单击"数据透视表"。

    • 若要创建数据透视图和数据透视表,请单击"数据透视图"和"数据透视表"。

      注意: " 仅创建连接 "选项仅适用于 OLAP 数据库。

  12. "要将数据放在何处?"下,执行下列操作之一:

    • 若要将数据放在现有工作表中,请选择"现有工作表",然后在要查找数据的单元格区域键入第一个单元格的名称。

      或者,单击"折叠对话框 按钮图像 以暂时折叠对话框,选择工作表上的开始单元格,然后单击"展开对话框" 按钮图像

    • 若要将数据放在从单元格 A1 开始的新工作表中,请单击"新建工作表"。

  13. (可选)可以更改连接属性 (,也可通过单击"属性") 在"连接属性"对话框中进行更改,然后单击"确定"来更改连接文件

    有关详细信息,请参阅"连接属性"。

对于 Excel 2007,可以使用 Microsoft Query 连接到 ODBC 数据源。

数据连接向导

  1. 在"数据"选项卡上的"获取外部数据"组中,单击"从其他源",然后单击"从Analysis Services。

    Excel 功能区图像

    将显示数据连接向导。 此向导有三个窗格。

    • 连接到数据库服务器

    • 选择数据库和表

    • 保存数据连接文件并完成

  2. 在" 连接到数据库服务器 "窗格的"服务器 名称 "框中,键入 OLAP 服务器数据库服务器。

    提示: 如果你知道要连接到的脱机多维数据集文件的名称,可以键入完整的文件路径、文件名和扩展名。

  3. "登录凭据"下,执行下列操作之一,然后单击"下一步":

    • 若要使用当前的 Windows 用户名和密码,请单击"使用Windows 身份验证"。

    • 若要输入数据库用户名和密码,请单击"使用以下用户名和密码",然后在相应的"用户名和密码"框中键入用户名和密码

  4. "选择包含需要的数据的数据库"窗格中,选择一个数据库,然后单击"下一步"。

    若要连接到数据库中的 多维数据集 ,请确保已选择"连接到特定多维数据集或表",然后从列表中选择一个多维数据集。

  5. 在"保存数据连接文件"和"完成"窗格中的"文件名"框中,根据需要修改默认文件名 (可选) 。

    单击 " 浏览"以更改"我的数据源" 的默认文件位置,或检查现有文件名。

  6. 在"说明"、"友好名称"和"搜索关键字"框中,键入文件说明、友好名称和常用搜索 (都是可选的) 。

  7. 若要确保在刷新数据透视表时使用该连接文件,请单击"始终"尝试使用此文件 刷新此数据

    选中此复选框可确保使用该连接文件的所有工作簿始终使用对连接文件的更新。

  8. 单击 " 完成"关闭数据连接向导。

  9. "导入数据"对话框中,在"选择要如何查看工作簿中的此数据"下,执行下列操作之一:

    • 若要仅创建数据透视表,请单击"数据透视表"。

    • 若要创建数据透视表和数据透视图,请单击"数据透视图"和"数据透视表"。

    • 若要将所选连接存储在工作簿中供以后使用,请单击"仅创建连接"。 此复选框可确保连接由包含您创建的多维数据集函数的公式使用,并且您不希望创建数据透视表。

  10. "要将数据放在何处"下,执行下列操作之一:

    • 若要将数据透视表放在现有工作表中,请选择"现有工作表",然后在要查找数据透视表的单元格区域键入第一个单元格的单元格引用。

      也可以单击"折叠对话框 按钮图像 以临时隐藏对话框,选择工作表上想要使用的开始单元格,然后按"展开对话框" 按钮图像

  11. 若要将数据透视表放在从单元格 A1 开始的新工作表中,请单击"新建工作表"。

  12. 若要验证或更改连接属性,请单击"属性",在"连接属性"对话框中进行必要的更改,然后单击"确定"。

使用外部数据连接

以下部分讨论外部数据连接如何工作,以及如何查找编辑、管理和与其他程序和用户共享连接信息。

了解数据连接的基础知识

Excel 工作簿中的数据可能来自两个不同的位置。 数据可以直接存储在工作簿中,也可以存储在外部数据源中,例如文本文件、数据库或 OLAP (联机分析) 多维数据集。 此外部数据源通过数据连接连接到工作簿,数据连接是一组描述如何查找、登录和访问外部数据源的信息。

连接到外部数据的主要好处是,您可以定期分析此数据,而无需重复将数据复制到工作簿,这是一项可能耗时且容易出错的操作。 连接到外部数据后,每当数据源更新 (时,) 原始数据源自动刷新或更新 Excel 工作簿。

连接信息存储在工作簿中,也可以存储在连接文件中,例如 Office 数据连接 (ODC) 文件 (.odc) 或数据源名称文件 (.dsn) 。

若要 外部数据 Excel 中,需要访问数据。 如果 数据源 访问的外部帐户不在本地计算机上,可能需要联系数据库管理员获取密码、用户权限或其他连接信息。 如果数据源是数据库,请确保数据库未在独占模式下打开。 如果数据源是文本文件或电子表格,请确保其他用户未打开该数据源进行独占访问。

许多数据源还需要 ODBC 驱动程序或 OLE DB 提供程序来协调 Excel、连接文件和数据源之间的数据流。

下图总结了有关数据连接的要点。

连接到外部数据源

1. 可以连接到多种数据源:Analysis Services、SQL Server、Microsoft Access、其他 OLAP 和关系数据库、电子表格和文本文件。

2. 许多数据源都有关联的 ODBC 驱动程序或 OLE DB 提供程序。

3. 连接文件定义从数据源访问和检索数据所需的全部信息。

4. 连接信息从连接文件复制到工作簿中,可以轻松编辑连接信息。

5. 数据将复制到工作簿中,以便您可以像使用直接存储在工作簿中的数据一样使用它。

查找连接

若要查找连接文件,请使用" 现有连接" 对话框。 ("数据"选项卡上的"获取外部数据"组中,单击"现有连接"。) 使用此对话框,可以看到以下类型的连接:

  • 工作簿中的连接数    

    此列表显示工作簿中所有当前连接。 该列表基于已定义的连接、使用数据连接向导的"选择数据源"对话框创建的连接或以前从此对话框中选作连接的连接创建。

  • 您的计算机上的连接文件    

    此列表从"我的数据源"文件夹创建,该文件夹通常存储在"我的文档" (Windows XP) 或"文档" ( Windows Vista) 文件夹中。

  • 网络连接文件    

    可以从以下项创建此列表:

    • 本地网络上有一组文件夹,可在部署组策略时在整个网络中部署Microsoft Office位置。

    • 在 Excel Services 站点上 (DCL) 数据 SharePoint Foundation 库。 

编辑连接属性

您也可以将 Excel 用作连接文件编辑器,以创建和编辑与存储在工作簿或连接文件中的外部数据源的连接。 如果找不到想要的连接,可以通过单击"浏览更多内容"以显示"选择数据源"对话框,然后单击"新建源"以启动数据连接向导来创建连接。

创建连接后,可以使用"连接属性"对话框 ("数据"选项卡上的"获取外部数据"组中,单击"属性".) 控制与外部数据源的连接的各种设置,以及使用、重复使用或切换连接文件。

如果使用连接文件连接到数据源,Excel 会从连接文件将连接信息复制到 Excel 工作簿中。 当您使用"连接属性"对话框进行更改时,您将编辑当前 Excel 工作簿中存储的数据连接信息,而不是用于创建连接 (的原始数据连接文件,该文件由"定义"选项卡) 上的"连接文件"属性中显示的文件名指示。 编辑连接信息后 (连接名称和连接说明属性除外) ,将删除指向连接文件的链接,并清除"连接文件"属性。

若要确保在刷新数据源时始终使用连接文件,请单击"始终尝试使用此文件刷新定义"选项卡上的此数据。 选中此复选框可确保使用该连接文件的所有工作簿始终使用对连接文件的更新,这些工作簿还必须设置此属性。

管理连接

通过使用" 工作簿连接" 对话框,您可以轻松管理这些连接,包括创建、编辑和删除它们。 ("数据"选项卡上的"获取外部数据"组中,单击"连接".) 可以使用此对话框执行以下操作:

  • 创建、编辑、刷新和删除工作簿中正使用的连接。

  • 验证外部数据源。 如果连接是由其他用户定义的,可能需要这样做。

  • 显示每个连接在当前工作簿中的使用位置。

  • 诊断有关与外部数据的连接的错误消息。

  • 将连接重定向到其他服务器或数据源,或替换现有连接的连接文件。

  • 轻松创建连接文件并与用户共享。

共享连接

连接文件对于在一致的基础上共享连接特别有用,使连接更加可发现,帮助提高连接的安全性,以及促进数据源管理。 共享连接文件的最佳方法就是将它们放在安全且受信任的位置,例如网络文件夹或 SharePoint 库,用户可以在这里读取文件,但只有指定的用户可以修改该文件。

使用 ODC 文件

可以通过"选择数据源"对话框或"数据连接向导"连接到外部数据, (.odc) 创建 Office 数据连接 ( ODC) 文件。 ODC 文件使用自定义 HTML 和 XML 标记来存储连接信息。 您可以在 Excel 中轻松查看或编辑文件的内容。

您可以与其他人共享连接文件,以授予他们与外部数据源相同的访问权限。 其他用户不需要设置数据源来打开连接文件,但可能需要安装访问其计算机上外部数据所需的 ODBC 驱动程序或 OLE DB 提供程序。

ODC 文件是连接到数据和共享数据的建议方法。 打开连接文件,然后单击"连接属性"对话框的"定义"选项卡上的"导出连接文件"按钮,即可轻松地将其他传统连接文件 (DSN、UDL和查询文件) 转换为 ODC 文件。

使用查询文件

查询文件是包含数据源信息的文本文件,包括数据所在的服务器的名称,以及创建数据源时提供的连接信息。 查询文件是与其他 Excel 用户共享查询的传统方法。

使用 .dqy 查询文件    可以使用 Microsoft Query 保存包含关系数据库或文本文件中数据的查询的 .dqy 文件。 在 Microsoft Query 中打开这些文件时,可以查看查询返回的数据,并修改查询以检索不同的结果。 可以使用查询向导或直接在 Microsoft Query 中为创建的任何查询保存 .dqy 文件。

使用 .oqy 查询文件    可以保存 .oqy 文件,以连接到 OLAP 数据库中的数据,不管是在服务器上还是 脱机多维数据集文件 (.cub) 。 使用 Microsoft Query 中的多维连接向导为 OLAP 数据库或多维数据集创建数据源时,会自动创建 .oqy 文件。 由于 OLAP 数据库未组织在记录或表中,因此无法创建查询或 .dqy 文件来访问这些数据库。

使用 .rqy 查询文件    Excel 可以打开 .rqy 格式的查询文件,以支持使用此格式的 OLE DB 数据源驱动程序。 有关详细信息,请参阅驱动程序的文档。

使用 .qry 查询文件    Microsoft Query 可以打开和保存 .qry 格式的查询文件,以用于无法打开 .dqy 文件的早期版本的 Microsoft Query。 如果你有一个要用于 Excel 的 .qry 格式的查询文件,在 Microsoft Query 中打开该文件,然后将它另存为 .dqy 文件。 有关保存 .dqy 文件的信息,请参阅 Microsoft Query 帮助。

使用 .iqy Web 查询文件    Excel 可以打开 .iqy Web 查询文件以从 Web 检索数据。

使用外部数据范围和属性

外部数据区域 (也称为查询表) 是定义的名称或表名称,用于定义放入工作表的数据的位置。 连接到外部数据时,Excel 会自动创建外部数据区域。 唯一的例外是连接到数据源的数据透视表,该数据源不会创建外部数据区域。 在 Excel 中,您可以设置外部数据区域的格式和布局,或在计算中使用它,就像处理任何其他数据一样。

Excel 会自动为外部数据区域命名,如下所示:

  • 来自 Office 数据连接 (ODC) 文件的外部数据范围的名称与文件名相同。

  • 数据库中的外部数据范围以查询名称命名。 默认情况下Query_from_源是 用于创建查询的数据源的名称。

  • 文本文件中的外部数据范围以文本文件名称命名。

  • Web 查询中的外部数据范围以检索数据的网页的名称命名。

如果工作表具有来自同一源的多个外部数据区域,则区域会编号。 例如,MyText、MyText_1、MyText_2等。

外部数据区域具有 (属性,请勿与可用于控制) 的连接属性混淆,例如保留单元格格式和列宽。 可以通过在"数据"选项卡上的"连接"组中单击"属性",然后在"外部数据范围属性"或"外部数据属性"对话框中进行更改来更改这些外部数据范围属性。

注意: 如果要共享基于外部数据的摘要或报表,您可以为其他人提供包含外部数据范围的工作簿,也可以创建报表模板。 使用 报表模板 可以保存摘要或报表,而无需保存外部数据,以便文件更小。 当用户打开报表模板时,将检索外部数据。

了解 Excel 和 excel 中的数据源Excel Services

有几个数据对象 (例如外部数据区域和数据透视表) 可用于连接到不同的数据源。 但是,可以连接到的数据源类型在每个数据对象之间是不同的。 您也可以在 Excel Services 中使用和刷新连接数据,但应了解其他限制和解决方法。

Excel 数据对象和数据源支持

下表汇总了 Excel 中每个数据对象支持的数据源。

支持
数据源

Excel
数据
对象


创建 外部
数据
range?

OLE
DB

ODBC

文本
文件

HTML
文件

XML
文件

SharePoint
list

导入文本向导

数据透视表
(非 OLAP)

支持

支持

支持

数据透视表
(OLAP)

Excel 表格

支持

支持

支持

支持

XML 映射

Web 查询

支持

数据连接向导

支持

支持

支持

支持

支持

支持

支持

Microsoft Query

支持

注意: 这些文件是使用导入文本向导导入的文本文件、使用 XML 映射导入的 XML 文件,以及使用 Web 查询导入的 HTML 或 XML 文件,不使用 ODBC 驱动程序或 OLE DB 提供程序来与数据源建立连接。

Excel Services数据源支持

如果要在 Web 浏览器Excel Services (Excel) Excel 中显示 Excel 工作簿,可以连接到数据并刷新数据,但必须使用数据透视表。 Excel Services不支持外部数据范围,这意味着Excel Services不支持连接到数据源、Web 查询、XML 映射或 Microsoft Query 的 Excel 表。

但是,您可以通过使用数据透视表连接到数据源来解决此限制,然后将数据透视表设计和布局为无级别、组或小计的二维表,以便显示所有所需的行和列值。 有关详细信息,请参阅"另请参阅" 部分中 的链接。

了解数据访问组件

Microsoft Windows Server 2003 (Windows XP SP2) MDAC 2.8 中包含的 Microsoft 数据访问组件。 使用 MDAC,可以连接到各种关系和非关系数据源的数据并使用这些数据。 可以使用由 Microsoft 构建和交付或由各种第三方开发的 开放式数据库连接 (ODBC) (ODBC) 驱动程序或 OLE DB 提供程序连接到许多不同的数据源。 在安装 Microsoft Office时,会向计算机添加其他 ODBC 驱动程序和 OLE DB 提供程序。

Windows Vista 和 Windows 7 使用 Windows 数据访问组件 (Windows DAC) 。

若要查看计算机上安装的 OLE DB 提供程序的完整列表,请从数据链接文件显示"数据链接属性"对话框,然后单击"提供程序"选项卡。

若要查看计算机上安装的 ODBC 提供程序的完整列表,请显示 "ODBC 数据库管理员"对话框,然后单击"驱动程序 " 选项卡。

也可使用其他制造商提供的 ODBC 驱动程序和 OLE DB 提供程序从 Microsoft 数据源外的其他源(包括其他类型的 ODBC 和 OLE DB 数据库)获取信息。 有关安装这些 ODBC 驱动程序或 OLE DB 提供程序的信息,请查阅数据库文档或与数据库供应商联系。

使用 ODBC 连接到数据源

以下部分更详细地开放式数据库连接 (ODBC) (ODBC) 的详细信息。

ODBC 体系结构

在 ODBC 体系结构中,应用程序 (例如 Excel) 连接到 ODBC 驱动程序管理器,而 ODBC 驱动程序管理器又使用特定的 ODBC 驱动程序 ((如 Microsoft SQL ODBC 驱动程序) )连接到数据源 (例如 Microsoft SQL Server 数据库) 。

定义连接信息

若要连接到 ODBC 数据源,请执行下列操作:

  1. 确保在包含数据源的计算机上安装了相应的 ODBC 驱动程序。

  2. 通过使用 ODBC 数据源管理员将连接信息存储在注册表或 DSN 文件中,或者使用 Microsoft Visual Basic 代码中的连接字符串将连接信息直接传递给 ODBC 驱动程序管理器,定义数据源名称 (DSN) 。

    若要定义数据源,请在 Windows Vista 中单击"开始"按钮,然后单击"控制面板"。 单击"系统和维护",然后单击"管理工具"。 在 Windows XP 和 Windows Server 中,单击"开始",然后单击"控制面板"。 单击"性能和维护",并单击"管理工具"。 ,然后单击"ODBC (数据源) 。 有关不同选项的详细信息,请单击每个对话框中的"帮助"按钮。

机器数据源

计算机数据源使用用户定义的名称将连接信息存储在注册表中的特定计算机上。 只能在定义机器数据源的计算机上使用机器数据源。 机器数据源分为两种类型,用户和系统。 用户数据源只能由当前用户使用,并且只对该用户可见。 系统数据源可以在计算机上由所有用户使用,并且对计算机的所有用户可见。

若要提高安全性,计算机数据源特别有用,因为它有助于确保只有已登录的用户才能查看计算机数据源,并且远程用户无法将计算机数据源复制到另一台计算机。

文件数据源

文件数据源 (也称为 DSN 文件) 将连接信息存储在文本文件而不是注册表中,通常比计算机数据源更灵活。 例如,可以将文件数据源复制到具有正确 ODBC 驱动程序的任何计算机,以便应用程序可以依赖于与它使用的所有计算机之间的一致且准确的连接信息。 也可以将文件数据源置于一台服务器上,在网络上的多个计算机之间共享,并轻松地将连接信息保留在一个位置。

文件数据源也可以是不可共享的。 不可共享的文件数据源驻留在单台计算机中,并指向计算机数据源。 可以使用不可共享的文件数据源访问来自文件数据源的现有机器数据源。

使用 OLE DB 连接到数据源

以下部分更详细地介绍了对象链接和嵌入数据库 (OLE DB) 的详细信息。

OLE DB 体系结构

在 OLE DB 体系结构中,访问数据的应用程序称为数据使用者 (,例如 Excel) ,允许对数据进行本机访问的程序称为数据库提供程序 (例如用于 SQL Server) 的 Microsoft OLE DB 提供程序。

定义连接信息

通用数据链接文件 (.udl) 包含数据使用者用来通过该数据源的 OLE DB 提供程序访问数据源的连接信息。 可以通过执行下列操作之一来创建连接信息:

  • 在数据连接向导中,使用" 数据链接属性 "对话框为 OLE DB 提供程序定义数据链接。 有关详细信息,请参阅"使用数据 连接向导导入数据"部分

  • 创建一个扩展名为 .udl 的空白文本文件,然后编辑该文件,其中显示了" 数据链接属性 "对话框。

刷新数据

连接到外部数据源时,还可以执行刷新操作来检索更新的数据。 每次刷新数据时,都可以看到数据的最新版本,包括自上次刷新以来对数据进行的任何更改。

下图说明了刷新连接到外部数据源的数据时发生的情况的基本过程。

刷新外部数据的基本流程

1. 刷新操作获取最新数据。

2. 连接文件定义从外部数据源访问和检索数据所需的全部信息。

3. 可以刷新多种数据源:OLAP、SQL Server、Access、OLE DB、ODBC、电子表格和文本文件。

4. 最新数据将添加到当前工作簿。

Excel 提供了许多用于刷新导入的数据的选项,包括每当打开工作簿时刷新数据,以及定期自动刷新数据。 在刷新数据时,可以继续在 Excel 中工作,还可以在刷新数据时检查刷新状态。

如果外部数据源需要 密码 才能访问数据,则每次刷新数据时, 外部数据区域 输入密码。

以编程方式和使用函数导入数据

如果您是开发人员,则 Excel 中有几种方法可用于导入数据:

  • 可以使用 Visual Basic for Applications访问外部数据源。 根据数据源,可以使用数据ActiveX或数据访问对象来检索数据。 还可以在代码中定义一个连接字符串,用于指定连接信息。 例如,当你想要避免要求系统管理员或用户先创建连接文件或简化应用程序的安装时,使用连接字符串非常有用。

  • 如果从 SQL Server 数据库导入数据,请考虑使用 SQL 本机客户端,它是一个独立的数据访问应用程序编程接口 (API) ,用于 OLE DB 和 ODBC。 它将 SQL OLE DB 提供程序和 SQL ODBC 驱动程序合并到一个本机动态链接库 (DLL) 中,同时还提供与 Microsoft 数据访问组件 (MDAC) 不同的新功能。 可以使用 SQL 本机客户端创建新应用程序或增强可以利用较新的 SQL Server 功能的现有应用程序,例如多个活动结果集 (MARS) 、User-Defined 类型 (UDT) 和 XML 数据类型 支持。

  • RTD 函数从支持 COM 自动化的程序检索实时数据。 必须在本地计算机上创建和注册 RTD COM 自动化加载项。

  • SQL。REQUEST 函数与外部数据源连接,从工作表运行查询。 SQL。然后,REQUEST 函数将结果作为数组返回,而无需进行宏编程。 如果此函数不可用,则必须安装 Microsoft Excel ODBC 加载项 (XLODBC.XLA)。 可以从 Office.com 安装加载项。

有关创建应用程序Visual Basic for Applications,请参阅Visual Basic帮助。

隐私级别

  • 在将数据源合并到符合数据分析要求的特定数据之前,请基于数据源隐私级别设置连接到数据源。

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

另请参阅

Power Query 在 Excel 2016 中称为“获取和转换”

Microsoft Power Query for Excel 帮助

使用本机数据库查询从数据库导入数据

Power BI for Office 365

需要更多帮助?

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×