Power Query 系列 (04) - 從 Web 導入數據

Power Query 系列 (04) – 從 Web 導入數據

2019 年 10 月 4 日
筆記

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/ 頁面。這個頁面在瀏覽器中的顯示如下：