CDと本の管理システムをExcelで作成する(0.プロジェクトスタート)

先日こちらの記事を拝見しまして、無性に作りたくなりました

infoment.hatenablog.com

ExcelでCDと本の管理システム

例のごとく行き当たりばったりになりますが、少しずつ組み立てていきたいと思います。

管理システムを作成する理由

直接的な原因は上記のblogを拝見したことですが、他にもここ最近の出来事で遠因となることがありました。

・Mediamarkerのサービス中止

mediamarker.net

６年近くの間、購入したCD・本は全てMediamarkerに記録してきました。その記録先が無くなるということで同種のサービスであるブクログに移籍したのですが、なんとなくですが合わないなぁという気持ちを抱いていました。

所有しているCD・本をいつ購入していつ購読が終わったのか。というサービスに求めているものはMediamarkerとほぼ同じレベルで出来るのですが

・なんとなく思ったとおりに動かせない

という点がモヤモヤとしていて小さな不満になっていました。(私がまだ使い方をよくわかっていないというのもあるとは思いますが)

無いなら作ればいいじゃないか！

という事で勉強がてら勢いで作り始めることにしました。

CD・本管理システムの概要

行き当たりばったりですので方針変更は多分に考えられますが、スタートにあたっての設計内容はこんな感じで。

・データ登録、修正、削除、閲覧等の操作系：Excel、Excel VBA

・CD、本データの蓄積：SQLite３データベース

ここから先は自分の今までの勉強領域外ですが出来たら良いなぁという機能

・SQLite３のデータをGoogle SpreadSheetへ自動転記して外出先で閲覧
※機能としてあるかも知りませんが
※Excelで全部作れば外で見れるじゃないかというのは聞こえないフリ

・CDジャケット、本の書影を自動取得

妄想レベルでの初期方針を以上のようにして勉強しながら勧めていきたいと思います。

まとめ

自分が作ったからと言ってブクログ以上に自分にマッチングするわけではないですが、いい勉強のネタなので今回はどんな稚拙な内容でも完成させる所存ですので、ご興味のある方はお付き合いください。

CSVファイルを配列へ取込、データベースへ高速Insert

前回CSVファイルを全件一括で取り込もうして失敗してしまいました。

www.excellovers.com

コメントで「1行ずつ配列にいれてみては」とアドバイスを頂きましたので、実践してみました。

高速かつ手間いらず

データ量：566,401×70のCSVファイル。213MB。

処理方法	処理時間
①CSVインポート→ExcelからSQLiteへInsert	255.29秒
②CSVから配列へ転記→配列からSQLiteへInsert	計測不可
③CSVファイルから一行読み込み配列へ→配列からSQLiteへInsert、1行毎にループ処理	118.67秒

今回も盛大にネタバレをしていますが、CSVを「1行ずつ配列取込→Insert」行うと2分弱で終了！

メリットは速いだけではありません

・1行処理ごとに配列を上書きしているためメモリを大量に消費しない

・2つの処理を1つにまとめたので、1つ目の処理が終わったかなぁと意識しなくて良い。

配列を使って高速化しようとしてした失敗を挽回出来たかな。

まとめ

CSVからの取り込みに関しては使い分けの必要がなくなるので、一行毎に配列へ取込→Insertで良いんじゃなかろうか。

トライして失敗して教えていただけたことで、自分の中に新しく定跡が作れた気がします。

配列を使って高速化しようとして失敗したお話

タイトルそのままですが。

今日試して撃沈したネタをご紹介します。

セル範囲を配列へ取り込み高速化

VBAでの処理高速化ネタの一つにセルへのアクセスを極力減らすというものがあります。

そのためにセル範囲を一旦配列へ格納してから諸々の処理を行うと処理が劇的に高速化されます。

セルの配列への格納方法はVariant型変数を宣言してセル範囲の値を代入するだけです。

Dim myVar as Variant
myVar = Range("A1:B10").Value

今回２つの例を試してみました。

データ量：2,458×58

処理方法	処理時間
①Excel→SQLiteへInsert	0.71秒
②Excel→配列→SQLiteへInsert	0.69秒

①はInsert文を発行するたびにセルへのアクセスが発生します。

②は先にセル範囲を全て配列に格納して、Insert文を発行する時は配列にアクセスしています。

この例の場合は誤差範囲でしか差が出ませんでしたが、一応配列を使用したほうが速くなっています。

差を検知するにはデータ量が少なかったようです。

次は今回の本命のデータで試してみました

メモリ容量という物理的限界

データ量：566,401×70のCSVファイル。213MB。

処理方法	処理時間
①CSVインポート→ExcelからSQLiteへInsert	255.29秒
②CSVから配列へ転記→配列からSQLiteへInsert	計測不可

①は計測して初めてわかりましたが4分以上もかかっている処理です。(実は元ネタのCSVを出力するのに一番時間がかかってるけど)

これを配列を使って高速化処理したらどうなるのでしょうか。

既にネタバレしていますが)いざ実測！

…　…　…　メモリ不足で処理が止まった　…　…　…　無念　…。

会社のPCは４GBでマクロ実行前の空き容量も２GB弱あったので200MB強のファイルなら行けるかと思ったら、メモリ不足で配列に取り込む途中で止まってしまいました。

ファイル容量分だけメモリを消費するのではないんですね。

まとめ

配列に取り込むと高速化は鉄板処理ですが、何でもかんでもというわけには行かないようです。 データ容量によって使い分けが必要なようです。

データ容量が大きくなりそうだったら分割して配列への取り込みとかもありなんだろうか？

今回の例だったら56万件一度にはは無理でも、10万件ずつならいけるとか？

また折を見て試してみたいと思います。

SQLite for ExcelでIn Memory Databaseを使用する方法

以前にSQLite3データベースをExcelから使用する方法をご紹介しました。

www.excellovers.com

その時には使い方がわからなかったIn Memory DataBaseという機能の使い方がわかりましたのでご紹介します。

In Memory Databaseとは

正確な定義はググって頂くとして、私の理解では

１．メモリー上に作成される一時的なデータベース(プログラム終了後、消去される)

２．メモリー上のため高速で処理が行われる。

１の特性があるために今の所、使用用途を見い出せていないのですが、２の特性があるために普通にデータベースを作成して処理を実行するのとは比べ物にならないスピードで処理ができます。

Excel上の5,000×13のデータの条件で以下の処理を実行

データベース作成
テーブル作成
Excel上のデータをデータをデータベースへ挿入
データベースから全件抽出下データをExcelへ転記

普通にデータベースを作成：26秒
In Memory DataBase：0.6秒

このぐらいの差が出てきます。

引数で簡単指定

使い方は簡単です。

以前の記事ではデータベース作成時に

SQLite3Open FileName , dbHandle

SQLite3Openを使用しましたが、In Memory DataBaseを使用する時は

SQLite3OpenV2　FileName , dbHandle , flags , vfsName

SQLite3OpenV2を使用します。

FileName：In Memory DataBaseを使用する場合は適当なデータベース名を指定してください。

dbHandle：SQLite3Openと同じようにLong型の変数を宣言しておいてください。

flags：ここがキモです。SQLITE_OPEN_READWRITE + SQLITE_OPEN_MEMORYと指定してください。

vfsName：正直ここの意味はわかりません。空白で処理は問題なく動くようです。

記載例は以下のとおりです。

     SQLite3OpenV2 "test.db3", myDBHandle, _
          SQLITE_OPEN_READWRITE + SQLITE_OPEN_MEMORY, ""

基本的にコード実行後にデータベースは消去されますが、dbHandleをプロシージャレベル変数で宣言しておくと、明示的に削除(データベースハンドルを閉じる)するか、マクロ記載のBookを閉じるまでは消去させずに継続使用することが出来ます。

おまけ

自分では今の所、使用用途を見いだせていないのですが、実装されている以上必要があったと思うんですよね。

Excelでは無くても他の言語では必要があるんだろうか。

Split、Joinの区切り文字は１文字じゃなくても良い

先日SQLの in 句を作成するためにこのようなことをやっていたのですが

実際にはもう少し単語を繋げていたので何ともイケてないなぁと感じていたら

Join関数があるじゃないかと閃いたんですね。

その時に自分の中で思わぬ発見がありました。

区切り文字(Delimiter)は2文字以上でも大丈夫

SplitやJoinで使う区切り文字て「,」(カンマ)や「 」 (スペース)を１文字だけ指定するものと思っていませんか？

色々な書籍でも区切り文字は１文字しか指定していないと思いますし、私もそう思っていました。

ところが冒頭のSQL文を作成するために無意識のうちにこんなコードを書いてみると

Sub SQLMake()
     Range("D8").Value = "'" & Join(WorksheetFunction _
          .Transpose(Range("D2:D6").Value), "','") & "'"
End Sub

こんな風に結合出来るんですよ

この例では区切り文字として「','」の３文字を指定しているのですが、ちゃんと機能しています。

Splitも同じように２文字以上指定できます。先ほど作成した文章を今度はSplit関数で分解してみます。

Sub SplitStr()
     Range("D10:D14").Value = WorksheetFunction. _
          Transpose(Split(Range("D8").Value, "','"))
End Sub 

この通りちゃんと｢','｣の３文字で文章を分割することが出来ました(佐藤’になってるのはスルー)。

おまけ

おそらく普通に考えていたら、区切り文字は１文字という思い込みがあったため、今回のような処理を思いつくことはなかったと思います。

みんなSQLのin句にたくさん指定したい時はどうやって書いてるのかなぁ。

図形内テキストで改行できない

本当にこれも何度となく出くわすんだけど、気づかずにEnter連打する時があるという。更にはセル内改行と一緒かと思いAlt+Enterをするも何も起きないという。

戒めのための備忘録。

改行できる場合と出来ない場合

もちろん図形内テキストでも改行はできます。

この場面でEnter(テンキーで無い方)を押すと

このように改行できます。

しかしテンキーのEnterを押すと改行できません。

リンク先には2007・2010のExcelで発生すると書かれているが、手元のExcelは2013だが同じように発生します。 https://support.microsoft.com/ja-jp/help/949635

まとめ

図形内テキストの改行はEnter(テンキーで無い方)で

おまけ

Alt + Enter(テンキーの)は直前作業の繰り返し処理な気がする。

つまりCtrl + Yと動作が同じ。

なぜ？

Thisworkbook.pathってどこ？

Thisworkbook.pathってどこ？

先日このような記事を公開しました。

www.excellovers.com

この記事を作成している際に実務でも時々出くわす事態に遭遇しましたので紹介します。

アドレスは無し？

Option Explicit

Sub BookPath()
     MsgBox ThisWorkbook.Path
End Sub

この場合MsgBoxに何が表示されると思いますか？(※わかってて曖昧な問題にしてますが…)

答えはこちら

該当のBookが一度も保存されていない場合、ThisWorkbook.Pathは「""」(空白)を返します。

ちなみにThisWorkbook.Pathの代わりにActiveworkbook.pathを指定しても同じように「""」(空白)を返します。

まとめ

一度も保存されていないためにブックのアドレスが存在しないためにこのようになるのでしょうね。