この記事では、ExcelのマクロからMySQLのテーブルにCSVファイルのデータをインポートする方法についてご説明します。
本記事を参照するにあたって、まず読んで欲しい注意事項があります。
【動画】ExcelのマクロからMySQLのテーブルにCSVファイルのデータをインポートする実際の動き
本題に入る前に、まずは次の動画をご覧ください。
まずは、CSVファイルのデータをインポートする前にインポート先のテーブル有無を確認しています。
テーブルが無ければテーブルを作成します。
次に、MySQLのLOAD DATAステートメントを利用してインポートしたいCSVファイルのデータをMySQLのデータベースのテーブルにインポートしています。
マクロ作成の流れ
「local_infile」の設定値を「1」でないと、インポート処理が拒否されてしまいます。
なお、インポート処理が終わったら、必ず「STEP.0-1」で確認した「local_infile」の値に戻しておきましょう。
実行すると、ExcelのマクロがMySQLのデータベースに接続されます。
【注意事項】MySQLサーバーの「local_infile」が「1」でないとインポート処理が拒否される
マクロを実行するのに、MySQLサーバーの「local_infile」が「1」でないとインポート処理が拒否されます。
このエラーは、ローカルデータの読み込みが無効になっているために起きたエラーのメッセージで、ローカルデータの読み込みを許可する必要があります。
この状態だとCSVファイルのインポートが拒否されてしまうので、CSVファイルのインポートが拒否されないようにするにはどうすれば良いのかというと、MySQLサーバーの「local_infile」の設定値を変更する必要があります。
「local_infile」の設定値は「1」を設定します。
ではMySQLサーバーの「local_infile」を「1」に設定する方法は、次の通り…とお話しする前に、次の確認作業を行う必要があります。
- サーバーの管理者に「local_infile」の値を変更してよいか確認する
- 現状「local_infile」がどんな値に設定されているかを確認する
MySQLサーバーの設定値を許可なく変更することはNG行為なので、サーバーの管理者に事前に確認をするようにしましょう。
また、MySQLサーバーの値を変えたあとは値をそのままにしてはいけないので、変更前の設定値を必ず確認して戻せるよう現状「local_infile」がどんな値に設定されているかを確認しましょう。
以上の確認作業を必ず行ってから本記事内の手順を進めてください。
以上を踏まえて、「local_infile」の値を変更する際は自己責任でお願いします。
①「local_infile」の値の確認手順
(1)MySQLクライアントからMySQLサーバーにログインする
MySQLクライアントからMySQLサーバーにログインします。
(2)コマンド「SELECT @@local_infile;」を実行する
コマンド「SELECT @@local_infile;」を実行します。
(3)「local_infile」の設定値が表示される
「local_infile」の設定値が表示されます。
以上で「local_infile」の設定値が何か確認することができました。
この値を必ずメモしておき、インポート処理を行った後に戻せるようにしましょう。
②「local_infile」の値の設定手順
(1)MySQLクライアントからMySQLサーバーにログインする
MySQLクライアントからMySQLサーバーにログインします。
※「①「local_infile」の値の確認手順」を行っていてすでにログインしている場合はこのログイン作業は無視して次の手順に進んでください。
(2)コマンド「SET GLOBAL local_infile=1;」を実行する
コマンド「SET GLOBAL local_infile=1;」を実行します。
(3)「local_infile」の設定値が変更される
「local_infile」の設定値が変更されました。
(4)「local_infile」の設定値変更後に設定値が「1」になっているかを確認するため、コマンド「SELECT @@local_infile;」を実行する
「local_infile」の設定値変更後に設定値が「1」になっているかを確認するため、コマンド「SELECT @@local_infile;」を実行します。
「SELECT @@local_infile;」を実行した結果、「local_infile」の設定値が「1」と表示されていれば手順完了です。
CSVファイルとインポート先のテーブルの例
今回は次のCSVファイルを用意しました。
CSVファイルには10件のデータが存在しています。
また、今回用意したインポート先のテーブルは次の通りです。
このテーブルに、CSVファイルにある10件のデータをインポートします。
試しに、以下のように1件もデータが無い状態を用意しておきました。
マクロを実行すると、先ほどのCSVファイルにある10件のデータがインポートされました。
CSVファイルにある10件のデータがテーブル上に存在していることが分かります。
コードの例
Option Explicit Sub test() Dim cn As ADODB.Connection 'Connection用変数 Dim rs As ADODB.Recordset 'レコードセット用変数 Dim sqlStr As String 'SQL用変数 Dim ws As Worksheet 'ワークシート変数 Dim csvFile As String 'CSVファイルのフルパス格納用変数 Const dbName As String = "testdb" 'CSVのデータを追加するテーブルがあるデータベース名 Const impTbl As String = "syain" 'CSVのデータを追加するテーブル名 'シートを取得する Set ws = Worksheets("work") '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;" & _ "ENABLE_LOCAL_INFILE=1;" 'コネクションを開く cn.Open 'Recordsetオブジェクトのインスタンスを生成する Set rs = New ADODB.Recordset 'SQLクエリを実行して、データ件数を取得します。 sqlStr = "SELECT count(*)" sqlStr = sqlStr & " FROM information_schema.tables" sqlStr = sqlStr & " WHERE table_schema = '" & dbName & "' AND table_name = 'syain'" 'SELECT文を実行してRecordsetを開く rs.Open sqlStr, cn, adOpenStatic If rs.Fields(0).Value = 0 Then 'テーブルが存在しない場合 'テーブルを作成する sqlStr = "CREATE TABLE " & impTbl & "(" sqlStr = sqlStr & " id INT PRIMARY KEY" sqlStr = sqlStr & " , name VARCHAR(255)" sqlStr = sqlStr & " , address VARCHAR(255)" sqlStr = sqlStr & " , age integer" sqlStr = sqlStr & ")" 'CREATE TABLE文を実行する cn.Execute sqlStr End If 'インポートするCSVファイルのフルパスを取得する csvFile = ThisWorkbook.Path & "\data.csv" 'CSVファイルのパスの連結文字「\」を「/」に変換する(「\」だとパスを認識できない) csvFile = Replace(csvFile, "¥", "/") 'CSVファイルをMySQLのデータベースのテーブルにインポートするコマンドを取得する sqlStr = "LOAD DATA LOCAL INFILE '" & csvFile & "' INTO TABLE " & impTbl & " FIELDS TERMINATED BY ',' ENCLOSED BY '""' ESCAPED BY '' LINES TERMINATED BY '\r\n'" 'Executeメソッドを実行してCSVファイルをMySQLのデータベースのテーブルにインポートする cn.Execute sqlStr '後処理 cn.Close Set cn = Nothing Set rs = Nothing End Sub
注目すべきコード①
最初に見て頂きたいのは18行目です。
'Connectionインスタンスの生成 Set cn = New ADODB.Connection
コードの説明
以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を取得して接続するコードです。
このインスタンスがないとマクロがMySQLのデータベースに接続することができないので必ず生成しておきます。
注目すべきコード②
次に見て頂きたいのは21行目から27行目です。
'MySQLへの接続情報の取得 cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _ "Server=localhost;" & _ "Port=3306;" & _ "Database=" & dbName & ";" & _ "User=root;" & _ "Password=testPass@123456;" & _ "ENABLE_LOCAL_INFILE=1;"
コードの説明
以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を用意するコードです。
MySQLのデータベースに接続するための接続情報はいくつかの引数が必要になります。
Driver
Driverには、マクロがMySQLのデータベースに接続するのに必要なODBCドライバーの情報を指定します。
Server
Serverには、接続したいMySQLのサーバーの名称を指定します。
今回はlocalhostに接続します。
Port
Portには、ポートの値を指定します。
Database
Databaseには、接続したいデータベース名を指定します。
User
Userには、MySQLのデータベースに接続するためのユーザ名を指定します。
Password
Passwordには、MySQLのデータベースに接続するためのパスワードを指定します。
ENABLE_LOCAL_INFILE
ENABLE_LOCAL_INFILEには、「1」を指定します。
この指定は「【重要】MySQLサーバーの「local_infile」が「1」でないとインポート処理が拒否される」にて「local_infile」についてお話した内容と同様で、CSVファイルをインポートするアクセスを許可してもらうために必要な指定です。
マクロがMySQLにログインする際にENABLE_LOCAL_INFILEに1を指定しないと、LOAD DATAステートメントを実行しようした時にエラーとなります。
このエラーメッセージは、MySQLデータベースにローカルファイルからデータをロードする際に、アクセス制限のためにファイルリクエストが拒否されたことを知らせています。
ファイルリクエストを許可してもらうには、マクロがMySQLにログインする際にENABLE_LOCAL_INFILEに「1」を設定する必要があります。
クライアント側(マクロ)も、サーバー側(MySQLサーバー)どちらも、ファイルリクエスト許可をもらうための設定が必要というわけです。
注目すべきコード③
次に見て頂きたいのは24行目です。
'コネクションを開く cn.Open
コードの説明
以上のコードは、ConnectionインスタンスのOpenメソッドを実行して、マクロがMySQLのデータベースに接続する処理のコードです。
Connectionインスタンスには「注目すべきコード②」で説明した通り、マクロがMySQLのデータベースに接続するための接続情報をConnectionStringプロパティに代入しているので、ここではOpenメソッドを実行することでマクロがMySQLのデータベースに接続されます。
注目すべきコード④
次に見て頂きたいのは32行目から58行目です。
'Recordsetオブジェクトのインスタンスを生成する Set rs = New ADODB.Recordset 'SQLクエリを実行して、データ件数を取得します。 sqlStr = "SELECT count(*)" sqlStr = sqlStr & " FROM information_schema.tables" sqlStr = sqlStr & " WHERE table_schema = '" & dbName & "' AND table_name = 'syain'" 'SELECT文を実行してRecordsetを開く rs.Open sqlStr, cn, adOpenStatic If rs.Fields(0).Value = 0 Then 'テーブルが存在しない場合 'テーブルを作成する sqlStr = "CREATE TABLE " & impTbl & "(" sqlStr = sqlStr & " id INT PRIMARY KEY" sqlStr = sqlStr & " , name VARCHAR(255)" sqlStr = sqlStr & " , address VARCHAR(255)" sqlStr = sqlStr & " , age integer" sqlStr = sqlStr & ")" 'CREATE TABLE文を実行する cn.Execute sqlStr End If
コードの説明
以上のコードは、インポート先のテーブルが存在しているかを確認しなければそのテーブルを作成する処理のコードです。
コードの詳細
33行目のコードでは、recordsetインスタンスを生成しています。
36行目から38行目のコードでは、インポート先のテーブル「syain」が存在するかを確認するSELECT文を組み立て、組み立てたSELECT文を41行目で実行しています。
SELECT文を実行した結果、テーブルが存在しているかを確認するにはrs.Fields(0).Valueの返す値で判定します。(43行目)
rs.Fields(0).Valueの値が0の場合はインポート先のテーブル「syain」が存在していないので、48行目から53行目でCREATE TABLE文を用意して56行目でCREATE TABLE文を実行します。
CREATE TABLE文が正常に実行されれば、インポート先のテーブル「syain」が用意できました。
ちなみに、以上のコードだけではSELECT文とCREATE TABLE文が分かりにくいと思うので、実際に実行するSELECT文とCREATE TABLE文をサンプルで以下にお見せします。
SELECT count(*) FROM information_schema.tables WHERE table_schema = 'testdb' AND table_name = 'syain'
さらに、テーブル「syain」を特定するにはフィールド「table_schema」と「table_name」を参照することでテーブル「syain」が存在するか特定することができます。
「table_schema」にはデータベース名が、「table_name」にはテーブル名が存在しています。
今回のインポート先のテーブル「syain」はデータベース「testdb」に存在しており、WHERE句に「table_schema = ‘testdb’」と指定しています。
さらにWHERE句に「table_name = ‘syain’」と指定することでテーブル「syain」が存在しているかが特定できます。
なお、テーブルが存在している場合はrs.Fields(0).Valueの返す値で判定します。(テーブルがあれば1(件)を返す)
テーブルが存在していない場合は0を返します。
CREATE TABLE syain( id INT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255), age integer )
注目すべきコード⑤
次に見て頂きたいのは61行目から64行目です。
'インポートするCSVファイルのフルパスを取得する csvFile = ThisWorkbook.Path & "\data.csv" 'CSVファイルのパスの連結文字「\」を「/」に変換する(「\」だとパスを認識できない) csvFile = Replace(csvFile, "¥", "/")
コードの説明
以上のコードは、CSVファイルのパスの連結文字「¥」を「/」に変換する処理のコードです。
LOAD DATAステートメントにCSVファイルをフルパスで指定する際、フルパスの連結文字が「¥」だとパスを認識してくれません。
以上のエラーメッセージは、指定されたファイルが見つからないことを知らせており、CSVファイルのパスの連結文字は「/」に変換しておく必要があります。
変換前:「C:¥work¥10_勉強¥10_VBA関連¥0247¥data.csv」
変換後:「C:/work/10_勉強/10_VBA関連/0247/data.csv」
注目すべきコード⑥
次に見て頂きたいのは67行目から70行目です。
'CSVファイルをMySQLのデータベースのテーブルにインポートするコマンドを取得する sqlStr = "LOAD DATA LOCAL INFILE '" & csvFile & "' INTO TABLE " & impTbl & " FIELDS TERMINATED BY ',' ENCLOSED BY '""' ESCAPED BY '' LINES TERMINATED BY '\r\n'" 'Executeメソッドを実行してCSVファイルをMySQLのデータベースのテーブルにインポートする cn.Execute sqlStr
コードの説明
以上のコードは、MySQLのLOAD DATAステートメントを利用してMySQLのテーブルにCSVファイルのデータをインポートするコマンドを用意してインポート処理を行っている処理のコードです。
70行目のExecuteメソッドの引数にMySQLのテーブルにCSVファイルのデータをインポートするコマンドを指定して実行することで、CSVファイルのデータがインポートされます。
ちなみに、以上のコードだけではLOAD DATAステートメントを使ったコードが分かりにくいと思うので、サンプルで以下に実際に実行するコードお見せします。
LOAD DATA LOCAL INFILE 'C:/work/10_勉強/10_VBA関連/0247/data.csv' -- ローカルファイルからデータを読み込み、テーブルに挿入する INTO TABLE syain -- CSVファイルのデータを挿入するテーブルの名前を指定 FIELDS TERMINATED BY ',' -- フィールド(列)の区切り文字をカンマに設定 ENCLOSED BY '"' -- フィールドの値がテキストで囲まれている場合、囲み文字としてダブルクォーテーションを指定 ESCAPED BY '' -- エスケープ文字は指定されていない(空文字列) LINES TERMINATED BY '\r\n' -- 行を区切る文字です。この場合は、改行コードです
動作確認
「CSVファイルとインポート先のテーブルの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」と6行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim cn As ADODB.Connection 'Connection用変数 Dim rs As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、ExcelのマクロからMySQLのテーブルにCSVファイルのデータをインポートする方法についてご説明しました。
ExcelのマクロからMySQLのテーブルにCSVファイルのデータをインポートするには以下の点を行うようにしましょう。
- サーバーの管理者に「local_infile」の値を変更してよいか確認する
- 現状「local_infile」がどんな値に設定されているかを確認する
MySQLサーバーへのCSVファイルのデータ読み込み許可についてはMySQLサーバーの「local_infile」の値を変更する必要があるので、変更前には必ずサーバー管理者に変更してよいか確認しましょう。
また、MySQLサーバーの「local_infile」の値を変更する際は、事前に「local_infile」の値を確認しておきましょう。
値が分からないと変更前の値に戻せなくなってしまいますからね。
以上の点に気を付けて、ExcelのマクロからMySQLのテーブルにCSVファイルのデータをインポートするときは本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。