DB作成とテーブル作成
コマンドプロンプトで作成できますが、ちょっと手間がかかるので「DB Browser for SQLite」を用います。
まずデータベースを作成します。
作成するには、メニューのすぐ左下のタブ「新しいデータベース」をクリックします。
クリックすると、「セーブするファイル名を下から選択」フォームが開きます。選択するファイルはないので、構わず「ファイル名」を任意に決め入力します。
この例ですと、エクセルのデータを処理しますので「excel.db」としました。
「ファイル名」を入れたら保存をクリックします。
「テーブルの定義を編集」フォームが開きます。
ここでは、一番上のテーブルに作成したいテーブル名を入力します。例では、「bark」としました。
次に、フィールドを作成します。タブ「フィールド」にあるメニュー「追加」をクリックします。すると、「名前」が入力できるようになりますので適当な名前にします。
例では、フィールド名が「ID」「name」の２つフィールドを作成します。
「ID」はデータ型「INTEGER」とし、属性PKとAIにチェックを打ちます。
「name」はデータ型「TEXT」とします。
入力が終わったら、最後にOKをクリックします。
フォームが閉じると、タイトルに データベース名のC:\Sulite3\excel.dbが、テーブル一覧に作成したテーブル「bark」が表示されています。
SQLiteに接続してシートをソート
前回同様、EXCELのシートのA列に104万件のデータをセットしています。
処理は、シートのセルをダブルクリックしたら、A列をソートしてその結果をC列に表示するようにします。
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim Con As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim Endrow As Long
Dim S() As String
Dim Vals As String
Dim mySQL As String
Cancel = True
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
ReDim S(0 To Endrow - 1)
For I = 1 To Endrow
S(I - 1) = "('" & Cells(I, 1).Value & "')"
Next
Vals = Join(S, ",")
Con.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\Sqlite3\excel.db"
mySQL = "INSERT INTO bark (name) VALUES " & Vals & ";"
'SQLを発行します
Con.Execute mySQL
mySQL = "SELECT name FROM bark ORDER BY name ASC;"
'SQLを発行します
Rs.Open mySQL, Con, adOpenKeyset, adLockReadOnly
'C列に貼り付けます。
Cells(1, 3).CopyFromRecordset Rs
Con.Close
Set Con = Nothing
End Sub
エラーになりました。
いきなり100万件越えのバルクインサートは無理のようです。そこで、10万件に区切って For ループしてみます。
バルクインサートとは
テーブルに行を追加するとき、複数の行を一回のSQL文の実行で追加すること。
INSERT文のVALUES句に複数行のデータを記述します。ACCESSではできません。
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim Con As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim Endrow As Long
Dim S() As String
Dim Vals() As String
Dim mySQL As String
Dim bulkRows As Long
Dim Ps As Long
Dim I As Long
Dim Jend As Long
Dim J As Long
Dim Ind As Long
Cancel = True
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
bulkRows = 100000
Jend = bulkRows
Ps = Int((Endrow - 1) / bulkRows) + 1
ReDim S(1 To bulkRows)
ReDim Vals(0 To Ps - 1)
For I = 1 To Ps
If I = Ps Then
Jend = Endrow - (Ps - 1) * Jend
ReDim S(1 To Jend)
End If
For J = 1 To Jend
Ind = (I - 1) * bulkRows + J
S(J) = "('" & Cells(Ind, 1).Value & "')"
Next J
Vals(I - 1) = Join(S, ",")
Next I
Con.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\Sqlite3\excel.db"
For I = 0 To UBound(Vals)
mySQL = "INSERT INTO bark (name) VALUES " & Vals(I) & ";"
'SQLを発行します
Con.Execute mySQL
Next I
mySQL = "SELECT name FROM bark ORDER BY name ASC;"
'SQLを発行します
Rs.Open mySQL, Con, adOpenKeyset, adLockReadOnly
'C列に貼り付けます。
Cells(1, 3).CopyFromRecordset Rs
Con.Close
Set Con = Nothing
End Sub
15秒でエクセルのシートに書き出しが始まりましたが、書き出しが終わるまで更に1分30秒要しました。書き出しに時間が大方費やされました。
クイックソートに比べ、書き出しに時間がかかるのが欠点ですが、良いことはSQL文で必要に応じてデータが作成できるので応用範囲が広いということです。
引き続き、あいまい検索を行ってみました。条件は先頭3文字が「075」で始まる文字です。
そうすると、SQL文のWHERE句に記述する演算は、ACCESS の場合「LIKE ‘075＊’」ですが、SQLiteでは「LIKE ‘075%’」です。
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim Con As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim mySQL As String
Cancel = True
Con.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\Sqlite3\excel.db"
mySQL = "SELECT name FROM bark"
mySQL = mySQL & " WHERE name LIKE '075%'"
mySQL = mySQL & " ORDER BY name ASC;"
'SQLを発行します
Rs.Open mySQL, Con, adOpenKeyset, adLockReadOnly
'C列に貼り付けます。
Cells(1, 3).CopyFromRecordset Rs
Con.Close
Set Con = Nothing
End Sub
予想外に早いです。ダブルクリックとほぼ同時に表示されました。
全部で8,000件ほど書き出しています。これは、先ほどの100万件のおおよそ100分の1の書き出し量で、時間は比例していると考えた場合、時間の計算が合います。
書き出しを修正
冒頭の104万件のデータのソートのコードの書き出し部分を配列に書き直しました。
これによりかなりの改善が見られ、トータル100秒以上要していた時間が、3分の1以下の30秒までに縮まりました。最速を更新しました。
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim Con As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim Endrow As Long
Dim S() As String
Dim Vals() As String
Dim mySQL As String
Dim bulkRows As Long
Dim Ps As Long
Dim I As Long
Dim Jend As Long
Dim J As Long
Dim Ind As Long
Cancel = True
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
bulkRows = 100000
Jend = bulkRows
Ps = Int((Endrow - 1) / bulkRows) + 1
ReDim S(1 To bulkRows)
ReDim Vals(0 To Ps - 1)
For I = 1 To Ps
If I = Ps Then
Jend = Endrow - (Ps - 1) * Jend
ReDim S(1 To Jend)
End If
For J = 1 To Jend
Ind = (I - 1) * bulkRows + J
S(J) = "('" & Cells(Ind, 1).Value & "')"
Next J
Vals(I - 1) = Join(S, ",")
Next I
Con.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\Sqlite3\excel.db"
mySQL = "DELETE FROM bark;"
'SQLを発行します
Con.Execute mySQL
For I = 0 To UBound(Vals)
mySQL = "INSERT INTO bark (name) VALUES " & Vals(I) & ";"
'SQLを発行します
Con.Execute mySQL
Next I
mySQL = "SELECT name FROM bark ORDER BY name ASC;"
'SQLを発行します
Rs.Open mySQL, Con, adOpenKeyset, adLockReadOnly
'C列に貼り付けます。
Erase S
I = 0
ReDim S(1 To Endrow, 1 To 1)
Do Until Rs.EOF
I = I + 1
S(I, 1) = Rs("name")
Rs.MoveNext
Loop
Range(Cells(1, 3), Cells(Endrow, 3)).Value = S
Con.Close
Set Con = Nothing
End Sub
DETETE テーブルデータの削除
テーブルの全データ削除のSQL文は、ACCESS の表現「DETETE ＊ FROM bark；」で実行するとエラーになります。
SQLiteは、「DELETE FROM bark;」として実行します。
これで、テーブル「bark」の全データが削除できました。