Use a disconnected recordset to sort large data sets in VBScript

[アーティクル]
08/25/2016

Hey, Scripting Guy!トースターと常時連絡を取れるようにする

Microsoft Scripting Guys

コードのダウンロード : HeyScriptingGuy2008_09.exe(150 KB)

この私たちの現代世界を簡潔にまとめるとしたら、「常時接続」という表現が適切でしょう。携帯電話のおかげで、家にいなくても電話を受けることができます。他の人は、時間と場所を問わず、あなたに電話をかけることができます (へぇ、それはすばらしい…ですね)。また、ワイヤレスでコンピュータが使用できるおかげで、会社にいなくても仕事ができるようになりました。自宅や海辺など、思いつく限りのすべての場所から仕事ができます。

これは実際にあった話ですが、編集者の両親が最近キャンプに行ったとき、キャンプ場のワイヤレスネットワークに接続できなかったため、偉大な探検家ルイスとクラークのように不便な生活を強いられたそうです。しかし、さいわいにも、衛星テレビは見ることができました。

ただし、話はここからです。GPS 機器は、ほとんど誤差なく所在地を示すことができます。また、あなたの所在地を他の人に知らせることができるものもあります (今日ほど「逃げても無駄だぞ」という慣用句が的を射ている時代はないでしょう)。このコラムを執筆している Scripting Guy が望めば、小切手が決済されるたびに当座預金口座から電話で知らせが来るようにしたり、車に毎月のステータスレポートを送らせることだってできます。まるでそれだけでは不十分だと言わんばかりに、トースターは、彼が休暇で不在になるときには、いつでも犬の散歩と植木の水やりを代行すると申し出てきました。

そうですね。確かに最後のトースターの件に関しては、まだ実現された話ではありません。でも、Scripting Guy が望めば、インターネットに対応したトースターを買うことはできます。帰宅途中にトースターに電話して、家のドアを開けたときには、焼きたてのトーストができあがっているようにできます。ですが、正直なところ、ドアを開けたときに焼きたてのトーストが欲しい理由は理解できません。でも、あなたが理解できるのであれば、どうぞご利用ください。

もちろん、世間が常時接続した状態を保つことにやっきになっているのであれば、流行にとらわれたことのない Scripting Guys が、非常時接続を推奨するのは無理もないことでしょう。それはつまり、Scripting Guys が携帯電話とラップトップコンピュータを破棄するように推奨している、ということになるのか、ですって。いいえ、そうではありません。いくら Scripting Guys だってそのようなことはしません。彼らが主張しているのは、切断されたレコードセットをスクリプトのコレクションに加えることです。でも、携帯電話やラップトップコンピュータを破棄したい方については引き止めたりすることはありませんので、ご自由にどうぞ。

注 Harris Interactive 社の調査によると、アメリカ人の 43% が仕事に関係したメールをチェックするために休暇中にラップトップコンピュータを使ったことがあるそうです。また、50% 以上の人が電子メールやボイスメールをチェックするために休暇中に携帯電話を使ったことがあると答えています。そして、この結果には、1 年の間に休暇を取らない 40% のアメリカ人は含まれていません。

多くの人が、切断されたレコードセットを喜んでスクリプトのコレクションに加えるでしょう。「切断されたレコードセットが何であるかわからないことを除いて」という但し書き付きになりますが。切断されたレコードセットという概念に詳しくない方のために説明すると、これは、いわば実際のデータベースに接続されていないデータベーステーブルのようなものです。これはスクリプトにより作成されており、メモリ内にのみ存在し、スクリプトが終了するときに消滅します。つまり、切断されたレコードセットは、数分間だけ存在する架空のデータ構造で、データと共に消滅します。「なるほど、それは本当に便利そうですね Scripting Guys さん。教えてくれてどうもありがとう。」という声が聞こえてきそうですね。

正直に言いますと、切断されたレコードセットは、それほどワクワクするものではなさそうです。実際のところ、ワクワクするものではありませんが、とても役に立つことがあります。ベテランの VBScript スクリプト作成者はご存じだと思いますが、VBScript のデータ分類機能は世界一優れたものというわけではありません (データ分類機能がないことが世界最良のデータ分類機能であると思っている場合は別ですが)。同様に、ひいき目に見ても VBScript の大きなデータセットを処理する能力も限られています。Dictionary オブジェクト (最大 2 つのプロパティを持つアイテムの作業に制限されます) または配列 (大部分が単一プロパティのデータ一覧に制限されます) の以外は、そんなところです。

切断されたレコードセットは、この両方の問題に対処できます (そしてその他の問題にも対処できます)。データ (特に複数プロパティを持つデータ) を分類する必要がある場合は、お任せください。先に述べたように、切断されたレコードセットは実質上データベーステーブルと同じようなものなので、データベーステーブルを分類するのは簡単な作業です (そうですね、厳密に言うなら、データベーステーブルを分類しないことの方が、データベーステーブルを分類するよりも簡単な作業でしょう)。複数プロパティを持つ多数のアイテムを整理する必要がある場合も、お任せください。切断されたレコードセットはデータベーステーブルと実質上同じようなものであることはお知らせしましたよね。なんらかの方法で情報をフィルタ処理したり、データに特定の値が含まれているかどうかを検索したりする必要がある場合は、データベーステーブルと実質的に同じものを使える方法があれば万事解決です。

おっしゃるとおり、具体例をお見せしましょう (私たちが話していることを自分たちで理解していることを前提としていますが)。まず始めに、図 1 に示す野球の統計データがあるとします。この統計は、MLB.com の Web サイトから取得したもので、タブ区切りファイル C:\Scripts\Test.txt に格納されています。

図 1 タブ区切りファイルに保存されている統計

Player	Home Runs	RBI	Average
D Pedroia	4	28	.276
K Kouzmanoff	8	25	.269
J Francouer	7	35	.254
C Guzman	5	20	.299
F Sanchez	2	25	.238
I Suzuki	3	15	.287
J Hamilton	17	67	.329
I Kinsler	7	35	.309
M Ramirez	12	39	.295
A Gonzalez	17	55	.299

この一覧自体には何も問題はありませんが、打ったホームランの数によって選手の一覧を並べ替えて表示したいとします。切断されたレコードセットをこの処理に利用できるのか、ですって。それについては、今からご説明しましょう。図 2 をご覧ください。たくさんのコードがありますが、心配しないで大丈夫です。すぐに見かけほど難しくないことがおわかりいただけると思います。

図 2 切断されたレコードセット

Const ForReading = 1
Const adVarChar = 200
Const MaxCharacters = 255
Const adDouble = 5

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "Player", _
  adVarChar, MaxCharacters
DataList.Fields.Append "HomeRuns", adDouble
DataList.Open

Set objFSO = _
  CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile _
  ("C:\Scripts\Test.txt", ForReading)

objFile.SkipLine

Do Until objFile.AtEndOfStream
    strStats = objFile.ReadLine
    arrStats = Split(strStats, vbTab)

    DataList.AddNew
    DataList("Player") = arrStats(0)
    DataList("HomeRuns") = arrStats(1)
    DataList.Update
Loop

objFile.Close

DataList.MoveFirst

Do Until DataList.EOF
    Wscript.Echo _
        DataList.Fields.Item("Player") & _
        vbTab & _
        DataList.Fields.Item("HomeRuns")
    DataList.MoveNext
Loop

まず、次の 4 つの定数を定義します。

ForReading: テキストファイルを開いて、そのデータを読み取るときに使用します。
adVarChar: Variant データ型を使用するフィールドを作成する標準の ADO 定数です。
MaxCharacters: Variant データ型のフィールドが保持できる最大文字数 (この場合は 255 文字) を示すのに使用する標準の ADO 定数です。
adDouble: Double (Numeric) データ型を使用するフィールドの作成に使用する ADO 定数です。

この 4 つの定数の定義の後には、次のコードブロックがあります。

Set DataList = CreateObject _
    ("ADOR.Recordset")
DataList.Fields.Append "Player", _
    adVarChar, MaxCharacters
DataList.Fields.Append "HomeRuns", _
    adDouble
DataList.Open

これは、切断されたレコードセットを実際に設定して構成するスクリプト部分です。このタスクを実行するには、まず、ADOR.Recordset オブジェクトのインスタンスを作成する必要があります。言うまでもありませんが、このオブジェクトを作成することで、仮想データベーステーブル (つまり、切断されたレコードセット) が作成されます。

次のコード行 (そして Append メソッド) を使用して、新しいフィールドをレコードセットに追加します。

DataList.Fields.Append "Player", adVarChar, MaxCharacters

ご覧のとおり、ここでは特別なことはしていません。単に Append メソッドを呼び出す際に次の 3 つのパラメータを渡しているだけです。

フィールド名 (Players)
フィールドのデータ型 (adVarChar)
フィールドに保存できる最大文字数 (MaxCharacters)

Players フィールドを追加したら、2 番目のフィールドである、Numeric (adDouble) データ型の HomeRuns フィールドを追加できます。その作業が終了したら、Open メソッドを呼び出して、レコードセットを開いて、作業をする準備ができていることを宣言します。

次に Scripting.FileSystemObject オブジェクトのインスタンスを作成して、C:\Scripts\Test.txt ファイルを開きます。スクリプトのこの部分は、実のところ切断されたレコードセットとは関係ありません。これは、テキストファイルからデータを取得するために必要な処理です。テキストファイルの最初の行には、次のようなヘッダー情報が含まれています。

Player     Home Runs     RBI        Average

この情報はレコードセットには必要ないので、ファイルを開いたら、まず SkipLine メソッドを呼び出して、この最初の行をスキップするように指示します。

objFile.SkipLine

これで、実際のデータが含まれている最初の行へと移動できたので、ファイルの残りのデータを行ごとに読み取る Do Until ループを設定します。ファイルから 1 行のデータを読み取るたびに、strLine という名の変数に値を格納し、Split メソッドを使用して (行データをタブで分割することにより) その行データを値の配列に変換します。

arrStats = Split(strStats, vbTab)

確かに、これは大雑把な説明ですが、大部分の方は、これまでのコラムからテキストファイルの情報を取得する方法は理解していると思うので問題ないでしょう。手短に言うと、1 回目のループ処理が完了すると、arrStats 配列には、図 3 に示すアイテムが含まれるようになります。

図 3 配列の内容

アイテム番号	アイテム名
0	D Pedroia
1	4
2	28
3	.276

さて、ここからがおもしろいところです。

DataList.AddNew
DataList("Player") = arrStats(0)
DataList("HomeRuns") = arrStats(1)
DataList.Update

ここで、選手 1 (D Pedroia) の情報を切断されたレコードセットに追加します。レコードをレコードセットに追加するために、まず AddNew メソッドを呼び出します。このメソッドでは、空の新しいレコードが作成されるので、この新しいレコードを使用して作業をします。その次の 2 行のコードを使用して、2 つのレコードセットフィールド (Player と HomeRuns) に値を割り当てます。その後、Update メソッドを呼び出して、レコードをレコードセットに書き込みます。それから、再度、ループの先頭に戻り、テキストファイルの次の行 (つまり次の選手) に対して同じ処理を繰り返します。どうですか。たくさんコードがあるように見えますが、とても単純でわかりやすいですよね。

では、すべての選手の情報がレコードセットに追加されたらどうなるのかをご説明しましょう。テキストファイルを閉じると、次のコードブロックが実行されます。

DataList.MoveFirst

Do Until DataList.EOF
  Wscript.Echo _
    DataList.Fields.Item("Player") & _
    vbTab & _
    DataList.Fields.Item("HomeRuns")
  DataList.MoveNext
Loop

1 行目では、MoveFirst メソッドを使用してカーソルをレコードセットの先頭に移動します。これを実行しないと、レコードセットのデータの一部しか表示されないことがあります。その後、データがなくなるまで (つまり、レコードセットの EOF (ファイルの終わり) プロパティが True になるまで) 実行する Do Until ループを設定します。

このループ処理では、Player フィールドと HomeRuns フィールドの値がエコーバックされるだけです (特定のフィールドを指定するのに少し変わった DataList.Fields.Item("Player") という構文を使用している点にご注意ください)。その後、MoveNext メソッドを呼び出してレコードセットの次のレコードに移動します。

言うまでもないことですが、これは本当に簡単でしたね。すべての作業を完了すると、次のような情報が返されます。

D Pedroia       4
K Kouzmanoff    8
J Francouer     7
C Guzman        5
F Sanchez       2
I Suzuki        3
J Hamilton      17
I Kinsler       7
M Ramirez       12
A Gonzalez      17

ご覧のとおり、考えてみるとそれほど大したものではないですね。選手の名前とホームランの合計数は取得できましたが、ホームランの合計数で並べ替えられていません。困りましたね。どうして切断されたレコードセットでは自動的にデータが並べ替えられないのでしょうか。

これには実は相応の理由があります。レコードセットに並べ替える基準となるフィールドを伝えなかったからです。でも、これは簡単に修正できます。スクリプトを変更して、MoveFirst メソッドを呼び出す直前に並べ替えの情報を追加すれば良いだけです。つまり、スクリプトの該当部分を次のように変更します。

DataList.Sort = "HomeRuns"
DataList.MoveFirst

一目瞭然ですが、ここでは何も特殊なことはしていません。HomeRuns フィールドを Sort プロパティに代入しているだけです。このスクリプトを実行した場合の出力は次のようになります。

F Sanchez       2
I Suzuki        3
D Pedroia       4
C Guzman        5
J Francouer     7
I Kinsler       7
K Kouzmanoff    8
M Ramirez       12
J Hamilton      17
A Gonzalez      17

ずっとわかりやすいですね。ただし、1 つだけ問題があります。普通、ホームランの合計数は、選手が打ったホームランが多い方から少ない方という降順で表示されます。降順でデータを並べ替える方法はあるのでしょうか。

もちろんあります。その場合に必要なことは、次のように、便利な DESC パラメータを付け加えるだけです。

DataList.Sort = "HomeRuns DESC"

DESC パラメータは何をしてくれるのかというと、このパラメータを追加した場合の結果を見てもらえば、わかるでしょう。

A Gonzalez      17
J Hamilton      17
M Ramirez       12
K Kouzmanoff    8
I Kinsler       7
J Francouer     7
C Guzman        5
D Pedroia       4
I Suzuki        3
F Sanchez       2

ちなみに、複数のプロパティで並べ替えることは可能です。その場合に必要な作業は、並べ替えの対象となる各プロパティを Sort ステートメントに指定するだけです。たとえば、最初にホームランの数で並べ替えて、次に打点で並べ替えたい場合も問題ありません。次のコマンドを使うと、この処理を簡単に行うことができます。

DataList.Sort = "HomeRuns DESC, RBI DESC"

試してみて結果をご覧になってください。休暇中にメールをチェックするほど楽しくはありませんが、それに近い楽しさはあると思います。

注レコードセットに追加していないフィールドでデータを並べ替えることはできません。これはどういうことかというと、たとえば、RBI というプロパティを Sort ステートメントに追加する場合は、その前に、スクリプトの適切な場所に次の行を追加する必要がある、ということです。

DataList.Fields.Append "RBI", adDouble

DataList("RBI") = arrStats(2)

また、出力を確認するには、Wscript.Echo ステートメントを次のように変更する必要があります。

Wscript.Echo _
  DataList.Fields.Item("Player") & _
  vbTab & _
  DataList.Fields.Item("HomeRuns") & _
  vbTab & DataList.Fields.Item("RBI")

他に、切断されたレコードセットで何を行うことができるのか、ですって。そうそう、こんなことができます。全選手のすべての情報を取得して、データを打率で並べ替えるとします (これには、他にも必要な作業はありますが、元のスクリプトを変更して、RBI フィールドと Batting-Average フィールドを作成する必要があるというのが主な作業です)。結果は次のようになります。

J Hamilton      17      67      0.329
I Kinsler       7       35      0.309
A Gonzalez      17      55      0.304
C Guzman        5       20      0.299
M Ramirez       12      39      0.295
I Suzuki        3       15      0.287
D Pedroia       4       28      0.276
K Kouzmanoff    8       25      0.269
J Francouer     7       35      0.254
F Sanchez       2       25      0.238

この一覧には何も問題はありませんが、打率が 3 割以上の選手の一覧が必要な場合やある特定の条件に合う選手のデータだけを表示する場合はどうしたら良いのか知りたい方がいるようですね。そうですね。その方法の 1 つとして、Filter をレコードセットに割り当てることができます。

DataList.Filter = "BattingAverage >= .300"

レコードセットのフィルタは、データベースのクエリとほぼ同じ働きをします。つまり、返されるデータをレコードセットの全レコードのサブセットに制限するメカニズムを提供します。ここでは、Batting-Average フィールドに .300 以上の値があるフィールド以外のすべてのレコードを除外するように Filter に指示しています。さて、どうでしょう。フィルタでは指示した処理が実行されます。

J Hamilton      17      67      0.329
I Kinsler       7       35      0.309
A Gonzalez      17      55      0.304

子供たちがこのように反応するとしたらすばらしいと思いませんか。

1 つのフィルタには複数の条件を含めることができます。たとえば、次のコマンドでは、BattingAverage フィールドの値が .300 以上であり、かつ HomeRuns フィールドの値が 10 より大きいレコードのみを返すように制限しています。

DataList.Filter = _
  "BattingAverage >= .300 AND HomeRuns > 10"

また、次のフィルタでは、BattingAverage フィールドの値が .300 以上であるか、または HomeRuns フィールドの値が 10 より大きいレコードのみを返すように制限しています。

DataList.Filter = "BattingAverage >= .300 OR HomeRuns > 10"

両方のフィルタをご自分で試して、違いを確かめてください。どうせならついでに、次のフィルタも試してみてください。

DataList.Filter = "Player LIKE 'I*'"

フィルタではワイルドカードを使用することもできます。これには、(等号ではなく) LIKE 演算子を使用して、MS-DOS® で dir C:\Scripts\*.txt のようなコマンドを実行する場合と同様にアスタリスクを使います。上記の例では、I で始まる名前の選手の一覧が返されます。というのも、ここでは「Player フィールドの値が I で始まり、その後には任意の文字列が続くすべてのレコードの一覧を表示するように」と指示する構文を使用しているからです。これもご自分で…。もう言いたいことは、おわかりですよね。

ところで、0.309 のような打率の表記にはこだわっていませんよね (通常、打率の前には 309 のように 0 を付けません)。こだわりがある場合でも大丈夫です。FormatNumber メソッドを使えば、打率をお好みの表記に変えることができます。

FormatNumber (DataList.Fields.Item("BattingAverage"), 3, 0)

Wscript.Echo ステートメントの数字を表示するところに、このメソッドを含めます (または、出力内容を変数に代入して、Echo ステートメントにその変数を組み入れることも可能です)。

Wscript.Echo _
  DataList.Fields.Item("Player") & _
  vbTab & _
  DataList.Fields.Item("HomeRuns") & _
  vbTab & DataList.Fields.Item("RBI") & _
  vbTab & _
  FormatNumber _
  (DataList.Fields.Item("BattingAverage"), _
   3, 0)

お楽しみいただけましたでしょうか。

残念ながら、今月も時間切れのようです。まとめとして言いますと…。すみません、電話が鳴っています。

とにかく、お伝えしたいのは…。おっと、今度は携帯電話が鳴っています。おまけに、トースターからメールが届きました。「重要なお知らせ : トーストが焼き上がりましたが、バターとジャムのどちらを塗りますか」という内容です。もう行かなければなりません。では、また来月お会いしましょう。

Dr. Scripto のスクリプトパズル

パズルを解く能力だけでなく、スクリプト作成スキルもテストする月に 1 度の課題です。

2008 年 9 月 : スクリプト探し

これは簡単な言葉探しです (もしかすると、そう簡単ではないかもしれませんが)。次の一覧にある VBScript 関数とステートメントをすべて探し出してください。ちょっとした仕掛けが隠されています。残った文字は、PowerShell™ コマンドレットの名前になっています。

単語の一覧 : Abs、Array、Atn、CCur、CLng、CInt、DateValue、Day、Dim、Else、Exp、Fix、InStr、IsEmpty、IsObject、Join、Len、Log、Loop、LTrim、Mid、Month、MsgBox、Now、Oct、Replace、Set、Sin、Space、Split、Sqr、StrComp、String、Timer、TimeValue、WeekdayName

解答 :

Dr. Scripto のスクリプトパズル

解答 : 2008 年 9 月 : スクリプト探し

Scripting Guys は、マイクロソフトの仕事をしています、というよりもマイクロソフトにより雇われています。野球をプレイしたり監督したり観戦したり (または他のさまざまな活動を) しているのでない限り、彼らは TechNet スクリプトセンターを運営しています。詳細については、www.scriptingguys.com を参照してください。

図 1 タブ区切りファイルに保存されている統計

図 2 切断されたレコードセット

図 3 配列の内容

その他のリソース