Excel Power Query + VBA – 寫入 ODBC ( MySQL )

Loading

前面一篇利用 ODBC 讓 Excel 讀取資料庫的資料 – https://benjr.tw/104821 , 那可以把 Excel 資料回寫到資料庫嗎? 可以利用 VBA 的功能來做到.

參考文章

  1. https://www.youtube.com/watch?v=VRm8tUerrZU
  2. https://terry28853669.pixnet.net/blog/post/64083631-excel-vba%E6%94%B9%E7%94%A8mysql%E7%95%B6%E6%88%90%E8%B3%87%E6%96%99%E5%BA%AB%E5%AD%98%E6%94%BE%E5%A4%A7%E9%87%8F%E8%B3%87%E6%96%99

測試環境 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;")
    

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

  • 建立到新的 worksheet.

從 Excel 上傳資料到資料庫

  • 開啟 VBA
    預設會看不到 VBA 的選單,需到 檔案 / 選項 / 自訂功能區 勾選 開發人員
  • 開啟 開發人員/ 巨集 或是 Visual Basic (空白區按右鍵 / 插入 / 模組)

    模組 / Module1 建立 VBA 內容

    Sub AddAll()
       For i = 2 To Range("A1").End(xlDown).Row
           A = Cells(i, 2)
           B = Cells(i, 3)
           Call ADOCon(A, B)
       Next
       MsgBox "Upload Completed!!", vbInformation
    End Sub
    
    • Range(“A1”).End(xlDown).Row
      抓資料從 A1 開始一直往下一行直到無資料為止.
      其中 xlToLeft (向左) , xlToRight (向右) , xlUp (向上) , xlDown (向下)
      關於 Range 請參考 – https://learn.microsoft.com/zh-tw/office/vba/api/excel.range(object)
    • Cells(i, 2)
      把 Cells 指定的行與列的儲存格內容指到變數.
    Sub ADOCon(A, B)
       Set myCon = CreateObject("ADODB.Connection")
       myCon.Open "DRIVER=MySQL ODBC 8.0 Unicode Driver;USER=ben;Password=111111;PORT=3306;SERVER=192.168.31.129;DATABASE=excel;"
       Sql = "INSERT INTO excel.staff(name, age) VALUES ('" & A & "' ," & B & " );"
       Set myRs = myCon.Execute(Sql)
    End Sub
    
    1. DRIVER=MySQL ODBC 8.0 Unicode Driver
      其他資料庫的連線 DRIVER 參數可以到 – https://www.connectionstrings.com/ 來查詢.
    2. USER=ben;Password=111111
      需可以存取指定資料表的使用者(需全域,可以從任何 IP 登入)與密碼.
    3. PORT=3306
      預設 MySQL TCP Port.
    4. SERVER=192.168.31.129
      MySQL 的 IP.
    5. DATABASE=excel
      剛剛建立的 excel Database(Schema)
    6. INSERT INTO excel.staff(name, age) VALUES (‘” & A & “‘ , ” & B & “);
      上面 SQL 語法是將 Excel 資料寫入到 excel.staff 資料表,這需要懂 SQL 語法 , 請參考 基礎 INSERT , SELECT , UPDATE , DELETE SQL 語法 – https://benjr.tw/95609
  • 新增表單控制項 開發人員/插入/按鈕

    選擇 AddAll
  • 新增資料並點選上傳鍵
  • 檢視資料庫
    資料是上傳了,但舊資料也上傳了一遍,這個需要去調整 SQL 語法去避免這個問題,這邊就不多談.
沒有解決問題,試試搜尋本站其他內容

One thought on “Excel Power Query + VBA – 寫入 ODBC ( MySQL )

  1. 自動引用通知: Linux – File | Benjr.tw

發佈留言

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

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