この記事では、ExcelのマクロからSQLiteのテーブルにCSVファイルのデータをインポートする方法についてご説明します。
【動画】ExcelのマクロからSQLiteのテーブルにCSVファイルのデータをインポートする実際の動き
本題に入る前に、まずは次の動画をご覧ください。
sqlite3.exe(コマンドプロンプトでSQLiteを操作するための実行ファイル)をコマンドプロンプト上で実行させてインポート処理を行っています。
マクロ作成の流れ
CSVファイルとインポート先のテーブルの例
今回は次のCSVファイルを用意しました。
CSVファイルにはヘッダと10件のデータが存在しています。
また、今回用意したインポート先のテーブルは次の通りです。
このテーブルに、CSVファイルにある10件のデータをインポートします。
マクロを実行すると、先ほどのCSVファイルにある10件のデータがインポートされました。
CSVファイルにある10件のデータがテーブル上に存在していることが分かります。
コードの例
Option Explicit
Sub test()
Dim command As String 'コマンド文用変数
Dim strSQL As String 'SQL文用変数
Dim conn As ADODB.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数
'SQLiteの実行ファイル(sqlite3.exe)のパス
Const exePath As String = "C:/Program Files/SQLite ODBC Driver for Win64/sqlite3.exe"
'SQLiteデータベースのパス
Const dbPath As String = "C:/work/0260_DB01.db"
'CSVファイルのパス
Const csvFilePath As String = "C:/work/0260_test_data.csv"
'インポート先のテーブル名
Const impTbl As String = "syain"
'importコマンドを実行するコマンドライン文字列を作成
command = exePath & " " & dbPath & " "".mode csv"" "".separator ,"" "".import --skip 1 " & csvFilePath & " " & impTbl & """"
'コマンドを実行
Shell command, vbNormalFocus
End Sub
注目すべきコード①
最初に見て頂きたいのは10行目から19行目です。
'SQLiteの実行ファイル(sqlite3.exe)のパス
Const exePath As String = "C:/Program Files/SQLite ODBC Driver for Win64/sqlite3.exe"
'SQLiteデータベースのパス
Const dbPath As String = "C:/work/0260_DB01.db"
'CSVファイルのパス
Const csvFilePath As String = "C:/work/0260_test_data.csv"
'インポート先のテーブル名
Const impTbl As String = "syain"
コードの説明
以上のコードは、本マクロ実行に必要な各ファイルのパスと名前、およびインポート先のテーブル名を取得するコードです。
コードの詳細
10行目のコードは、SQLiteの実行ファイル(sqlite3.exe)のパスを取得するコードです。
私のPC環境では「sqlite3.exe」は以下に格納されているので、今回はこの「sqlite3.exe」を使います。(C:¥Program Files¥SQLite ODBC Driver for Win64配下)
なおマクロのコードでは、パスは「¥」ではなく「/」を使います。
Const exePath As String = "C:/Program Files/SQLite ODBC Driver for Win64/sqlite3.exe"
13行目のコードは、SQLiteのデータベースファイルのパスを取得するコードです。(C:\work配下)
今回は以下に格納されたSQLiteのデータベースファイルを使います。
なおマクロのコードでは、パスは「¥」ではなく「/」を使います。
Const dbPath As String = "C:/work/0260_DB01.db"
16行目のコードは、CSVファイルのパスを取得するコードです。
今回は以下に格納されたCSVファイルを使います。(C:\work配下)
なおマクロのコードでは、パスは「¥」ではなく「/」を使います。
Const csvFilePath As String = "C:/work/0260_test_data.csv"
19行目のコードは、インポート先のテーブル名を取得するコードです。
今回はテーブル「syain」にCSVファイルをインポートします。
注目すべきコード②
次に見て頂きたいのは22行目から25行目です。
'importコマンドを実行するコマンドライン文字列を作成
command = exePath & " " & dbPath & " "".mode csv"" "".separator ,"" "".import --skip 1 " & csvFilePath & " " & impTbl & """"
'コマンドを実行
Shell command, vbNormalFocus
コードの説明
以上のコードは、SQLiteのテーブルにCSVファイルのデータをインポートするコマンドを用意して実行する処理のコードです。
実際にマクロを動かしたときのコマンドのサンプルは以下の通りです。
“C:/Program Files/SQLite ODBC Driver for Win64/sqlite3.exe C:/work/0260_DB01.db “.mode csv” “.separator ,” “.import –skip 1 C:/work/0260_test_data.csv syain””
"C:/Program Files/SQLite ODBC Driver for Win64/sqlite3.exe C:/work/0260_DB01.db ".mode csv" ".separator ," ".import --skip 1 C:/work/0260_test_data.csv syain""
また、コマンドで使われている各情報は下の通りです。
- exePath：SQLiteの実行ファイル(sqlite3.exe)
- dbPath：データベースファイル
- .mode csv：インポート元のファイルがCSVファイルであることをSQLiteに認識させる
- .separator ,：CSVファイルのデータの区切り文字を指定する
- .import：CSVファイルをインポートを命令するコマンド
- –skip 1：1行目をスキップする(インポート開始は2行目から)
- csvFilePath：CSVファイル
- impTbl：CSVファイルのデータをインポートするテーブル
①exePath：SQLiteの実行ファイル(sqlite3.exe)
exePathには、SQLiteの実行ファイル(sqlite3.exe)のフルパスを格納します。
私のPC環境では「sqlite3.exe」は以下に格納されているので、今回はこの「sqlite3.exe」を使います。(C:¥Program Files¥SQLite ODBC Driver for Win64配下)
②dbPath：データベースファイル
dbPathには、SQLiteのデータベースファイルのパスを格納します。
今回は以下に格納されたSQLiteのデータベースファイルを使います。
③.mode csv
「.mode csv」は,、modeコマンドにcsvを指定し、インポート元のファイルがCSVファイルであることをSQLiteに認識させます。
今回はCSVファイルのデータをSQLiteのテーブルにインポートするので、modeコマンドにcsvを指定します。
④.separator ,
「.separator ,」は、separatorコマンドに「,」(カンマ)を指定することで、CSVファイルのデータの区切り文字が「,」(カンマ)であることを認識させます。
今回サンプルで扱うCSVファイルのデータは「,」(カンマ)区切りですが、「,」(カンマ)区切りの場合はseparatorコマンドに「,」(カンマ)を指定します。
⑤.import
「.import」は、CSVファイルをインポートを命令するコマンドです。
⑥–skip 1
「–skip 1」は、1行目をスキップするオプションです。
今回サンプルで扱うCSVファイルのデータは1行目がヘッダ部で、データ部は2行目からです。
1行目はSQLiteのテーブルに取り込んで欲しくないので(2行目から取り込んで欲しいので)、このskipのオプションを使います。
skipオプションに指定している数値は、n行スキップするという意味です。
skipオプションに1を指定すると1行目をスキップ、2なら2行目をスキップするという意味になります。
⑦csvFilePath
csvFilePathには、SQLiteのテーブルにインポートするCSVファイルのパスを格納します。
今回は以下に格納されたCSVファイルを使います。(C:\work配下)
⑧impTbl
impTblには、CSVファイルのデータをインポートするテーブル名を指定します。
今回は以下のテーブル「syain」にCSVファイルのデータをインポートします。
25行目のコードでは、Shell関数に22行目で用意したコマンド文を指定して実行します。
コマンドが正常に終了すれば、SQLiteのテーブルにCSVファイルのデータがインポートされます。
動作確認
「CSVファイルとインポート先のテーブルの例」をご覧ください。
実現できなかったこと
ExcelのマクロからSQLiteのテーブルにCSVファイルのデータをインポートするのに、一部実現できなかったことがあります。
データベースファイルの格納先のフォルダ名に日本語を使っているとSQLiteがそのパスを参照できない
データベースファイルの格納先のフォルダ名が日本語を使っているとSQLiteがそのパスを参照できないのですが、現状その解決法が見つかりませんでした。
例えばデータベースファイルの格納先が「C:¥work¥10_勉強¥10_VBA関連¥0260」の場合、日本語の部分が「???」のように認識されずパスを正確に参照してくれません。(以下の画面はコマンドラインで検証した時の画面)
なので読み込みがされずにエラーとなります。(unable to open databaseとエラー)
暫定案
暫定案は、日本語を使ったパスにデータベースファイルを置かないことです。
今回のサンプルでは、データベースファイルの置き場に「C:¥work」を選びました。
「C:¥work」なら日本語を使っていないので、SQLiteが正しく参照してくれます。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの7行目の「ADODB.Connection」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim conn As ADODB.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、ExcelのマクロからSQLiteのテーブルにCSVファイルのデータをインポートする方法についてご説明しました。
ExcelのマクロからSQLiteのテーブルにCSVファイルのデータをインポートしたいときは本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
