[VBS]ADODB.Connectionを試してみた。

前回、CSVの差分マッチングを高速化する為にADODB.Connectionを使ってみたんですよね。

[WinActor]CSVの差分出力(高速版)

これが、まぁ、すごく処理早くてよかったんですよね。

で、ちょっと調べたらCSVでもExcelでも、もちろんAccessでもクエリ実行ができるみたいですね。

いろいろいじってみて引っかかったとこのTipsを残しておきます。

 

1.コネクションオブジェクトを作る。

vbscriptの場合は、こうですね。

で、この作成したコネクションオブジェクトのオプションで、いろんなファイルをデータソースとして扱える様な使い方ができるみたいですね。

 

2.CSVの場合はこんな感じ。

Open関数に「Driver={~}; DBQ=~」みたいな魔法詠唱(笑)を指定することでCSVをDBテーブルみたいに扱える様になるんですねぇー

ここで注意なのは、「Microsoft Text Driver」の場合は、DBQに指定するのはCSVファイルが配置されているフォルダまでのパスってことです。

CSVのファイル名を、SQLの中でテーブル名の様にしてFROM句に指定していきます。

前回の例の様に、同じフォルダにあればCSVファイル同士をJOINしたりUNION

したりできるんですねぇ。

「ReadOnly=1」ってなっちゃってる通り、とりあえずSELECTしか検証しませんでした。

 

3.Excelの場合はこんな感じ。

Excelの場合は、Office2003以前以後のxls、xlsxの拡張子違いで指定方法が変わるみたいね。

Excelの場合はBookのフルパスをData Sourceに指定ね。

パラメータ最後の「HDR=YES」で指定範囲の1行目を項目行として使うかどうかの指定になります。

「HDR=NO」にすると、カラム名は左から順に「F1、F2、F3、F4・・・」ってなります。

セル結合とかでヘッダ行が読めなかった時のカラム名も「F1」とかになるよ。

 

Excelからデータ取得するSQLでは、テーブル名入れるFROM句でシート名とセル範囲を指定します。

「~ FROM [シート名$セル範囲] WHERE ~」

 

試しに実行してみたら、Excelアプリが起動せずに中のデータが読めちゃう訳よ・・・。

しかも、読み込みめちゃ早い。。。キモチイイ。

そしたら、このままファイル開かずに更新できたらめちゃファイル処理早くね?って思う訳ですよ。

そして、見事に引っかかって苦悩した訳ですよ。

 

4.データ型で詰まる。

SELECTはいいよね。

データ受け取るねーって時はRecordsetオブジェクトが型を吸収して持ってきてくれる訳だから、吐き出す時に「はい、はい、文字列文字列ー!」って変換してあげれば支障ない。

でも、レコードの更新をしようとしたら、エラーが出た訳ですよ。

「抽出条件でデータ型が一致しません。」

ググると、まぁよく見るエラーでSQLでフィールド値を指定する時に型を表現する括り文字が間違ってる時にでるエラーだったりする訳だ。

要するに、数字型のフィールドに対して文字列型を表す‘シングルクオーテーション’なんかで括ってるとこうなる訳だ。

でも、ちょっと待ってよ。

アンタ、Excelでしょ?ってさ。

Accessでテーブル作る時みたいに列に対して型定義してる訳じゃないしさ。

パッと見わかんない訳ですよ。何列が何のデータ型なのか。

だから、SELECTでRecordset取得してFieldsから1個づつFieldオブジェクト取得してNameとTypeプロパティを調べてみました。

Typeの値は数値で返ってしまうので、こちら(DataTypeEnum)を参考に見てきます。

そしたら、文字列のところは202(adVarWChar)となってるんですが、ここがまた曲者。

adVarWChar 202 null で終わる Unicode 文字列を示します。

要するに、Nullを許容するタイプな訳なんですが、セルの値を消そうとする際に「[セル名]=”(空白)」ってすると「抽出条件でデータ型が一致しません。」がでちゃうんですねー。

この謎に気付くのに時間がかかった・・・。

セルのデータを消す時は、「[セル名]=Null」って指定する必要があるんですねー

いやー、わかった時は気持ちよかった。。。

 

5.Accessの場合はこんな感じ。

Excelはいろいろドラマがあったから説明も長くなっちゃったケド、気を取り直して。

Accessを操作する場合の魔法詠唱(笑)、実はいろいろあるんですねぇ。

データベース(Access 等)に接続(ADO)

簡単に言うと普通に、拡張子がaccdbの場合は以下でOKみたい。

ちなみに、この「.CursorLocation = 3」っていうのでクライアントカーソルっていうのを指定していて、これが漏れてるとRecordCountが取得できないみたいねー。

 

で、「ハイハイ、後はAccessでDB操作なら任せてよ。( ^ω^ )」なーんて思ってたら、この

後も更に落とし穴にハマった訳ですよ。

 

6.LIKE演算子で詰まる。

なんだなんだ、全然レコード取れねーぞ。ってなった。

AccessでSQL文書く時はLIKE演算子のワイルドカードは「*」ですよね。

でも、「Microsoft.ACE.OLEDB.12.0」でDBに接続する時は、ワイルドカードが「%」になるんだって。。。

 

・・・ん?ちょっと待てよ。

ACE.OLEDBってExcelでも使ってたじゃーん。

Excelの時は表の大きさって大したことないからわざわざLIKE演算子で絞らなくてもいいかなーってボリュームだったから気付かなかっただけか。

 

っていうことで、いろいろ詰まりながらもなんとか自己解決しながらCSV、Excel、Accessにクエリをたたくっていうのを使えるようになりました。

なんか、そのうち使い方をうまいこと纏めてWinActorのスクリプト実行に組み込めるようにしていきたいですねぃ。

Leave a Reply

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

Related Post