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