【ExcelVBA】ExcelのマクロからMySQLのテーブルにCSVファイルのデータをインポートするには

この記事では、ExcelのマクロからMySQLのテーブルにCSVファイルのデータをインポートする方法についてご説明します。

本記事を参照するにあたって、まず読んで欲しい注意事項があります。

【動画】ExcelのマクロからMySQLのテーブルにCSVファイルのデータをインポートする実際の動き

本題に入る前に、まずは次の動画をご覧ください。


まずは、CSVファイルのデータをインポートする前にインポート先のテーブル有無を確認しています。

テーブルが無ければテーブルを作成します。

次に、MySQLのLOAD DATAステートメントを利用してインポートしたいCSVファイルのデータをMySQLのデータベースのテーブルにインポートしています。

マクロ作成の流れ

前準備
STEP.0-1
【MySQL】「local_infile」の値を変更するため、MySQLサーバーの「local_infile」の設定値を確認する
「local_infile」の値を変更するため、MySQLサーバーの「local_infile」の設定値を確認しておきます。
STEP.0-2
【MySQL】MySQLサーバーの「local_infile」の設定値を「1」または「ON」に設定する
サーバーの「local_infile」の設定値を「1」または「ON」に設定します。
「local_infile」の設定値を「1」でないと、インポート処理が拒否されてしまいます。
なお、インポート処理が終わったら、必ず「STEP.0-1」で確認した「local_infile」の値に戻しておきましょう。
▼▼▼以降マクロ側の流れ▼▼▼
STEP.1
Connectionインスタンスを生成する
Connectionインスタンスを生成します。
STEP.2
MySQLへの接続情報を取得する
MySQLへの接続情報を取得します。
STEP.3
STEP.2の接続情報をOpenメソッドの引数に指定してOpenメソッドを実行する
STEP.2の接続情報をOpenメソッドの引数に指定してOpenメソッドを実行します。
実行すると、ExcelのマクロがMySQLのデータベースに接続されます。
STEP.4
インポート先のテーブル有無をチェックし、なければテーブルを生成する
インポート先のテーブル有無をチェックし、なければテーブルを生成します。
STEP.5
MySQLのLOAD DATAステートメントを利用してインポートしたいCSVファイルのデータをMySQLのデータベースのテーブルにインポートする
MySQLのLOAD DATAステートメントを利用してインポートしたいCSVファイルのデータをMySQLのデータベースのテーブルにインポートします。

【注意事項】MySQLサーバーの「local_infile」が「1」でないとインポート処理が拒否される

マクロを実行するのに、MySQLサーバーの「local_infile」が「1」でないとインポート処理が拒否されます。

このエラーは、ローカルデータの読み込みが無効になっているために起きたエラーのメッセージで、ローカルデータの読み込みを許可する必要があります。

この状態だとCSVファイルのインポートが拒否されてしまうので、CSVファイルのインポートが拒否されないようにするにはどうすれば良いのかというと、MySQLサーバーの「local_infile」の設定値を変更する必要があります。

「local_infile」の設定値は「1」を設定します。

ではMySQLサーバーの「local_infile」を「1」に設定する方法は、次の通り…とお話しする前に、次の確認作業を行う必要があります。

  1. サーバーの管理者に「local_infile」の値を変更してよいか確認する
  2. 現状「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に接続します。

【参考:MySQL Workbenchの画面】

Port

Portには、ポートの値を指定します。

【参考:MySQL Workbenchの画面】

Database

Databaseには、接続したいデータベース名を指定します。

【参考:MySQL Workbenchの画面】

User

Userには、MySQLのデータベースに接続するためのユーザ名を指定します。

【参考:MySQL Workbenchの画面】

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文
SELECT
    count(*)
FROM
    information_schema.tables
WHERE
    table_schema = 'testdb'
AND table_name = 'syain'
参考:information_schema.tablesのフィールド「table_schema」と「table_name」について
なぜテーブル「syain」が存在するか確認するのに「information_schema.tables」をSELECTするのかというと、データベース内に存在するテーブル名が取得できるからです。
さらに、テーブル「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を返します。

テーブルが存在する場合のrs.Fields(0).Valueが返す値:1

テーブルが存在しない場合のrs.Fields(0).Valueが返す値:0


CREATE TABLE文
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ファイルのパスの連結文字は「/」に変換しておく必要があります。

【参考】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ステートメントを使ったCSVファイルのインポート用コマンド
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」ボタンをクリックします。

  1. 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ファイルのデータをインポートするには以下の点を行うようにしましょう。

  1. サーバーの管理者に「local_infile」の値を変更してよいか確認する
  2. 現状「local_infile」がどんな値に設定されているかを確認する

MySQLサーバーへのCSVファイルのデータ読み込み許可についてはMySQLサーバーの「local_infile」の値を変更する必要があるので、変更前には必ずサーバー管理者に変更してよいか確認しましょう。

また、MySQLサーバーの「local_infile」の値を変更する際は、事前に「local_infile」の値を確認しておきましょう。

値が分からないと変更前の値に戻せなくなってしまいますからね。

以上の点に気を付けて、ExcelのマクロからMySQLのテーブルにCSVファイルのデータをインポートするときは本記事を参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→ TechAcademyの「1週間 無料体験」はこちら