この記事では、Excelの表の値をMySQLのテーブルに追加する方法についてご説明します。
本記事を参照するにあたって、まず読んで欲しい注意事項があります。
【動画】Excelの表の値をMySQLのテーブルに追加する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
まずは、Excelの表の値を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」と表示されていれば手順完了です。
Excelファイルとインポート先のテーブルの例
今回は次のExcelファイルを用意しました。
Excelファイルのシートにはヘッダと10件のデータが存在しています。
また、今回用意したインポート先のテーブルは次の通りです。
このテーブルに、Excelファイルのシートにある10件のデータをインポートします。
試しに、以下のように1件もデータが無い状態を用意しておきました。
マクロを実行すると、先ほどのExcelファイルのシートにある10件のデータがインポートされました。
Excelファイルにある10件のデータがテーブル上に存在していることが分かります。
コードの例
Option Explicit
Private Sub bt_exec_Click()
Dim csvFile As String 'CSVファイルのフルパス格納用変数
Dim dataRange As Range '出力するデータのセル範囲
Dim wb As Workbook 'ワークブック
Dim fso As Object 'FileSystemObjectオブジェクトのインスタンス用変数
Dim cn As ADODB.Connection 'Connection用変数
Dim sqlStr As String 'SQL用変数
Const dbName As String = "testdb" 'CSVのデータを追加するテーブルがあるデータベース名
Const impTbl As String = "syain" 'CSVのデータを追加するテーブル名
'CSVファイルの出力先を取得
csvFile = ThisWorkbook.Path & "\testData.csv"
'CSVファイルへ出力する範囲を指定
Set dataRange = Worksheets("work").Range("A1:D11")
'新規ブックを作成(新規ブックをCSVファイルとして保存(出力)するために行う)
Set wb = Workbooks.Add
'CSVファイルへ出力するデータのセル範囲を新規ブックにコピーする
dataRange.Copy wb.Worksheets(1).Range("A1")
'FileSystemObjectオブジェクトのインスタンスを作成する
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(csvFile) Then
'CSVファイルが存在している場合
'CSVファイルを削除する
fso.DeleteFile csvFile
End If
'新規ブックをCSVファイルとして保存(出力)する
wb.SaveAs Filename:=csvFile, FileFormat:=xlCSV
'新規ブックを保存せずに閉じる(CSVファイルの保存(出力)が終われば、新規ブックは不要のため)
wb.Close SaveChanges:=False
'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
'CSVファイルのパスの連結文字「\」を「/」に変換する(「\」だとパスを認識できない)
csvFile = Replace(csvFile, "\", "/")
'CSVファイルをMySQLのデータベースのテーブルにインポートするコマンドを取得する
sqlStr = "LOAD DATA LOCAL INFILE '" & csvFile & "' INTO TABLE " & impTbl & " CHARACTER SET cp932 FIELDS TERMINATED BY ',' ENCLOSED BY '""' ESCAPED BY '' LINES TERMINATED BY '\r\n' IGNORE 1 LINES"
'Executeメソッドを実行してCSVファイルをMySQLのデータベースのテーブルにインポートする
cn.Execute sqlStr
'後処理
cn.Close
Set fso = Nothing
Set cn = Nothing
Set fso = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは20行目です。
'CSVファイルへ出力する範囲を指定
Set dataRange = Worksheets("work").Range("A1:D11")
コードの説明
以上のコードは、CSVファイルへ出力する範囲を指定しているコードです。
今回はA1からD11までのセルの値をMySQLのテーブルに追加したいので、Worksheets(“work”).Range(“A1:D11”)と記述し、変数dataRangeに代入します。
※1行目(ヘッダ部)は対象外とします。MySQLのLOAD DATAステートメントの記述の中で1行目は除外するよう設定します。(IGNORE 1 LINES)
注目すべきコード②
次に見て頂きたいのは23行目から44行目です。
'新規ブックを作成(新規ブックをCSVファイルとして保存(出力)するために行う)
Set wb = Workbooks.Add
'CSVファイルへ出力するデータのセル範囲を新規ブックにコピーする
dataRange.Copy wb.Worksheets(1).Range("A1")
'FileSystemObjectオブジェクトのインスタンスを作成する
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(csvFile) Then
'CSVファイルが存在している場合
'CSVファイルを削除する
fso.DeleteFile csvFile
End If
'新規ブックをCSVファイルとして保存(出力)する
wb.SaveAs Filename:=csvFile, FileFormat:=xlCSV
'新規ブックを保存せずに閉じる(CSVファイルの保存(出力)が終われば、新規ブックは不要のため)
wb.Close SaveChanges:=False
コードの説明
以上のコードは、Excelの表の値をCSVファイルに出力する処理のコードです。
また、すでに本マクロを実行した後に本マクロを再度実行したときは、出力したCSVファイルが存在する場合はCSVファイルを削除します。
削除しないまま再度CSVファイルを生成しようとすると以下の確認メッセージが表示されてマクロが停止してしまいます。
なのでDeleteFileメソッドを使って削除しておきます。
コードの詳細
23行目のコードでは、新規ブック(新たなExcelファイル)を作成します。
新規ブックを作成する理由は、元のワークブック(今回で言うと「0249.xlsm」のExcelファイル)に影響を与えずにデータを書き出すためです。
元のワークブックに書き出したくないデータや設定が含まれている場合があるので、新しいワークブックを作成することで、元のワークブックに影響を与えずに必要なデータのみをCSVファイルに書き出すことができます。
26行目のコードでは、Excelのセルの値を新規ブックにコピーする処理です。
新規ブックのA1のセルを起点にExcelのセルの値がコピーされます。
29行目のコードではCSVファイルを削除するためのDeleteFileメソッドを実行するにはFileSystemObjectオブジェクトのインスタンスが必要になるので、ここでFileSystemObjectオブジェクトのインスタンスを生成しておきます。
31行目のコードでCSVファイルが存在しているのか確認し、存在していれば36行目でDeleteFileメソッドを実行しCSVファイルを削除します。
41行目のコードでは、23行目と26行目でExcelファイルのセルの値が出力された新規ブックをSaveAsメソッドを実行してCSVファイルに保存します。
CSVファイルは以下の通りに保存されます。
44行目のコードでは、Closeメソッドを実行して新規ブックを閉じます。
注目すべきコード③
次に見て頂きたいのは47行目です。
'Connectionインスタンスの生成
Set cn = New ADODB.Connection
コードの説明
以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を取得して接続するコードです。
このインスタンスがないとマクロがMySQLのデータベースに接続することができないので必ず生成しておきます。
注目すべきコード④
次に見て頂きたいのは50行目から56行目です。
'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サーバー)どちらも、ファイルリクエスト許可をもらうための設定が必要というわけです。
注目すべきコード⑤
次に見て頂きたいのは59行目です。
'コネクションを開く
cn.Open
コードの説明
以上のコードは、ConnectionインスタンスのOpenメソッドを実行して、マクロがMySQLのデータベースに接続する処理のコードです。
Connectionインスタンスには「注目すべきコード④」で説明した通り、マクロがMySQLのデータベースに接続するための接続情報をConnectionStringプロパティに代入しているので、ここではOpenメソッドを実行することでマクロがMySQLのデータベースに接続されます。
注目すべきコード⑥
次に見て頂きたいのは62行目です。
'CSVファイルのパスの連結文字「\」を「/」に変換する(「\」だとパスを認識できない)
csvFile = Replace(csvFile, "\", "/")
コードの説明
以上のコードは、CSVファイルのパスの連結文字「¥」を「/」に変換する処理のコードです。
LOAD DATAステートメントにCSVファイルをフルパスで指定する際、フルパスの連結文字が「¥」だとパスを認識してくれません。
以上のエラーメッセージは、指定されたファイルが見つからないことを知らせており、CSVファイルのパスの連結文字は「/」に変換しておく必要があります。
変換前：「C:¥work¥10_勉強¥10_VBA関連¥0249¥testData.csv」
変換後：「C:/work/10_勉強/10_VBA関連/0249/testData.csv」
注目すべきコード⑦
次に見て頂きたいのは65行目から68行目です。
'CSVファイルをMySQLのデータベースのテーブルにインポートするコマンドを取得する
sqlStr = "LOAD DATA LOCAL INFILE '" & csvFile & "' INTO TABLE " & impTbl & " CHARACTER SET cp932 FIELDS TERMINATED BY ',' ENCLOSED BY '""' ESCAPED BY '' LINES TERMINATED BY '\r\n' IGNORE 1 LINES"
'Executeメソッドを実行してCSVファイルをMySQLのデータベースのテーブルにインポートする
cn.Execute sqlStr
コードの説明
以上のコードは、MySQLのLOAD DATAステートメントを利用してMySQLのテーブルにCSVファイルのデータをインポートするコマンドを用意してインポート処理を行っている処理のコードです。
68行目のExecuteメソッドの引数にMySQLのテーブルにCSVファイルのデータをインポートするコマンドを指定して実行することで、CSVファイルのデータがインポートされます。
ちなみに、以上のコードだけではLOAD DATAステートメントを使ったコードが分かりにくいと思うので、サンプルで以下に実際に実行するコードお見せします。
LOAD DATA LOCAL INFILE 'C:/work/10_勉強/10_VBA関連/0247/data.csv' -- ローカルファイルからデータを読み込み、テーブルに挿入する
INTO
TABLE syain -- CSVファイルのデータを挿入するテーブルの名前を指定
CHARACTER SET cp932 -- ロードされるデータの文字セットを指定(Shift-JIS)
FIELDS TERMINATED BY ',' -- フィールド（列）の区切り文字をカンマに設定
ENCLOSED BY '"' -- フィールドの値がテキストで囲まれている場合、囲み文字としてダブルクォーテーションを指定
ESCAPED BY '' -- エスケープ文字は指定されていない（空文字列）
LINES TERMINATED BY '\r\n' -- 行を区切る文字です。この場合は、改行コードです
IGNORE 1 LINES -- 最初の行(ヘッダー)を読み込みの対象外として無視
動作確認
「Excelファイルとインポート先のテーブルの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの9行目の「ADODB.Connection」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim cn As ADODB.Connection 'Connection用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、Excelの表の値をMySQLのテーブルに追加する方法についてご説明しました。
今回はExcelのシートの値をMySQLのテーブルに追加するのに、一度CSVファイルに書き出して生成していますが、MySQLのテーブルにCSVファイルのデータを追加するには以下の点を行うようにしましょう。
- サーバーの管理者に「local_infile」の値を変更してよいか確認する
- 現状「local_infile」がどんな値に設定されているかを確認する
MySQLサーバーへのCSVファイルのデータ読み込み許可についてはMySQLサーバーの「local_infile」の値を変更する必要があるので、変更前には必ずサーバー管理者に変更してよいか確認しましょう。
また、MySQLサーバーの「local_infile」の値を変更する際は、事前に「local_infile」の値を確認しておきましょう。
値が分からないと変更前の値に戻せなくなってしまいますからね。
以上の点に気を付けて、Excelの表の値をMySQLのテーブルに追加するときは本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
