Excelが大好きだ!スマートフォン用の表示で見るExcelが大好きだ!Excel大好き経理マンがExcelの事を書き綴っていきます。VBA前回で使用するデータベースをSQLiteにすることを決定した。www.excellovers.com今回から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でつぶやいたところ、何点か改善案をご提案頂きました。ADODBでCSVをRecordset取得後、SQLiteへRecordsetから直接取り込むいずれの方法にしても現状のExcelのセルへの保存を回避することで実行速度を上げる方向です。これは後で気づいたのですが、SQLite3.exeを任意のフォルダに保存するだけでコマンドを使用することが出来ます。配列→SQLiteこちらは取り込んだCSVデータを配列に展開して、そこからSQLiteへ取り込んでいきます。結論から先にいうとこの方法は会社のPCでは処理を完了することが出来ませんでした。今回の対象データは会計ソフトから吐き出した段階で1ヶ月分55万行×216列のビッグデータ。これだけの量の配列を確保しようとして会社のPCはメモリー不足のため処理が止まってしまいました。これは今回の例のデータが大きすぎるために起こったことなので、数千~数万件レベルであればこの方法で十分に対応可能です。ADODB.Recoredset→SQLiteADODBでCSVファイルをRecordsetとして取得後、Recrodsetから1行ずつSQLiteへInsertしていきます。これも配列方式と同じでメモリー不足になるんじゃいかと思ったのですが、twitterで教えて頂いた方曰く、あくまでCSVファイルをDBとして見るようになるだけで、メモリーに全部読み込まれるわけではない、と。結果としてこの方法ではメモリー不足になることなくCSVファイルをSQLiteに取り込むことが出来ました。処理速度アップ!従来の方法ではSQLiteへの取り込みまで298秒かかっていましたが、新しいADODBで取り込む方法は125秒で終了しました。元の処理の42%の時間で完了しました。内容についても件数や、科目・部署ごとの金額も正しく登録されていました。まとめExcelでビッグデータを乗りこなすための第一歩を無事に踏み出すことが出来ました。次回以降は今回のADODB.Recordsetのコード紹介や、そもそもデータベース自体のお作法てどうしたら良いのか調査をしていきたいと思います。Tumblr関連記事2019-08-04Excelでビッグデータを乗りこなせ(8.テーブルの正規化のVBAコード)前回はSQLiteで単一のテーブルで管理していたデータを、データ...2019-07-20Excelでビッグデータを乗りこなせ(7.テーブルの正規化)前回はExcelとは異なるデータベースの独自要素を盛り込みつつSQ...2019-06-09Excelでビッグデータを乗りこなせ(5.データベースの独自要素)ADODBを利用してCSVファイルを操作する方法を検証した。(1)月別アーカイブはてなブログをはじめよう!ExcelLoverさんは、はてなブログを使っています。ブログを報告するスターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。