2020年10月17日
今回はデータベースの操作を取り上げます。
一般には Accessデータベースとして知られているものですが、
Accessがインストールされていなくても使えます。
test.accdb のような拡張子が .accdb のファイルを操作します。
最後の方では NHKのニュースサイトから得た情報をデータベースに記録します。
JuseOffice09.zipにサンプルが入っています。
「Accessがインストールされていなくても使える」と書きましたが、
データベース用のドライバーがインストールされていないと使えません。
ほとんどの場合、Windowsに標準でインストールされているとおもいますが、
ここで取り上げるスクリプトが動作しないときは
下記のサイトを参考にドライバーのインストールを試みてください。
ODBC Driver for SQL Server のダウンロード
データベースを作成するという場合、一般的に次のことを行います。
以下ではデータベース操作用のプログラミング言語 SQL を使って
上記の操作を行います。
ここで取り上げるSQLは一目瞭然の基本的な記述だけなので解説は省略します。
SQL の詳細については他のサイトを参照してください。
JuseOffice では [データベースを開く] という関数を定義しています。
[データベースを開く] "test.accdb"上のようにすると test.accdb をオープンします。
該当のファイルがない場合は新たに作成します。
次に、テーブルの作成はSQL命令文で行います。
サンプルでは下の電話番号簿を作成しています。
| 名称 | 電話番号 |
|---|---|
| 時報 | 117 |
| 天気予報 | 177 |
この枠組みを作るためのSQL命令文は下のとおり。
create table 電話番号簿
(名称 varchar(40), 電話番号 varchar(20));上記を文字列として変数sqlに代入してから
[sqlを実行] sql上を実行するとテーブルが作成されます。
[sqlを実行] は JuseOffice で定義してある関数です。
ここでサンフルスクリプト db_test01.wsf の前半を掲げておきます。
dbFile = "db_test01.accdb"
[データベースを開く] dbFile
sql = "create table 電話番号簿 " & _
"(名称 varchar(40), 電話番号 varchar(20));"
[sqlを実行] sql
【後略】insert命令によってテーブルにデータを書き込みます。
insert into 電話番号簿 (名称, 電話番号) values('時報', '117');上のSQL命令文を実行すれば、一組のデータ(レコード)が書き込まれます。
db_test01.wsf では、時報と天気予報の二つのレコードを書き入れています。
データベースの処理が一通り終わったら、
[データベースを閉じる] を実行します。
以下にサンプルスクリプト全体を掲げます。
'RunOn32bit
dbFile = "db_test01.accdb"
If [ファイルがある](dbFile) Then [ファイルを削除] dbFile
[データベースを開く] dbFile
sql = "create table 電話番号簿 " & _
"(名称 varchar(40), 電話番号 varchar(20));"
[sqlを実行] sql
sql = "insert into 電話番号簿 (名称, 電話番号) values('時報', '117');"
[sqlを実行] sql
sql = "insert into 電話番号簿 (名称, 電話番号) values('天気予報', '177');"
[sqlを実行] sql
[データベースを閉じる] 'RunOn32bit については、もう少し後の方で述べますが、
もしスクリプトを実行してエラーが発生するなら
行頭の ' を削除して再度試してみてください。
insert命令文を何度も書くのは面倒です。
そこで、SQL命令文のひな形を設けておいて
それを部分的に書き換えて実行することを考えます。
具体的には次のひな形を用意します。
sqlTemplate = "insert into 電話番号簿 (名称, 電話番号) values('%0', '%1');" 上の %0, %1 を適当なデータに置き換えて実行します。具体的には下のとおり。
[sqlを実行%] sqlTemplate, "時報 117"上のようにすると、%0 が「時報」、%1 が「117」に置き換えられて実行されます。
最後の引数を配列で与えることもできます。
[sqlを実行%] sqlTemplate, Array("時報", "117") でも、配列の記述は面倒なので "時報 117" の文字列で与えています。
文字列は、内部処理で、半角スペースを区切り文字として配列に変換されます。
JuseOffice の中で RunOn32bit というサブルーチンを定義しています。
スクリプトを64bit ではなく 32bit で実行するためのものです。
VBScript で書かれたスクリプトは
WSCript.exe というコマンドに渡されて実行されます。
この WScript.exe には 64bit版と 32bit版の二つがあります。
64bitのWindowsを使っているときは、64bit版が起動されます。
さて、ここで問題が発生する場合があります。
データベース用のドライバーとして
64bit版がインストールされていればいいのですが、
たまに 32bit版がインストールされている場合があります。
(たぶんOfficeソフトの32bit版が導入されている。)
そのような場合に RunOn32bit を呼び出します。
そうすると、32bitのドライバーがちゃんと動作するというわけです。
サンプルのスクリプトには 'RunOn32bit という行があります。
その行頭の ' を削除すれば 32bitのドライバーが使われます。
以前の Accessデータベースでは test.mdb のような
拡張子が mdb のものが使われていました。
このmdbファイルを安全・確実に扱うには 32bitのドライバーの方が望ましいです。
なお、拡張子が accdb のファイルは、32bitのドライバーで
何ら支障なく扱うことができます。
Accessデータベースのファイルを作成しても、
Accessがインストールされてなければ中身を確認できない……
というわけではありません。
ここではデータベースの中身を読み取る方法を紹介します。
Excelの QueryTable を用いると
データベースの中身をExcelに取り込むことができます。
それを行うためのスクリプト accdb2xlsx.wsf を用意しました。
カレントフォルダにある accdbファイル一つずつについて
変換するかどうか尋ねてくるので「はい/いいえ」で応答します。
test.accdb を変換すると、test.xlsx が作成されます。
一つのテーブルが一つのワークシートに変換され、
テーブル名がワークシート名になります。
QueryTable の詳細については省略しますが、
要は、データベースに対する問い合わせ(query)を行って
その結果を得ることができるという機能です。
問い合わせは SQL命令文で行います。典型例は下のとおり。
sql = "select * from 電話番号簿;" 問い合わせのためにはデータベースに含まれているテーブル名が必要ですが、
次の記述でテーブルの情報が得られます。
Set tblDict = [データベースの表の一覧]("TABLE VIEW")変数 tblDict は辞書型です。
テーブル名を一つずつチェックするには次のようにします。
For Each tblName In tblDict.Keys
MsgBox tblName
Next なお、accdb2xlsx.wsf の中の *.accdb を
*.mdb に変更すれば mdbファイルをExcelファイルに変換できます。
64bitのドライバーでも大丈夫です。
Excelファイルでなくテキストファイルに変換したいときは
accdb2text.wsf を実行します。
タブ区切りのテキストファイルが作成されます。
JuseOffice では [データベースから配列へ] という関数を定義しています。
sql = "select * from 電話番号簿;"
ary = [データベースから配列へ](sql)上のようにすると、該当のレコード群が配列として変数aryに代入されます。
レコードを一つずつ取り出すには次のようにします。
For Each record In ary
recordStr = Join(record, vbTab)
Nextaryは、いわゆる2次元配列ではなく、配列の配列です。
最初の要素 ary(0) にはデータではなくフィールド名がセットされます。
具体的には Array("名称", "電話番号") がセットされます。
多くのデータをデータベースに取り込む場合、
csvファイルで用意して、それを取り込むといったことはよくあります。
JuseOfficeには [csvからデータベースへ] というサブルーチンがあります。
データベースの特定のテーブルに、追加の形でデータを書き込みます。
引数は次の三つ。
"電話番号簿""sample_data.csv""Yes" または "No"sample_data.csv は、氏名、身長、生年月日の三つが列記されたファイルです。
ヘッダはなく、1行目からデータが書かれています。
田中,170.6,1978/10/5
佐藤,169.7,1968/2/15
高橋,181.9,1989/1/21
相沢,175.3,1977/5/18このcsvをデータベースの「名簿」というテーブルに追加する記述は下のとおり。
[csvからデータベースへ] "名簿", "sample_data.csv", "No"db_test03.wsf は、csvを取り込むサンプルです。下に掲げておきます。
'RunOn32bit
dbFile = "db_test03.accdb"
If [ファイルがある](dbFile) Then [ファイルを削除] dbFile
[データベースを開く] dbFile
sql = "create table 名簿 " & _
"(氏名 varchar(20), 身長 float, 生年月日 date);"
[sqlを実行] sql
[csvからデータベースへ] "名簿", "sample_data.csv", "No"
[データベースを閉じる] これまで掲げたスクリプトは accdbファイルを作成するものですが、
Excel用のxlsxファイルを作成するのにも使えます。
スクリプト中の .accdb を .xlsx に変更すればExcelファイルが作成されます。
SQL命令文がごく単純なものであれば Excelファイルを扱うのに支障はありません。
ただ、Excelでは処理できないSQL命令文もあるので注意が必要です。
いくつか例を挙げてみます。
Accessデータベースでは画像や写真のバイナリーデータを保管できますが、
Excelでは必ずしも正しく保管できるとは限りません。
特にサイズの大きいデータは、扱わないのが無難です。
accdb2xlsx.wsf では QueryTableを利用して
accdbファイルをxlsxファイルに変換していますが、
バイナリーデータは自動的に対象外になります。
Accessデータベースでは仮想テーブルを扱うことができますが、
Excelでは扱えません。
仮想テーブルは、標準テーブルを組み合わせることで生成されます。
標準テーブル(TABLE型)として「ID, 氏名」からなるものと
「ID, 生年月日」からなるものの二つがあるとします(IDが同じなら同一人物)。
この二つを組み合わせて「ID, 氏名, 生年月日」からなる
仮想テーブル(VIEW型)を生成できます。
VIEWはデータの実態を保持していないため記録容量を節約でき、
また、素材のTABLEが変更されれば VIEWの方も自動的に変更されます。
本格的なデータベース処理では、とても効力を発揮する機能です。
オートナンバー型は、データの入力が省略された場合に
1, 2, 3, 4, …… のように自動的に数値が書き込まれるものです。
counter というキーワードを用いて次のように定義します。
create table 電話番号簿
(ID counter(1,1), 名称 varchar(40), 電話番号 varchar(20)); counter(10,5) とすれば 10, 15, 20, 25, 30, …… となります。
ですが、Excelでは数値が入ることはなく空欄になります。
データベースではフィールドの挙動を定める様々なオプションがありますが、
Excelでは「オプションを指定しても効力を発揮しない」
と考えた方が無難です。
ちなみに、varchar(40) のような文字列の桁指定もExcelでは無効です。
指定しても結局 varchar(255) となるようです。
drop table 電話番号簿; というSQL命令文を実行すると、
「電話番号簿」というテーブルが削除されます。
Excelではテーブルがワークシートに該当します。
それでは drop table 電話番号簿; を実行すると
「電話番号簿」というワークシートが削除されるかというとそうはなりません。
ワークシートの中身が空っぽにはなりますが、ワークシートそのものは残ります。
逐一 列記はしませんが、これに類することは他にもいろいろあります。
以上、Excelをデータベースとして利用する際の制限の一端を述べました。
本格的なデータベース処理を行うなら accdbファイルにするのが無難です。
データベースとして Excelを扱うことのメリットをあえていうなら
処理速度がかなり短縮されることでしょうか。
Excelを起動して処理するわけではないので処理に時間がかかりません。
適宜どちらがいいか選択してみてください。
少し実践的な例として、NHKのニュースサイトから情報を取得して
データベースに記録してみます。
Webのhtmlを解析して必要な部分だけ取り出すのに
InternetExplorerを用います。
その詳細は長くなるので省きますが、要点のみ記します。
NHKのニュースサイトから情報を取得するための関数などが入っている
NHK_news.vbs を作成しました。
Webの記事情報をデータベースに記録するための関数も定義されています。
それらを使うと、少ない行数のスクリプトで目的を実現できます。
NHK_test01.wsf, NHK_test02.wsf がそうしたスクリプトの例です。
前者は記事の一覧をデータベースに記録します(記事本文は取得しない)。
後者は記事本文も含めて記録します。
NHK_test01.wsf の主要部分は次のとおり。
'RunOn32bit
Include "NHK_news.vbs"
Set IE = CreateObject("InternetExplorer.Application")
IE.visible = True
url = "https://www3.nhk.or.jp/news/catnew.html"
dbFile = "NHKニュース.accdb"
dbTable = "新着"
count = [NHK記事の一覧をデータベースに](IE, url, dbFile, dbTable)
IE.Quit
MsgBox count & "個の記事情報を取得.", vbSystemModal, "NHK_test01"NHKの新着ニュースから、表題(ヘッドライン)、記事の日時、URLを取得します。
NHK_test02.wsf の方も ほぼ同じスクリプトです。
データベース操作が関数の中に隠されてしまっているので
これを見ただけでは処理の内容が分かりません。
以下で NHK_news.vbs で定義している関数に触れながら
処理の内容を説明したいとおもいます。
NHK_news.vbs で定義している関数のうち
Webから情報を取得するための関数で、かなめとなるのは下の二つです。
記事の一覧(表題, 日時, URL)を得て配列に記録します。
引数は IE(InternetExplorerオブジェクト)、URL(文字列)の二つ。
URLにはNHKニュースサイトの「新着、ビジネス、社会、国際」などの
ジャンルのトップページのURLを指定します。
戻り値は配列の配列で、たとえば ary(0) が
「表題, 日時, URL」の3要素からなる配列になっています。
htmlの解析方法としては、全部の <a …… </a> を取り出して
日時の情報(<time …… )が入ったものを記事情報とみなします。
一つの記事本文を得て文字列として返します。
引数は IE, URL の二つ。
この場合のURLは、一つの記事を指し示すURLです。
最初にみつかった <section …… </section> に
記事本文が入っていることを利用しています。
データベースに記録するための関数で、かなめとなるのは次の二つです。
記事の一覧(表題, 日時, URL)をデータベースに記録します。
引数は IE, URL, dbFile, dbTable の四つ。
dbFile は "NHKニュース.accdb" のようなデータベースファイル名、
dbTable は "新着" のようなテーブル名です。
戻り値は、実際に記録した記事情報の個数。
テーブルを作成するとき、表題, URL の二つのフィールドには
unique(重複禁止)のオプションを指定しています。
なので、同じものが既に記録されていないかを事前にチェックします。
Webに掲載されているのは、せいぜい昨日から今日にかけてのものなので
データベースの中の「昨日から今日」だけを検索し、
重複してなければ記録します。
下のSQL命令文で「昨日から今日」の情報を引き出すことができます。
select * from 新着
where 日時 >= Date()-1 and 日時 < Date()+1;記事の一覧情報だけでなく、記事本文も含めて記録します。
引数は IE, URL, dbFile, dbTable の四つ。
URLにはNHKニュースサイトの「新着、ビジネス、社会、国際」などの
ジャンルのトップページのURLを指定します。
そこから辿れる記事を一つずつ記録します。
戻り値は、実際に記録した記事情報の個数。
表題, URL がまだ記録されていなければSQLの insert でデータを追加します。
insert into 新着 values('%0', #%1#, '%2', '%3');もちろん、%0～%3は、「表題, 日時, URL, 記事」の具体的な値に置き換えます。
URLが既に記録されていて、記事本文などを加える場合は
update を使って次のようにします。
update 新着 set 表題 = '%0', 日時 = #%1#,
URL = '%2', 記事 = '%3' where URL = '%2'; 上記は、あるレコードのURLが特定の値である場合に
その該当のレコードを上書きするという意味です。
なお、InternetExplorerで各々の記事本文を取得すると時間がかかります。
なので、一つの記事を取得・記録するたびに
「じゃりじゃり」のような音がなるようにしてあります。
全部の処理が終わったら「タターン」のような音がなります。
記事本文が長いので、タブ区切りテキストの形で書き出すと
読みにくくなってしまいます。
そこで、フィールドごとに改行する形で書き出します。
それを行うのが NHK_text.wsf です。
結果は NHK_text.txt に書き出されます。
記事本文があればそれを書き出し、
なければ「表題, 日時, URL」を書き出します。
日時の新しいものから古いものの順番で書き出します。
実行するSQL命令文は下のとおり。
select * from 新着 order by 日時 desc; テーブルに記録されている全レコードを書き出すようになっていますが、
日時を絞りたい場合は where で条件指定を行ってください。
JuseOfficeにおけるデータベース関連の関数について
説明しきれていない部分がいろいろあります。
いくつか補足的に説明を記します。
[データベースを開く] では accdb, xlsx の他に mdb, xls ファイルも扱えます。
mdb, xls は、32bitのドライバーでは問題なく扱えますが、
64bitの場合、読み込みは問題ない者の、書き込みがうまくいかないかもしれません。
sql = "select * from DummyTable;"
Set rs = [sqlを実行](sql) 上記が成功すれば変数rsに Recordsetオブジェクトがセットされますが、
失敗したときは Nothing がセットされます。
たとえば、DummyTable というテーブルが存在しない場合は失敗します。
なぜ失敗したかを示すエラーメッセージは、
「Warning(JuseOffice).txt」というファイルに書き出されます。
このWarningファイルがなければ「失敗したものはなかった」と考えて大丈夫です。
Set tblDict = [データベースの表の一覧]("TABLE VIEW") 上のようにして得られた戻り値 tblDict は辞書型で、
tblDict.Keys によってテーブル名の一覧を得ることができます。
そして、データベース内に test という名前のテーブルがあるとしたら
tblDict("test") にテーブルの関連情報が入ります。
これは、テーブルの「名前, 種類, 作成日時, 更新日時」の
四つの要素からなる配列です。
「種類」というのは、TABLE(標準テーブル)、VIEW(仮装テーブル)などのことです。
他に、ユーザーの意図とは関係なく設けられる管理用テーブルもありますが、
通常は参照する必要はないとおもいます。
[データベースの表の一覧]("TABLE VIEW") の引数は、
取得したいテーブルの種類を指定しています。
引数を Null にすれば管理用テーブルを含めた全テーブルの情報が得られます。
データベースの操作は Connectionオブジェクトによって行われます。
ですが、これまでのスクリプトにはその Connectionオブジェクトが出てきません。
実は [データベース] という変数にそのオブジェクトがセットされていて
内部的に操作されています。
でも、たとえば二つのデータベースを同時平行で操作したいような場合は
Connectionオブジェクトを取り出して自分で管理する必要が出てきます。
Connectionを自分で管理したいときは、たとえば次のようにします。
Set cn = [データベースを開く]("test.accdb")
sql = "select * from TestTable;"
Set rs = [sqlを実行on](cn, sql)
【途中省略】
[データベースを閉じるon] cn データベース関連の関数やサブルーチンで、「on」が付くものは
第1引数に Connectionオブジェクトを与えます。
なお、Set rs = [sqlを実行on](cn, sql) というのは
Set rs = cn.Execute(sql) でもいいわけですが、
前者だとエラー処理が組み込まれているので
不必要に処理が中断されることはありません。
～ 以上 ～
Copyright (C) T. Yoshiizumi, 2020 All rights reserved.