Power Query 系列 (04) - 从 Web 导入数据

Power Query (PQ) 从 Web 导入数据，主要有如下几种应用：

数据包含表格格式，导入表格中的数据
Restful API 数据导入
OData 格式数据导入

下面就介绍以上三种数据格式的导入方法。

导入页面中含有 table 的数据

因为网络页面具有很大不确定性，比如数据变化，或者页面过几天就不见了，所以无法保证我所选取的页面在您查看的时候一定还在。但基本方法类似，您也可以找到一个类似的页面来操作。

这里给出的一个页面是中国 211 大学的一个清单，网址为 https://www.dxsbb.com/news/2799.html，页面含有两个表格，如下：

现在我们将页面的 211 大学数据导入到 PQ Query 。切换到【数据】选项卡，通过【数据获取】- 【自其他源】- 【自网站】，打开连接界面：

输入上面的网址，点击确定按钮：

在接下来导航器界面中，左边部分已经出现了两个 table：table0 和 table1，右边预览区默认在表视图。可以点击 table0 或者 table1，右边预览区在表视图中会将解析到的数据预览。

点击加载按钮，将数据加载到 Excel 工作表，导入的工作完成。

导入 Restful API 数据

下面演示提供 Restful 服务的后端从 url 导入 json 格式数据的方法，本示例使用 SAP 系统提供的 Restful 服务。

之前曾经用不同语言实现过 Restful Service, 也在博客中记录了实现的过程。感兴趣的读者可以参考我的文章，或者网络上其他文章。如果不是程序开发人员的话，使用其他语言实现 Restful API 可能有一定难度。

在本示例中，Restful 服务返回的 json 格式数据如下：

[
    {
        "mandt": "001",
        "empid": "1",
        "empname": "Stone Wang",
        "empaddr": "Wuhan"
    },
    {
        "mandt": "001",
        "empid": "2",
        "empname": "Alice Brown",
        "empaddr": "England"
    },
    {
        "mandt": "001",
        "empid": "3",
        "empname": "张三",
        "empaddr": "武汉"
    },
    {
        "mandt": "001",
        "empid": "4",
        "empname": "李四",
        "empaddr": "Wuhan"
    },
    {
        "mandt": "001",
        "empid": "5",
        "empname": "王五",
        "empaddr": "武汉经开"
    },
    {
        "mandt": "001",
        "empid": "6",
        "empname": "王二",
        "empaddr": "北京"
    }
]

切换到【数据】选项卡，通过【数据获取】- 【自其他源】- 【自网站】，打开连接界面。在页面中输入 url 路径：

一般来说，这种提供数据服务的 url 是需要校验用户是否是合法用户（authentication），在 SAP 提供的服务中，使用的是基本认证方式（basic authentication），所以接下来，进入认证界面，选择基本，输入登录的用户名和密码：

后续的操作与上一篇文章提到的导入 json 格式数据类似。本示例返回 json 格式是一个 json 数组，数组每一项是一个 json 对象，PQ 导入的时候，将数组对应到 List，将 json 对象对应到 Record:

将 List 转换成 Table:

点击 Column 右边的图标，进行展开操作：

展开的时候，不使用原始列名作为前缀：

点击确定，数据导入到 PQ：

在主页中，点击关闭并上载，数据上载至 Excel sheet。

导入 OData 格式数据

OData: 开放数据协议（Open Data Protocol，缩写 OData）是一种描述如何创建和访问 Restful 服务的 OASIS 标准。该标准由微软发起，前三个版本1.0、2.0、3.0 都是微软开放标准。第四个版本4.0 于 2014 年 3 月 17 日在 OASIS 投票通过成为开放工业标准。

OData 是用来查询和更新数据的一种 Web协议，提供了把存在于应用程序中的数据暴露出来的方式。OData 运用且构建于很多 Web 技术之上，比如 HTTP、Atom Publishing Protocol（AtomPub）和 JSON，提供了从各种应用程序、服务和存储库中访问信息的能力。OData 被用来从各种数据源中暴露和访问信息，这些数据源包括但不限于：关系数据库、文件系统、内容管理系统和传统 Web 站点。

Restful 只是一种设计 Web 服务的思想，不是一种标准化的协议。正由于缺乏标准化，从而导致各家公布的 Restful API 统一通用方面的欠缺。OData 就是为弥补这种欠缺而被提出来的标准协议。

http://services.odata.org/ 这个站点提供了 OData data service 的示例，并且可以对 OData 数据进行 CRUD 操作。我们在浏览器中这个页面，点击 Browse the Read-Only Northwind Service 超链接，进入 https://services.odata.org/V3/Northwind/Northwind.svc/ 页面。这个页面在浏览器中的显示如下：

接下来在 PQ 中导入 OData 格式数据。切换到【数据】选项卡，通过【获取数据】- 【自其他源】- 【从 OData 源】打开连接界面：

在 URL 中输入服务的根路径，点击确定按钮：

在接下来的导航器界面中，左边导航区已经可以看到有哪些数据了，比如 Customers, Products 等，选择一个或者多个数据导入 PQ 即可。这里我们选择 Categories:

点击加载按钮，数据加载到 PQ: