CDと本の管理システムをExcelで作成する(3.SQLite3データベースへのデータ挿入)

前回はCD・本データを格納しておく、SQLite３データベースを作成しました。

www.excellovers.com

今回は作成したデータベースへMediaMarkerからダウンローしたデータを挿入してみます。

下記のコードを標準モジュールに記載してください。

Sub CDBookManger_CSVImport()
     Dim n As Long
     Dim myVar As Variant
     Dim DBFullpath As String
     DBFullpath = ThisWorkbook.Path & "\CDBookManager.db3"
     
     '①
     n = SQLiteCSVOpen(ThisWorkbook.Path & "\MediaMarkerExport.csv", False)
     
     '②
     myVar = SQLiteCSVLineData(n, ",")
     
     Dim DBHandle As Long
     SQLite3Initialize
     SQLite3OpenV2 DBFullpath, DBHandle, SQLITE_OPEN_READWRITE, ""
     
     Dim mySQL As String
     Dim StmtHandle As Long
     
     '③
     Do Until UBound(myVar) = -1
         '④
          mySQL = "Insert into CDBookManager values("
          mySQL = mySQL & """" & myVar(0) & ""","
          mySQL = mySQL & """" & myVar(4) & ""","
          mySQL = mySQL & """" & myVar(7) & ""","
          mySQL = mySQL & """" & myVar(6) & ""","
          mySQL = mySQL & """" & myVar(21) & ""","
          mySQL = mySQL & """" & myVar(23) & ""","
          mySQL = mySQL & myVar(17) & ","
          mySQL = mySQL & """" & myVar(16) & ""","
          mySQL = mySQL & """""" & ")"
          
          SQLite3PrepareV2 DBHandle, mySQL, StmtHandle
          SQLite3Step StmtHandle
          SQLite3Finalize StmtHandle
          
          myVar = SQLiteCSVLineData(n, ",")
     Loop
     SQLite3Close DBHandle
End Sub
'--------------------------------------------------------------
Function SQLiteCSVOpen(CSVFullPath As String, _
                    HeaderInport As Boolean) As Long
     Dim n As Long
     n = FreeFile
     Open CSVFullPath For Input As #n
     
     Dim myArray As Variant
     If Not (HeaderInport) Then Line Input #n, myArray
     
     SQLiteCSVOpen = n
End Function
'--------------------------------------------------------------
Function SQLiteCSVLineData(FreeFileNumber As Long, _
     Optional Separate As String = ",") As Variant
     
     Dim CSVLineData As String
     Dim ReturnValue As Variant
     
     If EOF(FreeFileNumber) Then
          '⑤
          ReturnValue = Array()
          Close #FreeFileNumber
     Else
          Line Input #FreeFileNumber, CSVLineData
          
          Select Case Separate
               Case Is = ""
                    ReturnValue = CSVLineData
               Case Else
                    '⑥
                    ReturnValue = FP_MAKE_ARRAY_From_CSV(CSVLineData)
               End Select
     End If
     SQLiteCSVLineData = ReturnValue
End Function

①②はセットで使用するユーザー定義関数です。

①第１引数で指定したCSVファイルを開いています。第２引数でヘッダーの取込有無を指定します。

②はループ処理の直前とループ内の最終行で使用します。第一引数で①の関数から引き継いだファイルナンバーを指定して CSVファイルから一行分のデータを読み込み、第二引数で指定した文字で分割したデータを配列に取り込み、返り値としています。最後にCSVの読み込み位置を次の行に移動して処理終了です。

CSVファイルから一行分配列に取り込み→データベースへ挿入を実現するために作成したけど、なんかしっくり来てない。要検証。

③　②の関数は返す要素がない場合、空配列(要素数が－１)を返すのでそれを終了条件としている。

④データベースへデータを挿入するSQL文は以下のとおりです。

Insert into テーブル名 values(挿入データ)

上記はテーブルの全ての列にデータを挿入する際の構文です。実際のテーブルの列の並びと同じように挿入データを記載します

今回の取込データは全てText型ですので挿入データを『’』(シングルクォーテーション)で囲ってやるのが普通なのですが、挿入データの中に『'』が含まれていると正常に判定してくれませんので、『"』(ダブルクォーテーション)で囲っています。

配列に取り込んだMediaMarkerからダウンロードしたCSVデータのうち必要な項目を選択してSQL文に指定しています。

⑤今まで配列を返すユーザー定義関数で、該当する配列が無い時は適当な文字列を返したりしていたのですが、以下の記事で『空配列』というものを知ったので使用しています。

配列を返す時は、返り値に対してUbound関数を使うことが多々ありますが、適当な文字列を返してしまうと、別の判定処理が必要になっていました。

詳細は以下の記事を参考。

infoment.hatenablog.com

⑥CSVをカンマで区切って配列に取り込む際に、挿入データの中に『,』(カンマ)が含まれていると、思わぬ場所でデータが区切られてしまう場合があります。

FP_MAKE_ARRAY_From_CSV関数はカンマとカンマの間の挿入データに含まれるカンマを無視して、正しく区切ったデータを返してくれる関数です。

この関数はこちらのサイトを参考にさせていただきました。

VBA応用(CSV形式テキストデータの読み込み:カンマ数不定版)

まとめ

CD・本データをSQLite３データベースに取り込んだことで、一旦元となるデータベースが作成できました。

次回は新規登録用のフォーム作成を行ってみたいと思います。

Excelが大好きだ！

Excel大好き経理マンがExcelの事を書き綴っていきます。

CDと本の管理システムをExcelで作成する(3.SQLite3データベースへのデータ挿入)

まとめ