So-net無料ブログ作成
検索選択

SQLiteをExcel(VBA)でODBCなしで使う [その他]

CSVで保存されたデータを集計して帳票として表示したい。
CSVファイルが大量にあったのでVBAで書くよりDBの方が便利かな~と思ったので
無料のSQLite3を使用した。

SQLiteをVBAで使うためにODBCのインストールが必要みたいだけど
インストールしたくなかったので調べたら次の情報を見つけた。

SQLite for Excel
http://sqliteforexcel.codeplex.com/
・SQLiteForExcel-0.9.zip

SQLiteのDLL版をVBAで使用するための、DLL(ラッパー)を使う。
zipを展開して次のDLLを使用する。
\SQLiteForExcel-0.9\Distribution\SQLite3_StdCall.dll


SQLiteの本体(DLL版)をダウンロードする。
http://www.sqlite.org/
・sqlite-dll-win32-x86-201405262205.zip

SQLite3_StdCall.dll と同じ場所に sqlite3.dll を置く。


Excelファイル(xls)もdllと同じ場所に置く、なのでフォルダは
次のようになる。

・Sample.xls
・SQLite3_StdCall.dll
・sqlite3.dll


ExcelのVBAを作る
\SQLiteForExcel-0.9\Source\SQLite3VBAModules\Sqlite3.bas をインポートする。

エラー処理は一切なしです。
Dim ret As Long
Dim i As Long
Dim rowCount As Long
Dim colCount As Long
Dim colType As Long
Dim sql As String
Dim dbPath As String
Dim dbHandle As Long
Dim stmtHandle As Long
Dim getDatas() As Variant

'DB、テーブルは既に作成済みとします。
dbPath = ThisWorkbook.Path & "\sample.sqlite3"

'<SELECT>
sql = "SELECT * FROM SAMPLE"

ret = SQLite3Open(dbPath, dbHandle)

ret = SQLite3PrepareV2(dbHandle, sql, stmtHandle)

ret = SQLite3Step(stmtHandle)
rowCount = 0
'RecordsetオブジェクトのGetRowsメソッドの取得と同じ
'getDatas = ***.GetRows()
Do While ret <> SQLITE_DONE
	If rowCount = 0 Then
		colCount = SQLite3ColumnCount(stmtHandle)
		ReDim getDatas(colCount - 1, rowCount)
	Else
		ReDim Preserve getDatas(colCount - 1, rowCount)
	End If
	For i = 0 To colCount - 1
		colType = SQLite3ColumnType(stmtHandle, i)
		getDatas(i, rowCount) = ColumnValue(stmtHandle, i, colType)
	Next
	ret = SQLite3Step(stmtHandle)
	rowCount = rowCount + 1
Loop

ret = SQLite3Finalize(stmtHandle)

ret = SQLite3Close(dbHandle)

'<INSERT/UPDATE>
sql = "INSERT INTO SAMPLE VALUES('1', 'ABCD', '9999')"

ret = SQLite3Open(dbPath, dbHandle)

ret = SQLite3PrepareV2(dbHandle, sql, stmtHandle)
If ret <> SQLITE_DONE Then
	Debug.Print "SQL error: " & SQLite3ErrMsg(dbHandle)
End If

ret = SQLite3Step(stmtHandle)

ret = SQLite3Finalize(stmtHandle)

ret = SQLite3Close(dbHandle)

'--------------------
'Sqlite3Demo.basより
Private Function ColumnValue(ByVal stmtHandle As Long, ByVal ZeroBasedColIndex As Long, ByVal SQLiteType As Long) As Variant
	Select Case SQLiteType
		Case SQLITE_INTEGER:
			ColumnValue = SQLite3ColumnInt32(stmtHandle, ZeroBasedColIndex)
		Case SQLITE_FLOAT:
			ColumnValue = SQLite3ColumnDouble(stmtHandle, ZeroBasedColIndex)
		Case SQLITE_TEXT:
			ColumnValue = SQLite3ColumnText(stmtHandle, ZeroBasedColIndex)
		Case SQLITE_BLOB:
			ColumnValue = SQLite3ColumnText(stmtHandle, ZeroBasedColIndex)
		Case SQLITE_NULL:
			ColumnValue = Null
	End Select
End Function

nice!(0)  コメント(4)  トラックバック(0) 

nice! 0

コメント 4

名無し

SQLite for Excelについてですが、execコマンドが無いので、つまりauto commitしかできないため、大量データのinsertが遅い(2万件で300secくらい)のですが、何かいい方法を御存知でしたら、ご教示いただけますと大変助かります。
by 名無し (2014-12-18 19:31) 

CARO

コメント付いてることがまれなので返事が遅くなりすぎてごめんなさい。
もう解決されたでしょうか?
一度、CSVファイルに出力する必要はありますが
SQLiteではコマンドラインから.importを使用すれば速くCSVファイルを取り込めます。
これについてブログを書きましたので、参考程度に見てください。

by CARO (2015-02-01 03:42) 

ももじ

プログラムから大量データをインサートする方法ですが、TRANSACTIONを使ったらどうでしょう。


SQLite3PrepareV2(dbHandle, ”BEGIN", stmtHandle)

(インサート処理など)

SQLite3PrepareV2(dbHandle, "COMMIT", stmtHandle)
~ここまで~

そうすれば、インサート中はオートコミットにならないはず。
by ももじ (2015-11-27 16:30) 

CARO

TRANSACTIONを使って28800件のレコードを取り込んでみました。
1レコードがTEXTフィールド30個のデータです。
CSVファイルの読み込み、整形の処理時間も含めての時間ですが28800件で、109~122秒でした。
ただし、CSVファイル数が24ファイルあり、ファイル毎にTRANSACTIONを行っています。
by CARO (2016-06-09 12:02) 

コメントを書く

お名前:
URL:
コメント:
画像認証:
下の画像に表示されている文字を入力してください。

トラックバック 0

この広告は前回の更新から一定期間経過したブログに表示されています。更新すると自動で解除されます。