この記事では、Excelの表の値をMySQLのテーブルに追加する方法についてご説明します。
ちなみに、本記事と似た記事がありますのでそちらもご覧いただけたら幸いです。
【ExcelVBA】Excelの表の値をMySQLのテーブルに追加するには①以上の記事と今回の記事の内容で何が違うのかというと、本記事ではExcelの表の値をINSERT文を使ってMySQLのテーブルに追加しているのに対し、以上の記事ではMySQLのLOAD DATAステートメントを利用して追加しています。
【動画】Excelの表の値をMySQLのテーブルに追加する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
Excelの表の値を1行ずつ取得してINSERT文でMySQLのテーブルに追加しています。
マクロ作成の流れ
実行すると、ExcelのマクロがMySQLのデータベースに接続されます。
Excelファイルとインポート先のテーブルの例
今回は次のExcelファイルを用意しました。
Excelファイルのシートにはヘッダと10件のデータが存在しています。
また、今回用意したインポート先のテーブルは次の通りです。
このテーブルに、Excelファイルのシートにある10件のデータをインポートします。
試しに、以下のように1件もデータが無い状態を用意しておきました。
マクロを実行すると、先ほどのExcelファイルのシートにある10件のデータがインポートされました。
Excelファイルにある10件のデータがテーブル上に存在していることが分かります。
コードの例
Option Explicit Private Sub bt_exec_Click() Dim dataRange As Range '出力するデータのセル範囲 Dim cn As ADODB.Connection 'Connection用変数 Dim cnt As Long 'カウンタ Dim sqlStr As String 'SQL用変数 Const LastRow As Long = 11 'データの最終行 Const dbName As String = "testdb" 'Excelの表のデータを追加するテーブルがあるデータベース名 Const impTbl As String = "syain" 'Excelのデータを追加するテーブル名 '追加するデータセル範囲を指定 Set dataRange = Worksheets("work").Range("A1:D" & LastRow) 'Connectionインスタンスの生成 Set cn = New ADODB.Connection 'MySQLへの接続情報の取得 cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _ "Server=localhost;" & _ "Port=3306;" & _ "Database=" & dbName & ";" & _ "User=root;" & _ "Password=testPass@123456;" 'コネクションを開く cn.Open For cnt = 2 To LastRow ' A2からD11のセル範囲を対象にする場合 sqlStr = "INSERT INTO " & impTbl sqlStr = sqlStr & " (" sqlStr = sqlStr & " id, name, address, age" sqlStr = sqlStr & " )" sqlStr = sqlStr & " VALUES" sqlStr = sqlStr & " (" sqlStr = sqlStr & " " & dataRange.Cells(cnt, 1).Value sqlStr = sqlStr & " , '" & dataRange.Cells(cnt, 2).Value & "'" sqlStr = sqlStr & " , '" & dataRange.Cells(cnt, 3).Value & "'" sqlStr = sqlStr & " , " & dataRange.Cells(cnt, 4).Value sqlStr = sqlStr & " )" 'INSERT文実行する cn.Execute sqlStr Next cnt '後処理 cn.Close Set cn = Nothing End Sub
注目すべきコード①
最初に見て頂きたいのは15行目です。
'追加するデータセル範囲を指定 Set dataRange = Worksheets("work").Range("A1:D" & LastRow)
コードの説明
以上のコードは、MySQLのテーブルに追加するExcelの表のセル範囲を指定するコードです。
注目すべきコード②
次に見て頂きたいのは18行目です。
'Connectionインスタンスの生成 Set cn = New ADODB.Connection
コードの説明
以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を取得して接続するコードです。
このインスタンスがないとマクロがMySQLのデータベースに接続することができないので必ず生成しておきます。
注目すべきコード③
次に見て頂きたいのは21行目から26行目です。
'MySQLへの接続情報の取得 cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _ "Server=localhost;" & _ "Port=3306;" & _ "Database=" & dbName & ";" & _ "User=root;" & _ "Password=testPass@123456;"
コードの説明
以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を用意するコードです。
MySQLのデータベースに接続するための接続情報はいくつかの引数が必要になります。
Driver
Driverには、マクロがMySQLのデータベースに接続するのに必要なODBCドライバーの情報を指定します。
Server
Serverには、接続したいMySQLのサーバーの名称を指定します。
今回はlocalhostに接続します。
Port
Portには、ポートの値を指定します。
Database
Databaseには、接続したいデータベース名を指定します。
User
Userには、MySQLのデータベースに接続するためのユーザ名を指定します。
Password
Passwordには、MySQLのデータベースに接続するためのパスワードを指定します。
注目すべきコード④
次に見て頂きたいのは59行目です。
'コネクションを開く cn.Open
コードの説明
以上のコードは、ConnectionインスタンスのOpenメソッドを実行して、マクロがMySQLのデータベースに接続する処理のコードです。
Connectionインスタンスには「注目すべきコード③」で説明した通り、マクロがMySQLのデータベースに接続するための接続情報をConnectionStringプロパティに代入しているので、ここではOpenメソッドを実行することでマクロがMySQLのデータベースに接続されます。
注目すべきコード⑤
最初に見て頂きたいのは31行目から48行目です。
For cnt = 2 To LastRow ' A2からD11のセル範囲を対象にする場合 sqlStr = "INSERT INTO " & impTbl sqlStr = sqlStr & " (" sqlStr = sqlStr & " id, name, address, age" sqlStr = sqlStr & " )" sqlStr = sqlStr & " VALUES" sqlStr = sqlStr & " (" sqlStr = sqlStr & " " & dataRange.Cells(cnt, 1).Value sqlStr = sqlStr & " , '" & dataRange.Cells(cnt, 2).Value & "'" sqlStr = sqlStr & " , '" & dataRange.Cells(cnt, 3).Value & "'" sqlStr = sqlStr & " , " & dataRange.Cells(cnt, 4).Value sqlStr = sqlStr & " )" 'INSERT文実行する cn.Execute sqlStr Next cnt
コードの説明
以上のコードは、Excelの表のデータ件数分MySQLのテーブルに追加する処理のコードです。
MySQLのテーブルへの追加はSQLのINSERT文を使い、1レコードずつINSERT文を繰り返します。
INSERT文の実行は、ConnectionインスタンスのExecuteメソッドにINSERT文を引数にして実行することでINSERT文が実行されて、MySQLのテーブルにデータが追加されます。
ちなみに、33行目から43行目のコードだけではINSERT文が分かりにくいと思うので、実際に実行するINSERT文を以下にお見せします。
INSERT INTO syain( id, name, address, age ) VALUES( 1, '田中 太一', '東京都港区', 32 )
以上は、項番が1のデータをMySQLのテーブルに追加するINSERT文です。
今回のサンプルデータは10件のデータがあるので、10レコード分INSERT文を実行します。
動作確認
「Excelファイルとインポート先のテーブルの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの6行目の「ADODB.Connection」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim cn As ADODB.Connection 'Connection用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、Excelの表の値をMySQLのテーブルに追加する方法についてご説明しました。
SQLのINSERT文を実行することで簡単にExcelの表の値をMySQLのテーブルに追加することができます。
ただし、1レコードごとにSQLのINSERT文を実行するので、大量のデータを追加するには時間がかかってしまいます。
もし大量のデータをMySQLのテーブルに追加したい場合は、MySQLのLOAD DATAステートメントを利用して追加する方が時間を短縮することができるかもしれません。
MySQLのLOAD DATAステートメントを利用して大量のデータを追加する方法は下の記事で説明していますので、そちらも参考にして頂けたら幸いです。
【ExcelVBA】Excelの表の値をMySQLのテーブルに追加するには①プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。