ExcelのVBAで特定シートの中身を一発でMySQL内のテーブルに反映させたい

Question

前提・実現したいこと

下記のようなテーブルがExcelのシートにあるとして、このテーブルの内容を
MySQLに反映させるプログラムを作成したいと思っています。

発生している問題・エラーメッセージ

ネットで探してみて、これをRecordsetを用いて別のシートに反映させるプログラムはあったのですが、
mysqlに直接反映させる方法が分かりませんでした。
pythonでやるのであれば、pandasに取り込んで、to_sqlなどすれば解決すると思うのですが、
VBAは不慣れで方法が分かりませんでした。（今回のプログラムはVBAで記載する必要があります）
お手数をおかけいたしますが、ご教示いただくことは可能でしょうか。

該当のソースコード

Sub test_register_to_db()
    Dim cn_excel   As ADODB.Connection
    Dim rs_excel   As ADODB.Recordset
    Call DB_Connect 'mysqlのDBに接続(connectionはcn)
    
    Set cn_excel = CreateObject("ADODB.Connection")
    Set rs_excel = CreateObject("ADODB.Recordset")
    cn_excel.Provider = "Microsoft.ACE.OLEDB.12.0"
    '1行目がヘッダの場合はHDR=YESにする。NOの場合はF1,F2,F3・・・と番号が振られる。
     
    cn_excel.Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"
      
    cn_excel.Open ThisWorkbook.FullName '自ワークブックのファイル名を指定しているが、
    'もちろん別ワークブックでもOK！
      
    strSQL = ""
    strSQL = strSQL & " SELECT * "
    strSQL = strSQL & " FROM [db登録$B2:F10] " 'Sheet2のA3:Z100にデータがあると仮定する。
    rs_excel.Open strSQL, cn_excel, adOpenKeyset, adLockReadOnly

    
    ThisWorkbook.Worksheets("コピー先").Cells(10, 3).CopyFromRecordset rs_excel 'シート1のセル(10,3)からデータをセットする
        
    rs_excel.Close
    Set rs_excel = Nothing
    cn_excel.Close
    Set cn_excel = Nothing

End Sub

試したこと

ネットでサンプルコードを探し、これを改造してみた。

Answer 1

mysqlにデータを追加する場合は,SQLのinsertを使う必要があります。

こちらを確認されてみると参考になると思います。

直接セルを指定していませんが、テーブルを作ってデータをinsertして
mysqlのデータを表示するという一覧の動作を確認できます。

https://excelwork.info/excel/databasemysql/

この部分がinsert文になります。

 'データ追加
    strSQL = "insert into 仕入先一覧 values " & _
             "('00001', '大門商店', '40012345'), " & _
             "('00002', '中門商店', '11012311'), " & _
             "('00003', '小門商店', '12012221'), " & _
             "('00004', '日進電器', '13422900'), " & _
             "('00005', '山本電気', '11199923'), " & _
             "('00006', '松尾商社', '23247001'), " & _
             "('00007', '丸得商店', '11223345'), " & _
             "('00008', '青葉倉庫', '12120000'), " & _
             "('00009', '緑銀行', '12345678'), " & _
             "('00010', '赤黄信用金庫', '87654321');"
    rs.Source = strSQL

Comments

[H.K2]

ご回答ありがとうございます。
insert文の場合、特に長さに制限などはないのでしょうか。
上の例だと10レコードしかないのですが、将来的に5000レコードとかある場合、
すごい長いinsert文になるのですが、問題なく動きますでしょうか。

[退会済みユーザー]

mysqlにはCSVをテーブルに取り込む命令があります。
エクセルシートをCSV出力し、それを一括で読み込むコードを記述すれば
シンプルなVBAで済むと思います。

関連する記事の（例）

1,CSVファイルをMySQLにインポートするSQL文（例）

https://www.virment.com/sql-for-import-csv-mysql/

https://mugendennou.net/mysql/8_1loaddatainfile/

2,Excelシート上のデータを DB にインポートするVBAマクロ

https://language-and-engineering.hatenablog.jp/entry/20090327/p1

[退会済みユーザー]

まとめてインポートとは少し違いますが皆さん色々されてるようですね。。
下記のケースですと、MysqlにinsertするSQLを自動で作成するVBAを紹介しています。
windowsですとコマンドプロンプトでにsqlを貼り付けて実行するパターンですね・・。

【VBA】ExcelマクロからSQLのinsert文を自動生成する方法

https://utage.headwaters.co.jp/blog/archives/2954

Answer 2

6000件でinsert文を実験してみました。PCのスペックによりますがwindows10 64bit/メモリ8Gで
10秒～15秒くらいでした。
データはサンプルです。

サンプルデータベース作成

create database db_users;

use db_users;

create table t_users(
id varchar(20) not null primary key,
name varchar(20),
password varchar(32),
section varchar(15)
);

sheet:data1
データ数:6000件

|id|name|password|setion|
|ID1|ダミー1|XXXX|:YY部|
|ID2|ダミー2|XXXX|:ZZ部|

Sub Mysql_連続insert()

'ExcelでADO・ADODBへの参照設定で下記をチェック（古いバージョンでも可）
'Microsoft ActiveX 'Data Objects 6.1 Library


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim connectionString As String
Dim sqlStr As String

Dim id As String
Dim name As String
Dim password As String
Dim section As String


'接続文字列
connectionString = "Driver={MySQL ODBC 8.0 ANSI Driver};" _
                & " SERVER=localhost;" _
                & " DATABASE=db_users;" _
                & " USER=root;" _
                & " PASSWORD=test;"

'ADODB.Connection生成
Set cn = New ADODB.Connection

On Error GoTo Err

'MySQLに接続
cn.Open connectionString

For i = 2 To 6000

    With Sheets("data1")
        id = .Range("A" & i).Value
        name = .Range("B" & i).Value
        password = .Range("C" & i).Value
        section = .Range("D" & i).Value
     
    End With
        
        sqlStr = "insert into t_users (id,name,password,section) values ('" & id & "','" & name & "','" & password & "','" & section & "')"
        Set rs = cn.Execute(sqlStr)
    
Next

cn.Close
Set rs = Nothing
Set cn = Nothing

MsgBox "データの登録がおわりました"
Exit Sub

Err:
    Set rs = Nothing
    Set cn = Nothing
    MsgBox (Err.Description)

End Sub

Comments

[H.K2]

ありがとうございます！助かりますー。

[退会済みユーザー]

ついでに。。。今回のご質問中、Mysql接続関数(select文用)を作りました。
拡張して使ってみてください・
この例では、Excelの指定したdataシートにsqlの結果を展開します。
select文と指定シートを変えることでsqlの検証が簡単になると思います。

使い方。

Sub test()

Dim sql As String

sql = "select * from t_users"

Call Mysql_select("data", sql)　 '自作関数使い方 Mysql_select(展開したいシート名、select構文)

end sub

sub Mysql_select(sh As String, sql As String)

'mysql関数　Mysql_select(展開したいシート名、select構文)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim connectionString As String
Dim sqlStr As String

Dim rowNo As Integer
Dim colNo As Integer
Dim i As Integer
Dim item As Variant

'接続文字列
connectionString = "Driver={MySQL ODBC 8.0 ANSI Driver};" _
& " SERVER=localhost;" _
& " DATABASE=db_users;" _
& " USER=root;" _
& " PASSWORD=;"

'ADODB.Connection生成
Set cn = New ADODB.Connection

On Error GoTo Err

'MySQLに接続
cn.Open connectionString

sqlStr = sql

Set rs = cn.Execute(sqlStr)

'シートデータクリア
Worksheets(sh).Cells.Clear

rowNo = 2
colNo = 1
i = 1
'■フィールド名抽出
For Each fld In rs.Fields
Worksheets(sh).Cells(1, i).Value = fld.name
i = i + 1
Next

'■データ抽出
Do While rs.EOF = False

For Each item In rs.Fields

Worksheets(sh).Cells(rowNo, colNo).Value = item.Value
colNo = colNo + 1

Next
colNo = 1
rowNo = rowNo + 1
rs.MoveNext
Loop

cn.Close
Set rs = Nothing
Set cn = Nothing

Exit Sub

Err:
Set rs = Nothing
Set cn = Nothing
MsgBox (Err.Description)

End Sub

[退会済みユーザー]

ついでに・・・・。
手作業でエクセルシートをWindows cドライブ直下にCSV保存したとしてinportするMysqlコマンドは下記のとおりです。
1秒かからないくらいで6000件の取り込みが終わります。

load data local infile "C:\data.csv" INTO TABLE t_users FIELDS terminated by ',' enclosed by '"' escaped by '"';

mysqlについてはネットで調べてみてください。

今回の記事に関連して、下記の書籍ではExcelVBA・Userfomでmysqlクライアント管理ツールを作る
事例を紹介しています（高橋良明さんの書籍で関連書籍は3つあります）。
購入後、勉強した気になり開いていませんでしたが3冊とも家にありました。

python+pandasをご使用になられるのでしたら、テストデータ作成・開発にぜひ。

■参考：Amazon
Excelで学ぶMySQL5.0―もう迷わない!データベースの勘所 スラスラ書けるSQLプログラミングの急所 (日本語) 単行本 – 2006/5/1

[H.K2]

いろいろありがとうございますー。ちょっと買ってみます！助かります！
…と思ったんですが、amazonだと中古品でめっちゃ高い値段になってますね…。
コロナが収まったら図書館で借りて読んでみます。ありがとうございました。
レコードが数万件くらいあってボトルネックが気になってて、一発でやると早くなるかな、というところが気になってたのでありがたいですー！
個人的にはpython+pandasでやりたいのですが、会社でpython使えるの僕しかいなくて、VBAで書かないといけなくて…????

[退会済みユーザー]

あらま、高額な値段でしたか(たけーな)。。。差し上げでもいいのですが、10分ぐらいしか私も読んでいないので・・。

でも、基本的な動作のサンプルはネットを調べると色々でてきますので、
あとは、Access＋excel VBAで組むADOのコードを参考にすると応用がきくかもしれませんね。

接続を確立してしまったら、あとはADOの事例に従えばいいという安直な考えでいくとうまくいきます。
ですから、今回の事例はMYSQLの事例なのですが私はADOの事例を調べました。

・・・。私も素人ですので、コツみたいなものということで勘弁してくださいませ。

python100本ノックとか本を買ったりと私もpython好きです。
最近はLinux mintに開発環境を構築したりして（満足していじっていない）
VBAに似た感じでしょうか・・・。

[H.K2]

了解しました。ADOの事例を調べつつやってみますーありがとうございます。
pythonはコードが読みやすくて好きですね。VBAはどうもなんか癖のようなもの（＋前提とか？）が強くてまだまだ苦手感があります…。
#というか、うちの会社の先輩のコードがすごい汚くて、それで苦手になったところが大きいような…
(マテ
一応最近RubberDuckを入れて、VBE環境を少しだけ強化したんですが、pycharmとかVSCodeみたいな
レベルには全然ならなくて…????(RubberDuckは、日本語の紹介サイトあまりなくて、いまいちよくわからないですし…）