前回で使用するデータベースをSQLiteにすることを決定した。
今回からCSVファイルの取り込み方法について試行錯誤していきたいと思う。
現状の取り込み方法
・Querytables.addでCSVをExcelに取り込む
・取り込んだデータをSQLのInsert分でSQLiteへ取り込み
552,024行×216列のCSVを48列に減らしつつExcelへの取り込みで219秒。
552,024行×48列のExcelデータをSQLiteへ取り込むので79秒。
上記は空っぽのデータベースへの処理なので、データが蓄積されてくるとひょっとしたら処理スピードが遅くなるかもしれません。
(※このあたりデータベースのことがよくわかっていません
取り込み頻度としては1日1回を想定しています。
そのため1回辺りの処理時間が短くなるような処理方法が無いか模索しています。
改善案
twitterでつぶやいたところ、何点か改善案をご提案頂きました。
① SQLiteに用意されているコマンドでCSVを直接SQLiteに直接取り込む
② CSVデータをExcelに転記せず配列に取り込んで、配列から直接SQLiteへ取り込み
③ ADODBでCSVをRecordset取得後、SQLiteへRecordsetから直接取り込む
いずれの方法にしても現状のExcelのセルへの保存を回避することで実行速度を上げる方向です。
SQLiteコマンド
自分が調べた感じではコマンドプロンプトの画面でSQLiteに対してコマンドを入力するみたいですが、今回はソフトのインストールご法度なのでパス
※ これは後で気づいたのですが、SQLite3.exeを任意のフォルダに保存するだけでコマンドを使用することが出来ます。
配列→SQLite
こちらは取り込んだCSVデータを配列に展開して、そこからSQLiteへ取り込んでいきます。
結論から先にいうとこの方法は会社のPCでは処理を完了することが出来ませんでした。
今回の対象データは会計ソフトから吐き出した段階で1ヶ月分55万行×216列のビッグデータ。
これだけの量の配列を確保しようとして会社のPCはメモリー不足のため処理が止まってしまいました。
ただこれは今回の例のデータが大きすぎるために起こったことなので、数千~数万件レベルであればこの方法で十分に対応可能です。
ADODB.Recoredset→SQLite
ADODBでCSVファイルをRecordsetとして取得後、Recrodsetから1行ずつSQLiteへInsertしていきます。
これも配列方式と同じでメモリー不足になるんじゃいかと思ったのですが、twitterで教えて頂いた方曰く、あくまでCSVファイルをDBとして見るようになるだけで、メモリーに全部読み込まれるわけではない、と。
結果としてこの方法ではメモリー不足になることなくCSVファイルをSQLiteに取り込むことが出来ました。
処理速度アップ!
従来の方法ではSQLiteへの取り込みまで298秒かかっていましたが、新しいADODBで取り込む方法は
125秒
で終了しました。
元の処理の42%の時間で完了しました。
内容についても件数や、科目・部署ごとの金額も正しく登録されていました。
まとめ
Excelでビッグデータを乗りこなすための第一歩を無事に踏み出すことが出来ました。
次回以降は今回のADODB.Recordsetのコード紹介や、そもそもデータベース自体のお作法てどうしたら良いのか調査をしていきたいと思います。