こんにちは。ひらのです。
Excel VBA で大量の文字列をいじったり並べ替えたりする際に、
いちいちシート上で計算すると、とっても遅くなってしまいます。
そんな時に便利な一時DB⇔ADOをご紹介します。
■前提知識
- 簡単なExcel VBAコードを読めること
- 簡単な正規表現が読めること（文字列解析の解説がいらない人は不要です）
■お題（要件）
- 任意のフォーマットの文字列を解釈したい（SELECT, INSERT）
- 特定のデータを更新したい（UPDATE）
- 特定のデータを削除したい（DELETE）
長くなってしまったので、それぞれ記事を分けてご紹介します。
この記事では「任意のフォーマットの文字列を解釈したい」を解説します。
■ADOを使用するための環境
ADOを使用するには"Microsoft ActiveX Data Objects"が必要です。
Excel VBAのウィンドウ（開発ツール）メニューバー[ツール] > [参照設定]を開いて、
"Microsoft ActiveX Data Objects X.X Library"にチェックを入れてください。
（Versionは何でもいいです。）
■任意のフォーマットの文字列を解釈したい
例として下記のようなデータを想定しましょう。
[2014/10/01 7:40:58.112] RCV command=ACK ID=110405 messageId=001 data=
[2014/10/01 7:40:58.303] SND command=ACK ID=110405 messageId=002 data=
[2014/10/01 7:40:58.579] RCV command=DATA ID=110405 messageId=501 data=14B4 88B7 D54B A0D2 9A6A 3935
：
この中から取り出したいデータは下記とします。
- 日時（年～ミリ秒）
- RCV/SNDの部分
- command
- ID
- messageId
- data
上記を下記のテーブル構成としましょう。
| No | 項目 | カラム名 | 型 | サイズ |
|---|
| 1 | 日時 | timestamp | ADODB.adDouble | - |
| 2 | RCV/SND | direction | ADODB.adVarChar | 3 |
| 3 | command | command | ADODB.adVarChar | 4 |
| 4 | ID | id | ADODB.adInteger | - |
| 5 | messageId | message_id | ADODB.adVarChar | 3 |
| 6 | data | data | ADODB.adVarChar | 256 |
残念ながらExcelやADOにはミリ秒を扱える日付型は存在しないので、
日時はシリアル値で表現しようと思います。
日付型を使用する場合は ADODB.adDBTimeStamp を使用してください。
また、文字列はサイズを指定してあげる必要がありますので、サイズを決めておきましょう。
では、これをコードに落とします。
今回はDB用に標準モジュール"MyDB"をつくって、そこにコードを書きました。
Private adoRecordset As ADODB.Recordset
Public Sub OpenDb()
Set adoRecordset = New ADODB.Recordset
Call adoRecordset.Fields.Append("timestamp", ADODB.adDouble)
Call adoRecordset.Fields.Append("direction", ADODB.adVarChar, 3)
Call adoRecordset.Fields.Append("command", ADODB.adVarChar, 4)
Call adoRecordset.Fields.Append("id", ADODB.adInteger)
Call adoRecordset.Fields.Append("message_id", ADODB.adVarChar, 3)
Call adoRecordset.Fields.Append("data", ADODB.adVarChar, 256)
adoRecordset.Open
End Sub
どんなテーブル構成にするかは、DB（ADO）のOpen前に定義します。
Openがあるということは、Closeが必要ですね。
Closeは下記のように定義しました。
Public Sub CloseDb()
adoRecordset.Close
Set adoRecordset = Nothing
End Sub
次に、ファイルからデータを読み込んでADOに登録していきます。
が、今回扱うデータは時間をシリアル値に変換しなきゃいけなかったり、
区切り文字をスペースにするとdata部分がブチブチ切れそうだったり、面倒くさそうですね。
この文字列から欲しい情報を抽出する部分とADOへの登録は、後ほどご説明します。
では、まずはファイルを読み込むところから
Public Sub Execute()
' --- DBとテーブル作成 ---
Call MyDb.OpenDb
' --- ファイルを読み込んでDBにINSERT ---
Dim fileNo As Integer
Dim line As String
fileNo = FreeFile 'ファイル番号の取得
Open ActiveWorkbook.Path & "\data.txt" For Input As #fileNo 'ファイルを読込モードで開く
Do Until EOF(fileNo) 'EOFになるまでループ
Line Input #fileNo, line 'ファイルから１行読み込む
Call InsertLineToDb(line)
Loop
Close #fileNo
Call MyDb.PrintAllRecord
Call MyDb.CloseDb
End Sub
ポイントは 3,12,18行目です。
3行目でDB（ADO）を作成して、使い終わった18行目で閉じてあげます。
InsertLineToDb() は文字列を解析してADOに登録する自作関数です。
PrintAllRecord() はADOに登録した内容をイミディエイトウィンドウに出力する自作関数ですが、
後で解説します。
行単位でファイルを読み込む部分の解説は省いて、InsertLineToDb() の中身を見てみましょう。
Private Sub InsertLineToDb(ByVal str As String)
Dim id As Long
Dim timestamp As Date
Dim regExpObj, matcher, submatches As Object
Set regExpObj = CreateObject("VBScript.RegExp")
With regExpObj
.Pattern = "\[([0-9/\s:\.]+)\]\s([A-Z]{3})\scommand=([A-Z]+)\sID=([0-9]{6})\smessageId=([0-9]{3})\sdata=(.*)"
.IgnoreCase = True
.Global = True ' 文字列全体を検索
Set matcher = .Execute(str)
If 0 < matcher.Count Then
Set submatches = matcher(0).submatches ' index は 0 固定
timestamp = ToTimestamp(submatches(0))
id = CLng(submatches(3))
Call MyDb.Insert(timestamp, submatches(1), submatches(2), id, submatches(4), submatches(5))
End If
End With
Set submatches = Nothing
Set matcher = Nothing
Set regExpObj = Nothing
End Sub
□ 文字列解析
文字列解析は正規表現を使用しています。
正規表現を使用するには正規表現オブジェクト"VBScript.RegExp"を使用します。
ここでのポイントは正規表現のグループ化。
括弧()で抽出したいパターンを囲むと、囲まれた部分にマッチする文字列だけ
順番に取得することができます。
簡単な例を挙げましょう。
| 文字列 | : | ABcdExy34z |
| パターン | : | .+([a-z]+).+([0-9]+).+ |
→ Matcherのsubmatches 0番目に "cd"、1番目に "34" が入る
□ シリアル値への変換
16行目の自作関数 ToTimestamp()で読み込んだ文字列（例：2014/10/01 7:40:58.112）を
シリアル値に変換しています。
シリアル値は、1900/1/1を1として、1日経過するごとに1を増やす日時の表現方法です。
ToTimestamp() の実装はこんな感じです。
Private Function ToTimestamp(ByVal str As String) As Double
Dim ms As Double
Dim d As Date
Dim regExpObj, matcher, submatches As Object
Set regExpObj = CreateObject("VBScript.RegExp")
With regExpObj
.Pattern = "([0-9/\s:]+)\.([0-9]{3})"
.IgnoreCase = True
.Global = True ' 文字列全体を検索
Set matcher = .Execute(str)
If 0 < matcher.Count Then
Set submatches = matcher(0).submatches
d = CDate(submatches(0))
ms = CDbl(submatches(1))
ToTimestamp = d + (ms / 24 / 60 / 60 / 1000) ' ミリ秒を24時間÷60分÷60秒÷1000ミリ秒 でシリアル値に変換
End If
End With
Set submatches = Nothing
Set matcher = Nothing
Set regExpObj = Nothing
End Function
日時の文字列を正規表現で"年～秒"と"ミリ秒"部分に分けています。
文字列の長さが固定の場合はLeft$()やRight$()などを使えますが、
月や時間などの長さが不定（例：3/4, 11/12）なので、正規表現を使いました。
18行目でシリアル値で計算したいため、15行目では取得した"年～秒"をDate型に、
16行目では"ミリ秒"をDouble型に変換しています。
Date型にするとシリアル値で表現されるので、そのままシリアル値として計算できます。
□ ADOへのレコード登録（INSERT）
やっと本題のDB（ADO）へのレコード登録に来ました（長かった...）。
レコード登録は自作関数 MyDb.Insert() で下記のように組んでいます。
Public Sub Insert( _
ByVal timestamp As Double, _
ByVal direction As String, _
ByVal command As String, _
ByVal id As Long, _
ByVal messageId As String, _
ByVal data As String)
Call adoRecordset.AddNew '新しいレコードを登録
adoRecordset.Fields("timestamp") = timestamp
adoRecordset.Fields("direction") = direction
adoRecordset.Fields("command") = command
adoRecordset.Fields("id") = id
adoRecordset.Fields("message_id") = messageId
adoRecordset.Fields("data") = data
adoRecordset.Fields("key") = command & id
Call adoRecordset.Update '新しいレコードの内容を更新
End Sub
まずは新しいレコードを登録します（9行目）。
そのあと、Open時に指定してあげたカラム名を Fields() に指定してデータを入れ（11～17行目）、
レコードを更新します（19行目）。
MySQLなどのRDBMSみたいに、値を指定しつつレコード登録はできないのですね。
□ ADOレコードの参照（SELECT）
コード上でデータをDB（ADO）に登録していても、本当に登録されているか不安です。
そんな時はADOの内容をイミディエイトウィンドウに出力して、内容を確認してみましょう。
（イミディエイトウィンドウはメニューバー[表示] > [イミディエイト ウィンドウ]で表示できます。）
以下はADO内の全レコードの内容を出力するコードです。
Public Sub PrintAllRecord()
Dim i As Integer
Dim str As String
adoRecordset.MoveFirst '先頭のレコードにカレントレコードを移動
Do Until adoRecordset.EOF
str = ""
For i = 0 To adoRecordset.Fields.Count - 1
str = str & adoRecordset.Fields(i) & " "
Next i
Debug.Print str
adoRecordset.MoveNext '次のレコードにカレントレコードを移動
Loop
End Sub
ADOでは"カレントレコード"を操作して、操作対象のレコードを設定します。
ファイルポインタみたいなものですね。
ファイルの操作時と同じように、EOFになるまでカレントレコードを移動させてあげます。
カレントレコードを移動させるメソッドには、下記があります。
| MoveFirst | : | 最初のレコードに移動 |
| MoveLast | : | 最後のレコードに移動 |
| MovePrevious | : | 前のレコードに移動 |
| MoveNext | : | 次のレコードに移動 |
「任意のフォーマットの文字列を解釈したい」はここまでです。
次の記事では特定のデータを更新（UPDATE）する方法をご紹介します(^^)/~
