Excelでビッグデータを乗りこなせ（8．テーブルの正規化のVBAコード）

前回はSQLiteで単一のテーブルで管理していたデータを、データベースの正規化を行い複数のテーブルに分割してみた。

www.excellovers.com

今回は実際に単一のテーブルを複数のテーブルに分割するコードを紹介したいと思います。

例のごとく早速コード

Option Explicit
Const CSVFullpath As String = "C:\Users\...\家計簿データ.csv"
Const DBFullPath As String = "C:\Users\...\家計簿.db3"
Dim SQLiteDB_Handle As Long
Dim myStmtHandle As Long
'---------------------------------------
Sub 家計簿データベース初期設定()
     SQLiteOpen
     SQLiteDatabase_Make
     CSV_Data_Insert
     SQLiteOpen
     口座Master_Insert
     お店Master_Insert
     項目Master_Insert
     内訳Master_Insert
     仕訳_Insert
     仕訳inbox_Delete
     SQLiteDatabase_ViewMake
     SQLiteDatabase_最適化
     SQLite3Close SQLiteDB_Handle
     MsgBox "データベース作成完了"
End Sub
'---------------------------------------
Sub SQLiteOpen()
'DLLへ接続
     SQLite3Initialize
     
'データベース作成
     SQLite3Open DBFullPath, SQLiteDB_Handle
End Sub
'---------------------------------------
Sub SQLiteDatabase_Make()

'①CSV受け入れ用テーブル作成
     SQLite3PrepareV2 SQLiteDB_Handle, _
         "CREATE TABLE 仕訳inbox(" & _
         "日付 TEXT," & _
         "項目 TEXT," & _
         "内訳 TEXT," & _
         "品名 TEXT," & _
         "お店 TEXT," & _
         "収入 INTEGER," & _
         "支出 INTEGER," & _
         "口座 TEXT," & _
         "メモ TEXT)", _
         myStmtHandle
         
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle

     SQLite3PrepareV2 SQLiteDB_Handle, _
         "CREATE TABLE 仕訳(" & _
         "仕訳ID INTEGER PRIMARY KEY," & _
         "日付 TEXT," & _
         "内訳ID Integer," & _
         "品名 TEXT," & _
         "お店ID Integer," & _
         "収入 INTEGER," & _
         "支出 INTEGER," & _
         "口座ID Integer," & _
         "メモ TEXT)", _
         myStmtHandle
         
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
     
'②インデックス作成
     SQLite3PrepareV2 SQLiteDB_Handle, _
          "Create Index 日付index On 仕訳(日付)", _
          myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
          
     SQLite3PrepareV2 SQLiteDB_Handle, _
          "Create Index 項目index On 仕訳(項目)", _
          myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
     
'内訳マスターテーブル作成
     SQLite3PrepareV2 SQLiteDB_Handle, _
         "CREATE TABLE 内訳マスター(" & _
         "項目ID INTEGER," & _
         "内訳ID INTEGER PRIMARY KEY," & _
         "内訳 TEXT)", _
         myStmtHandle
         
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
     
     SQLite3PrepareV2 SQLiteDB_Handle, _
          "Create Index 項目index On 項目マスター(項目ID)", _
          myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle

     
'項目マスターテーブル作成
     SQLite3PrepareV2 SQLiteDB_Handle, _
         "CREATE TABLE 項目マスター(" & _
         "項目ID INTEGER PRIMARY KEY," & _
         "項目 TEXT)", _
         myStmtHandle
         
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
     SQLite3PrepareV2 SQLiteDB_Handle, _
          "Create Index 項目index On 項目マスター(項目ID)", _
          myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle

     
'口座マスターテーブル作成
     SQLite3PrepareV2 SQLiteDB_Handle, _
         "CREATE TABLE 口座マスター(" & _
         "口座ID INTEGER PRIMARY KEY," & _
         "口座 TEXT)", _
         myStmtHandle
         
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
     
     SQLite3PrepareV2 SQLiteDB_Handle, _
          "Create Index 口座index On 口座マスター(口座ID)", _
          myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
          
'お店マスターテーブル作成
     SQLite3PrepareV2 SQLiteDB_Handle, _
         "CREATE TABLE お店マスター(" & _
         "お店ID INTEGER PRIMARY KEY," & _
         "お店 TEXT)", _
         myStmtHandle
         
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
     
     SQLite3PrepareV2 SQLiteDB_Handle, _
          "Create Index お店index On お店マスター(お店ID)", _
          myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
'データベース接続終了
     SQLite3Close SQLiteDB_Handle
     
End Sub
'---------------------------------------
Sub CSV_Data_Insert()
     Dim myCon As ADODB.Connection
     Dim myRS As ADODB.Recordset
     Dim myTranHandle As Long
     Dim mySQL As String
     
'CSVファイルの取込
     Set myCon = New ADODB.Connection
     myCon.Provider = "Microsoft.JET.OLEDB.4.0"
     myCon.Properties("Extended Properties") = "text;"
     Dim Fullpath As String
     Fullpath = Left(CSVFullpath, InStrRev(CSVFullpath, "\"))
     myCon.Open Fullpath
     
     Set myRS = New ADODB.Recordset
     mySQL = "select 日付,項目,内訳,品名,お店,収入,支出,口座,メモ from [家計簿データ.csv]"
     myRS.Open mySQL, myCon, adOpenStatic
     myRS.MoveFirst
     
'SQLiteへのデータ取込------------------------------------------------------------------------
     SQLite3OpenV2 DBFullPath, SQLiteDB_Handle, SQLITE_OPEN_READWRITE, ""
     
     SQLite3PrepareV2 SQLiteDB_Handle, "BEGIN TRANSACTION", myTranHandle
     SQLite3Step myTranHandle
     SQLite3Finalize myTranHandle
     Dim i As Long
     i = 1
     Dim Ans As Variant
     Do Until myRS.EOF
          mySQL = "Insert Into 仕訳inbox"
          mySQL = mySQL & "(日付,項目,内訳,品名,お店,収入,支出,口座,メモ)  Values( "
          mySQL = mySQL & "'" & SQLiteDateFormat(myRS("日付")) & "',"
          mySQL = mySQL & "'" & myRS("項目") & "',"
          mySQL = mySQL & "'" & myRS("内訳") & "',"
          mySQL = mySQL & "'" & SingleQuotationEscape(myRS("品名")) & "',"
          mySQL = mySQL & "'" & SingleQuotationEscape(myRS("お店")) & "',"
          mySQL = mySQL & "'" & myRS("収入") & "',"
          mySQL = mySQL & "'" & myRS("支出") & "',"
          mySQL = mySQL & "'" & myRS("口座") & "',"
          mySQL = mySQL & "'" & myRS("メモ") & "')"
          Ans = SQLite3PrepareV2(SQLiteDB_Handle, mySQL, myStmtHandle)
          SQLite3Step myStmtHandle
          SQLite3Finalize myStmtHandle
          
          If Ans = 1 Then Debug.Print i
          i = i + 1
          myRS.MoveNext
     Loop
     Set myRS = Nothing
     Set myCon = Nothing
     SQLite3PrepareV2 SQLiteDB_Handle, "COMMIT TRANSACTION", myTranHandle
     SQLite3Step myTranHandle
     SQLite3Finalize myTranHandle
     SQLite3Close SQLiteDB_Handle
End Sub
'---------------------------------------
Sub 口座Master_Insert()
     
'データベース抽出
     Dim mySQL As String
     mySQL = "Insert Into 口座マスター(口座) select distinct 口座 from 仕訳inbox"
     SQLite3PrepareV2 SQLiteDB_Handle, mySQL, myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
End Sub
'---------------------------------------                    
Sub お店Master_Insert()

'データベース抽出
     Dim mySQL As String
     mySQL = "Insert Into お店マスター(お店) select distinct お店 from 仕訳inbox"
     SQLite3PrepareV2 SQLiteDB_Handle, mySQL, myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
End Sub
'---------------------------------------                    
Sub 項目Master_Insert()

'データベース抽出
     Dim mySQL As String
     mySQL = "Insert Into 項目マスター(項目) select distinct 項目 from 仕訳inbox"
     SQLite3PrepareV2 SQLiteDB_Handle, mySQL, myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
End Sub
'---------------------------------------
Sub 内訳Master_Insert()

'データベース抽出
     Dim mySQL As String
     mySQL = "Insert Into 内訳マスター(項目ID,内訳) select distinct 項目ID,内訳 from 仕訳inbox join 項目マスター on 仕訳inbox.項目 = 項目マスター.項目"
     SQLite3PrepareV2 SQLiteDB_Handle, mySQL, myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
End Sub
'---------------------------------------
Sub 仕訳_Insert()
     
'データベース抽出
     Dim mySQL As String
     mySQL = "Insert Into 仕訳(日付,内訳ID,品名,お店ID,収入,支出,口座ID,メモ) select 日付,項目内訳.内訳ID,品名,お店マスター.お店ID,収入,支出,口座マスター.口座ID,メモ from 仕訳inbox"
     mySQL = mySQL & " join お店マスター on 仕訳inbox.お店 = お店マスター.お店 join 口座マスター on 仕訳inbox.口座 = 口座マスター.口座"
     mySQL = mySQL & " join (select 項目マスター.項目,内訳マスター.内訳ID,内訳マスター.内訳 from 内訳マスター join 項目マスター on 内訳マスター.項目ID = 項目マスター.項目ID) as 項目内訳"
     mySQL = mySQL & " on 仕訳inbox.項目 = 項目内訳.項目 and 仕訳inbox.内訳 = 項目内訳.内訳"
     
     SQLite3PrepareV2 SQLiteDB_Handle, mySQL, myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
End Sub
'---------------------------------------                    
Sub 仕訳inbox_Delete()

'データベース抽出
     Dim mySQL As String
     mySQL = "Delete from 仕訳inbox"
     SQLite3PrepareV2 SQLiteDB_Handle, mySQL, myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
End Sub
'---------------------------------------                    
Sub SQLiteDatabase_最適化()
     
'データベース最適化
     SQLite3PrepareV2 SQLiteDB_Handle, _
         "vacuum", _
         myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
End Sub
'---------------------------------------
Sub SQLiteDatabase_ViewMake()

'うきうき家計簿集計用ビュー
     Dim mySQL As String
     Dim i As Long
     i = 1
     If i = 1 Then
          mySQL = "Create View うきうき家計簿_月別 as select 年月,項目,内訳,口座,sum(収入) as 総収入,sum(支出) as 総支出 from"
          mySQL = mySQL & " (select strftime('%Y-%m', 日付) as 年月,項目内訳.項目,項目内訳.内訳,収入,支出,口座マスター.口座 from 仕訳"
          mySQL = mySQL & " join 口座マスター on 仕訳.口座ID = 口座マスター.口座ID"
          mySQL = mySQL & " join (select 項目マスター.項目ID,項目マスター.項目,内訳マスター.内訳ID,内訳マスター.内訳 from 内訳マスター join 項目マスター on 内訳マスター.項目ID = 項目マスター.項目ID) as 項目内訳"
          mySQL = mySQL & " on 仕訳.内訳ID = 項目内訳.内訳ID) group by 年月,項目,内訳,口座"
     Else
          mySQL = "Drop View うきうき家計簿_月別"
     End If
     SQLite3PrepareV2 SQLiteDB_Handle, _
         mySQL, _
         myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
     
'手作業集計用ビュー
     i = 1
     If i = 1 Then
          mySQL = "Create View うきうき家計簿 as select 日付,項目,内訳,品名,口座,収入,支出 from"
          mySQL = mySQL & " (select 日付,項目内訳.項目,項目内訳.内訳,品名,収入,支出,口座マスター.口座 from 仕訳"
          mySQL = mySQL & " join 口座マスター on 仕訳.口座ID = 口座マスター.口座ID"
          mySQL = mySQL & " join (select 項目マスター.項目ID,項目マスター.項目,内訳マスター.内訳ID,内訳マスター.内訳 from 内訳マスター join 項目マスター on 内訳マスター.項目ID = 項目マスター.項目ID) as 項目内訳"
          mySQL = mySQL & " on 仕訳.内訳ID = 項目内訳.内訳ID)"
     Else
          mySQL = "Drop View うきうき家計簿"
     End If
     SQLite3PrepareV2 SQLiteDB_Handle, _
         mySQL, _
         myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
End Sub
'---------------------------------------                                  
Function SQLiteDateFormat(Datestr As String) As String
     Dim Ans As String
     Ans = Format(Datestr, "yyyy-mm-dd")
     SQLiteDateFormat = Ans
End Function
'---------------------------------------                    
Function SingleQuotationEscape(myStr As Variant) As Variant
     Dim Ans As Variant
     If IsNull(myStr) Then
          Ans = myStr
     Else
          Ans = Replace(myStr, "'", "''")
     End If
     SingleQuotationEscape = Ans
End Function

いっぺんに記載するのはどうなのという量のコードですが、前回の処理を全部記載するとこのような感じになります。

処理の流れ

プロシージャ名	処理
SQLiteOpen	・SQLite3.dll、SQLite3_StdCall.dllへの接続 ・データベースファイルの作成、オープン
SQLiteDatabase_Make	データベース内に各種テーブルの作成
CSV_Data_Insert	CSVファイルのデータを一時保管テーブルにインサート
口座Master_Insert	一時保管テーブルから口座一覧を作成して専用テーブルへインサート
お店Master_Insert	一時保管テーブルからお店一覧を作成して専用テーブルへインサート
項目Master_Insert	一時保管テーブルから項目一覧を作成して専用テーブルへインサート
内訳Master_Insert	一時保管テーブルから内訳一覧を作成して専用テーブルへインサート
仕訳_Insert	一時保管テーブルから連結用列の作成・不要列削除を行い、専用テーブルへインサート
仕訳inbox_Delete	一時保管テーブルのデータを削除
SQLiteDatabase_最適化	データベースの最適化
SQLiteDatabase_ViewMake	各種テーブルを連結して閲覧用のビューを作成する
SQLite3Close	データベースを閉じる

まず大本のコンセプトとして家計簿データをデータベースに差分追加ではなく、毎回データベースをゼロから作り直して、家計簿データを全て取り込み直すという流れになっています。

なぜ差分追加ではなく１から作り直すのか？

・SQLiteはUpdate・Deleteが遅い、とどこかで見かけた

・差分でUpdateする方法がよくわからない

要は単に知識不足です。

ただ毎回ゼロからデータベースを作ってゼロからデータを取り込み直しても、現状では２秒もかからないぐらいですので十分許容範囲です。

差分でのアップロードに関しては仕事で使うには必要なスキルになるので追々勉強していこうと思います。

まとめ

今回は長くなりすぎたのでここまで。

次回はコードの中身をもう少し見ていきます。