Excel Power Query – ODBC ( MySQL )

Excel 的 Data Source 支援多種來源, 除了從檔案外還支援 ODBC 如 MySQL 等資料庫來源 ,下面就來看一下要怎麼從 Excel 同步 (synchronization) 資料庫的資料.

參考文章

  1. https://www.youtube.com/watch?v=VRm8tUerrZU
  2. https://learn.microsoft.com/zh-tw/sql/integration-services/import-export-data/connect-to-an-odbc-data-source-sql-server-import-and-export-wizard?view=sql-server-ver16

測試環境 Windows 2019 Stnadrad + Excel 2016 與 MySQL Community 8.0.30 + Connector/ODBC 8.0.30 (版本都是 64 bits)

架構如下 (MySQL 與 Excel 皆在同一台機器上)

MySQL Community

下載並安裝 MySQL Community 8.0.30 – https://dev.mysql.com/downloads/mysql/ , 需安裝 MySQL Server 與 Connector/ODBC . 詳細安裝請參考 https://benjr.tw/94826

接下來建立測試用的資料.

  • 建立 Database(Schema) 與 table
    CREATE SCHEMA excel;
    
    CREATE TABLE excel.staff (
      id INT NOT NULL AUTO_INCREMENT,
      name VARCHAR(45) NULL,
      age INT NULL,
      PRIMARY KEY (id));
    
  • 新增資料 (Records)
    INSERT INTO excel.staff (name, age) VALUES ('ben', '30');
    INSERT INTO excel.staff (name, age) VALUES ('lily', '8');
    
    SELECT * FROM excel.staff;
    +----+------+------+
    | id | name | age  |
    +----+------+------+
    |  1 | ben  |   30 |
    |  2 | lily |    8 |
    +----+------+------+
    
  • Create MySQL User & Assign privilege
    透過 MySQL WorkBench 建立 ben@% 使用者 (% 代表全域,可以從任何 IP 登入) ,權限請自行決定 (測試時可先開成 DBA ,權限全開)

Excel

Excel 端需安裝以下工具.

  1. Visual Studio 2019 x64 redistributable – https://learn.microsoft.com/en-US/cpp/windows/latest-supported-vc-redist?view=msvc-170#visual-studio-2015-2017-2019-and-2022
  2. MySQL Connector/ODBC
    如果 Excel 不是跟 MySQL Server 在同台機器上時需要額外安裝 MySQL Connector/ODBC – https://dev.mysql.com/downloads/connector/odbc/ 下載與安裝後開啟 odbc data source (64bits) 並測試一下連線.

    從開始鍵入 odbc 來搜尋.

    Add 新增連線來測試一下 ( 資料會儲存為 test.dsn )

    填入 IP , Port (3306) , 使用者 (root 預設只能在本地端,可以用剛剛建立的 ben 使用者), 密碼 與前面建立好的 Database(Schema) excel , 並 Test 是否出現 Connection Successful

回到 Excel

  • 資料 / 新查詢 / 從其他來源 / 空白查詢
  • 在查詢鍵入 Power Query 指令,關於 Odbc.Query 請參考 – https://learn.microsoft.com/zh-tw/powerquery-m/odbc-query
    = Odbc.Query("DRIVER=MySQL ODBC 8.0 Unicode Driver;USER=ben;PORT=3306;SERVER=192.168.31.129;DATABASE=excel", "SELECT * FROM excel.staff;")
    
    1. DRIVER=MySQL ODBC 8.0 Unicode Driver
      其他資料庫的連線 DRIVER 參數可以到 – https://www.connectionstrings.com/ 來查詢.
    2. USER=ben
      需可以存取指定資料表的使用者(需全域,可以從任何 IP 登入).
    3. PORT=3306
      預設 MySQL TCP Port.
    4. SERVER=192.168.31.129
      MySQL 的 IP.
    5. DATABASE=excel
      剛剛建立的 excel Database(Schema)
    6. SELECT * FROM excel.staff
      主要看要查詢什麼資料,上面 SQL 語法是抓所有在 excel.staff 資料表的資料,這需要懂 SQL 語法 , 請參考 基礎 INSERT , SELECT , UPDATE , DELETE SQL 語法 – https://benjr.tw/95609

  • 請指定連接方式 /編輯認證 , 這邊需鍵入資料庫使用者與密碼 (預設的 root 只能用在本地端 127.0.0.1 才能連線)
  • 資料擷取正確就可以看到資料庫的資料了.

  • 看是要建立到新的 worksheet 或是原先 worksheet.

之後在 MySQL / excel 資料庫 / staff 資料表 新增的資料,只需要在 Excel 執行重新整理就會同步到 Excel 上.

遇過的問題

MySQL Connector/NET 8.0.30 只提供 32bits ,在連線時出現需要 64 bits 的錯誤訊息,後來就改用 MySQL Connector/ODBC 64bits 就沒問題了.

DataSource.MissingClientLibrary: MySQL: 找不到具有不變名稱 'MySql.Data.MySqlClient' 的資料庫提供者。
此錯誤可能是這部電腦上缺少提供者所需要的特定用戶端軟體所致。若要下載此提供者適用的用戶端軟體,請前往以下網站,並最少要選擇 64 位元 (x64) 版本的 'MySQL Connector/Net': https://go.microsoft.com/fwlink/?LinkId=278885
詳細資料:
    DataSourceKind=MySql
    DataSourcePath=192.168.31.129;test
    ClientLibraryName=MySQL Connector/Net
    DownloadLink=https://go.microsoft.com/fwlink/?LinkId=278885

其他連線方式

  1. MySQL Community + Connector/NET
    Connector/NET 下載 – https://dev.mysql.com/downloads/connector/net/
  2. Postgresql + Npgsql
    Postgresq – 下載 https://www.postgresql.org/download/
    Npgsql 下載 (類似 MySQL 的 Connector) https://github.com/npgsql/Npgsql/releases
    參考文章 – https://learn.microsoft.com/en-us/power-query/connectors/postgresql
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料