UWSCでODBC(Microsoft Text Driver)を使ったCSVファイル検索
UWSC公式掲示板の素晴らしい回答に触発されて、別方式版を作ってみた。
http://www3.bigcosmic.com/board/s/board.cgi?id=umiumi&mode=all&no=3715
でも、Findstr方式に比べても、3倍くらいの遅さなので、あまり実用的ではないので黙ってることに。
メリットは、、、前処理を自動でやってくれるのと、読み検索もできる、くらい。
技術的なメモとしては、SLCTBOXに連想配列を渡せることと、連想配列はほとんど可変配列であるということ。
あとは、ODBC接続はこうやると出来ますよ、というところ。
ODBC接続はCOMなので、WSH(JScript/VBScript)やPowershellでも同じ。
まずは作ったものの提示
ちょっと、準備が面倒です。
KEN_ALL.CSVとスクリプトと、次に示すschema.iniが同一フォルダにある想定です。
また、大抵の環境で動くと思いますが「Microsoft Text Driver」が入っていない環境では動作しません。
通常大丈夫だと思いますが、、、。
「odbcad32」コマンドで有無が確認できた、、、はず。
schema.ini
[KEN_ALL.CSV] ColNameHeader=False CharacterSet=ANSI Format=CSVDelimited Col1=JIS_X0401 Text Col2=ZIP5 Text Col3=ZIP Text Col4=KEN_YOMI Text Col5=SHI_YOMI Text Col6=OP_YOMI Text Col7=KEN Text Col8=SHI Text Col9=OP Text Col10=OP_D Short Col11=ABOUT_ZIP Short Col12=REF_D Short Col13=OP_GROUP Short Col14=TYPE Short Col15=REASON Short [ken_all_proc.csv] ColNameHeader=False CharacterSet=ANSI Format=Delimited( ) Col1=ZIP Text Col2=ADDR Text Col3=YOMI Text
こっちが肝心のスクリプト
OPTION EXPLICIT
IFB CheckFileDate("KEN_ALL.CSV", "ken_all_proc.csv") THEN
IFB MSGBOX("前処理後ファイルが古いようです。前処理をしますか？", BTN_YES or BTN_NO) = BTN_YES THEN
PRINT "PreProc"
PreProc()
ENDIF
ENDIF
dim addr = "いぶき野", zip, str = ","
while (str = EMPTY or LENGTH(str) < 2) and addr <> EMPTY
addr = INPUT("検索住所", addr)
if LENGTH(addr) > 0 then str = Search(addr)
wend
zip = token(",", str)
msgbox("郵便番号 : " + zip + "<#cr>" + " 住所 : " + str)
FUNCTION Search(addr)
DIM old = gettime()*1000+G_TIME_ZZ
DIM col = "ADDR"
// 全てが全角カタカナなら、半角カタカナに変換する
DIM regexp = CreateOleObj("VBScript.RegExp")
regexp.Pattern = "^[ァ-ヾ]+$"
if regexp.Test(addr) then addr = strconv(addr, SC_HALFWIDTH)
// 半角カタカナなら、検索対象はYOMI
regexp.Pattern = "^[ｦ-ﾟ]+$"
if regexp.Test(addr) then col = "YOMI"
DIM db = CREATEOLEOBJ("ADODB.Connection")
db.Open("Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=.; ReadOnly=1")
DIM rs = db.Execute("SELECT * FROM ken_all_proc.csv WHERE " + col + " like '%" + addr + "%'")
hashtbl d
dim i = 0
while !rs.EOF
d[i] = rs.Fields["ZIP"] + "," + rs.Fields["ADDR"]
i = i + 1
rs.MoveNext()
wend
PRINT "検索時間 : " + (gettime()*1000+G_TIME_ZZ-old)/1000 + "秒"
DIM str = ","
ifb i = 0
msgbox("該当がありません")
elseif i = 1
str = d[0]
else
str = SLCTBOX(SLCT_LST or SLCT_STR, 0, "選択 : "+ i + " 件", d)
endif
RESULT = str
FEND
PROCEDURE PreProc()
DIM old = gettime()*1000+G_TIME_ZZ
DIM db = CREATEOLEOBJ("ADODB.Connection")
db.Open("Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=.; ReadOnly=1")
FUKIDASI("検索中です")
DIM rs = db.Execute("SELECT * FROM ken_all.csv")
DIM fp = FOPEN("ken_all_proc.csv", F_WRITE), i = 0
while !rs.EOF
FPUT(fp, rs.Fields["ZIP"] + " " + rs.Fields["KEN"] + rs.Fields["SHI"] + rs.Fields["OP"] + " " + rs.Fields["SHI_YOMI"] + rs.Fields["OP_YOMI"])
i = i + 1
if (i MOD 1000) = 0 then FUKIDASI(i + "件処理完了")
rs.MoveNext()
wend
FCLOSE(fp)
PRINT "処理時間 : " + (gettime()*1000+G_TIME_ZZ-old)/1000 + "秒"
FEND
FUNCTION CheckFileDate(f1, f2)
DIM fso = CreateOleObj("Scripting.FileSystemObject")
COM_ERR_IGN
DIM o1 = fso.GetFile(f1), o2 = fso.GetFile(f2)
RESULT = (o1.DateLastModified > o2.DateLastModified)
COM_ERR_RET
if COM_ERR_FLG then RESULT = true
FEND
PROCEDURE Test(addr)
PRINT "FindStr"
PRINT SearchFindstr(addr)
PRINT ""
PRINT "No PreProc"
PRINT SearchOdbc(addr)
PRINT ""
PRINT "Odbc"
PRINT Search(addr)
PRINT ""
FEND
FUNCTION SearchOdbc(addr)
DIM old = gettime()*1000+G_TIME_ZZ
DIM db = CREATEOLEOBJ("ADODB.Connection")
db.Open("Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=.; ReadOnly=1")
DIM rs = db.Execute("SELECT * FROM ken_all.csv WHERE (KEN+SHI+OP) like '%" + addr + "%'")
hashtbl d
dim i = 0
while !rs.EOF
d[i] = rs.Fields["ZIP"] + "," + rs.Fields["KEN"] + rs.Fields["SHI"] + rs.Fields["OP"]
i = i + 1
rs.MoveNext()
wend
PRINT "検索時間 : " + (gettime()*1000+G_TIME_ZZ-old)/1000 + "秒"
DIM str
ifb i = 0
msgbox("該当がありません")
elseif i = 1
str = d[0]
else
str = SLCTBOX(SLCT_LST or SLCT_STR, 0, "選択 : "+ i + " 件", d)
endif
RESULT = str
FEND
FUNCTION SearchFindstr(addr)
dim old = gettime()*1000+G_TIME_ZZ
dim fp = fopen("temp.csv", f_write)
fput(fp, trim(doscmd("findstr /R <#dbl>" + addr + "<#dbl> KEN_ALL.csv")))
fclose(fp)
fp = fopen("temp.csv")
dim n = fget(fp, -1), d[n-1], i
for i = 1 to n
d[i-1] = replace(fget(fp,i , 3) + "," + fget(fp,i , 7) + fget(fp,i , 8) + fget(fp,i , 9), "<#dbl>", "")
next
fclose(fp)
PRINT "検索時間 : " + (gettime()*1000+G_TIME_ZZ-old)/1000 + "秒"
DIM str
ifb d[0] = ","
msgbox("該当がありません")
str = d[0]
else
if n = 1 then str = d[0] else str = SLCTBOX(SLCT_LST or SLCT_STR, 0, "選択 : "+ n + " 件", d[])
endif
RESULT = str
FENDなお、Test関数より下は不要です。
速度計測のために、書いてみたもの。
SearchFindstr関数は、Linersさんの作ったもの。
SearchOdbc関数は、前処理なしだとどうなるか作ったもの。
とても遅い。SearchFindstr関数の6倍くらいの遅さ。
使い方
三つのファイルが揃ったら、スクリプトを実行してください。
最初に「前処理したファイルが古い」とか言われます。
「はい」を押すと「ken_all_proc.csv」という名前で作成されます。
手元の環境では1分くらいかかります、、、。
あまりに遅いので、FUKIDASIで途中経過を出してます。
前処理は、KEN_ALL.CSVを最新にする毎に実行されます。
前処理が終わると、入力ボックスが出て、検索が出来るようになります。
カタカナのみを入れると、読み検索。
カタカナ以外の文字では、住所表記検索になります。
複数見つかると、SLCTBOXなのは同じ。
この時、何も選択しなければ、再度検索を行います。（絞込みではない）
岐阜県の恵比寿を検索したい、とか言う場合は「岐阜県%恵比寿」と入れます。
ワイルドカードは「%」です。
このスクリプトに至るまで
- UWSCのみと考えて、部分読込を作ってみて、あまりの遅さに絶望する
- Linersさんの回答に感動する
- 「この方法もUWSCのみと言えるのだよ、甘いな、君」と言われた気になり、他の方法を考える
- そうそう、検索と言えばSQLでしょ、と思う。
- SQLでテキスト、、、お手軽なのはODBCドライバーか。（遅いだろうな、とも思う）
- SearchOdbc関数を書いてみる
- 6倍という遅さに悲しくなる（ま、最初に比べれば早いけど、、、）
- senさんの回答に「前処理を、、、億劫がらずに、、、」という文言を見る
- 億劫！億劫なことは人間のやることではありません！と思う
- 前処理判定、かつ、SQLの遅い部分解消を狙う
で、本スクリプトとなる。
ファイル構成
ken_all_proc.csv
KEN_ALL.CSVから作成されるファイル。
質問にあったフォーマットをなんとなく流用。
半角から全角への変換は、諸事情によってやってない。
→やる場合は、スクリプト変更が必要
最早csvじゃないファイル。（あえて言うならssv）
schema.ini
Microsoft Text Driverが、CSV等のフォーマットを解釈するためのファイル。
先頭がカラム名になっている場合は、このファイルはなくてもなんとかなる。
KEN_ALL.CSVは先頭もデータ行なので、先頭にカラム名を追加するか、
schema.iniでフォーマットを指定するかが必要となる。
毎回カラム名を先頭に追加するのは面倒なので、schema.iniを採用。
もし、日本郵便が提供するファイルのフォーマットに変更が出た場合は、
このファイルを修正する必要がある。
スクリプトの解説
CheckFileDate関数は、指定された二つのファイルの更新日付を取得し、第一ファイルが新しければTrueを返す。
どちらかのファイルが存在しない場合もTrueを返す。
ということで、KEN_ALL.CSVがない状態でこのスクリプトを動かすのは想定外。
エラートラップの書き方が酷すぎる。
Search関数
入力された住所を検索して、返す。
入力文字の判定をしたり変換をしたりして、読み仮名や表記住所で検索を行う。
「"Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=.; ReadOnly=1"」がODBC接続文字列の指定。
ここを変更すると、Excelファイル検索やAccessファイル検索、はたまたDBMSにも接続可能。
なお、TextドライバーでDBQ=.なので、カレントディレクトリを対象にしている。
スクリプトと違うところに、KEN_ALL.CSVをおきたい場合は、これも修正する必要がある。
連想配列のこういう使い方は今回初めて気付いた。
便利すぎ。
今までTokenは配列との相性が悪い、と思ってたけど、連想配列を可変配列として扱えば解決。
SLCTBOXに配列として渡せるところも恐ろしい。
PreProc関数
多分こういう前処理をしたのでは？という想像をもとに作った関数。
Accessの方が早いと思うけど、わざわざAccessを立ち上げなくて良いのがメリット。
（いや、コマンドラインから操作するようにしてたら立ち上げないかもしれませんが）
最初、KEN_ALL.CSVもFOPENで開いてたのですが、、、ちょっと遅かったので、これもODBC経由にしています。
それでも遅いので、1000件毎にFUKIDASI表示。
FUKIDASI表示する分だけ、処理時間は少し遅くなりますが、心配になるのである方がよさげ。
なお、"Scripting.FileSystemObject"経由で書き出してみたけど、あんまり変わらなかった。
メモリがかつかつのPCなら早いかもしれない。
Test関数
速度計測用の関数。3つの検索関数に同じ文字列を渡して、時間を見るためのもの。
メインから呼ばれるパスがないので、実験したい場合は、メインに記述が必要。
というか、いらないので削除すべき。
SearchOdbc関数
遅い原因は、SQL文「SELECT * FROM ken_all.csv WHERE (KEN+SHI+OP) like '%" + addr + "%'」
これを解消しても、Findstr版には勝てませんが、、、。
like演算子が遅い上に、カラムの結合が遅い。
Search関数は、likeはどうしようもないけど、カラム結合をなくしてスピードアップ。
ただ、インデックスも張れないし、これ以上早くはできません。
ODBC経由で検索を考えたのが、愚かな気がする、、、。
これもいらない子。
SearchFindstr関数
- 大量のデータはUWSC向きではない
- では得意なヤツに少量にしてもらえば良い
この発想ですね。
なんか、こう、わかっている技術の組み合わせなのに、思いつかなかったことがとてもくやしい。
コロンブスの卵、とはまさにこのこと。
いや素晴らしい。
ちなみに、正規表現FindStrの2バイト文字バグは、私も知りませんでした。
修正する気はないだろうなぁ。
「Scripting.FileSystemObjectとVBScript.Regexpで同等のことができます」とか言われそう。
FindStrは2バイト文字に弱い子。
（そういえば、Scripting.FileSystemObject+VBScript.Regexpの例は書いてないけど、、、多分遅い）
結論
これ、ODBC接続とMicrosoft Text Driverにおけるschema.iniのメモ記事ですね。
多分、Linersさんの方法に、自動前処理と読み仮名検索を組み込むのがベスト、と思いつつ、それは放置。
PreProc()からODBC接続を排除すれば、shema.iniが不要になるので、より良さそう。
ただ、FGETでやるようにしてみたところ、時間が倍になったので、何か他の方法が良いかも。
（手元のWin7 x64環境で、50秒程度の前処理が、FGET版では100秒程度になった、、、
とはいえ、そう頻繁にやることではないので、良い気もする）
余談
Powershellを平行して学習中。
これまた便利。
COMオブジェクトのメンバー調査をしたい場合、とても使える。
例えば、今回、更新日付を取得したくなって、どうしたかと言うと、、、
Scripting.FileSystemObjectで出来そうと思う
$fs = New-Object -ComObject "Scripting.FileSystemObject" $fs | Get-Member | Out-GridView
GetFileメソッドが使えそうに思う
$fs.GetFile("ken_all.csv")DateLastModifiedプロパティが目的のものと、発見という流れ。
いや、先輩方のサイトを見れば、すぐ分かったとも思いますが、なんとなく自力で。
せんだって眠い頭で、だーっと書いてしまった、IEのスクロール座標の取得も、こうやって調査していたりします。
ちなみに例の件は、ちょっと不親切過ぎたのでは、と悔やんでいたりもします。
「IEオブジェクトの取得スクリプトはPro版付属のRecIEを使えば、すぐわかりますよ」
とか宣伝文句を入れても良かったかもしれない。
なお、ODBCの接続文字列は検索する以外の良い方法を知らない、、、。
ODBC接続を使おうとすると、常に苦労させられる部分（あまり使わないから覚えない）
