SQLite をVBAから検索する

2021年8月19日

前回のつづきです。SQLite3とVBAとRPAで業務自動化を考えています。VBAからデータベースファイルを操作できれば、選択肢が広がりそうです。

SQLite (エスキューライト)をWindows10にインストール

importコマンド

CSV形式のデータをファイルからテーブルへインポート可能です。テーブルが存在しない場合とすでに存在している場合で挙動が異なります。

テーブルが存在しない場合

CSVファイルの１行目を列名とみなして、新規にテーブルが作成される
列の型はすべてテキストになる

テーブルが存在する場合

１行目からデータとみなして、既存テーブルに追記される
列の型はテーブル作成時に定義済み

26万件、40MBのCSVファイルをインポートしてみた

テーブルが存在しない空のデータベースsample2.dbで試しました。

sqlite> .open sample2.db
sqlite> .separator ,
sqlite> .import AAA.csv AAA

約3秒でインポート完了！

sqlite> select count(*) from AAA;
262586

sample2.dbのファイルサイズは40MBよりやや小さかったです。

Excelのシートに検索条件を入力してVBAで抽出する

エクセルに検索対象のデータベースファイル名、検索条件を入力して、VBAからSQLで検索する処理を作成しました。

Sub readaaa()

    Dim dbFile As String
    Dim objCn As New ADODB.Connection
    
    Dim objRS As New ADODB.Recordset
    Dim strSQL As String
    
    Dim recordCount As Long
    
    'ファイルを指定
    Sheets("Sheet1").Activate
    dbFile = Cells(4, 1).Value

    'SQLiteは、user idやpasswordはいらない
    objCn.Open "DRIVER=SQLite3 ODBC Driver;Database=" & dbFile
    
    
    'SQL処理
    Dim A As String  '検索文字列
    Dim B As String
    Dim c As String
    Dim D As String
    

    '事業所取得
    A= Cells(9, 2).Value
    B= Cells(10, 2).Value
    C= Cells(11, 2).Value
    D= Cells(12, 2).Value
    
    
    strSQL = "select * from AAA where A=" & A & " and B=" & B & " and C=" & C & " and D=" & D '"エスキューエル"
    
    
    'テーブルOPEN
    objRS.Open strSQL, objCn, adOpenKeyset, adLockReadOnly
    
    recordCount = objRS.recordCount

    Cells(16, 2).Value = recordCount
    

    Sheets("AAA").Activate
    'クリア
    Range("A1", Cells(Rows.Count, Columns.Count)).Delete
    
    'ヘッダー貼り付け    
    Cells(1, 1) = "A"
    Cells(1, 2) = "B"
    Cells(1, 3) = "C"
    Cells(1, 4) = "D"
    Cells(1, 5) = "E"
    Cells(1, 6) = "F"
    Cells(1, 7) = "G"
    Cells(1, 8) = "H"
    Cells(1, 9) = "I"

    'セルＡ2にデータ一括貼り付け        
    ActiveSheet.Cells(2, 1).CopyFromRecordset objRS
    Range("A1").Select
    Range("A:I").Columns.AutoFit
    
        
    objRS.Close
    objCn.Close
    Set objCn = Nothing
End Sub

RPAで複数のシステムからCSVを取得して、SQLiteのデータベースでレコードを集計すれば、必要なデータを集計できそうです。保守性はどうなんだろう…