前回、CSVの差分マッチングを高速化する為にADODB.Connectionを使ってみたんですよね。
これが、まぁ、すごく処理早くてよかったんですよね。
で、ちょっと調べたらCSVでもExcelでも、もちろんAccessでもクエリ実行ができるみたいですね。
いろいろいじってみて引っかかったとこのTipsを残しておきます。
１．コネクションオブジェクトを作る。
vbscriptの場合は、こうですね。
Set objADO = CreateObject("ADODB.Connection")
で、この作成したコネクションオブジェクトのオプションで、いろんなファイルをデータソースとして扱える様な使い方ができるみたいですね。
２．CSVの場合はこんな感じ。
FolderPath = "CSVファイルが配置されているフォルダのパス"
objADO.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & FolderPath & ";ReadOnly=1"
Open関数に「Driver={～}; DBQ=～」みたいな魔法詠唱(笑)を指定することでCSVをDBテーブルみたいに扱える様になるんですねぇー
ここで注意なのは、「Microsoft Text Driver」の場合は、DBQに指定するのはCSVファイルが配置されているフォルダまでのパスってことです。
CSVのファイル名を、SQLの中でテーブル名の様にしてFROM句に指定していきます。
前回の例の様に、同じフォルダにあればCSVファイル同士をJOINしたりUNION
したりできるんですねぇ。
「ReadOnly=1」ってなっちゃってる通り、とりあえずSELECTしか検証しませんでした。
３．Excelの場合はこんな感じ。
Excelの場合は、Office2003以前以後のxls、xlsxの拡張子違いで指定方法が変わるみたいね。
ExcelPath = "ExcelBookのフルパス" ' 拡張子 xls の場合 objADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ExcelPath & ";Extended properties=""Excel 8.0;HDR=YES;""" ' 拡張子 xlsx の場合 objADO.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelPath & ";Extended properties=""Excel 12.0;HDR=YES;"""
Excelの場合はBookのフルパスをData Sourceに指定ね。
パラメータ最後の「HDR=YES」で指定範囲の１行目を項目行として使うかどうかの指定になります。
「HDR=NO」にすると、カラム名は左から順に「F1、F2、F3、F4・・・」ってなります。
セル結合とかでヘッダ行が読めなかった時のカラム名も「F1」とかになるよ。
Excelからデータ取得するSQLでは、テーブル名入れるFROM句でシート名とセル範囲を指定します。
「～ FROM [シート名$セル範囲] WHERE ～」
' Sheet1のセルA1からZ50までの範囲を指定する場合 SELECT * FROM [Sheet1$A1:Z50] WHERE [F1]=1; ' Sheet1でセル範囲は１行目から表全体を指定する場合 SELECT * FROM [Sheet1$] WHERE [F1]=1;
試しに実行してみたら、Excelアプリが起動せずに中のデータが読めちゃう訳よ・・・。
しかも、読み込みめちゃ早い。。。キモチイイ。
そしたら、このままファイル開かずに更新できたらめちゃファイル処理早くね？って思う訳ですよ。
そして、見事に引っかかって苦悩した訳ですよ。
４．データ型で詰まる。
SELECTはいいよね。
データ受け取るねーって時はRecordsetオブジェクトが型を吸収して持ってきてくれる訳だから、吐き出す時に「はい、はい、文字列文字列ー！」って変換してあげれば支障ない。
でも、レコードの更新をしようとしたら、エラーが出た訳ですよ。
「抽出条件でデータ型が一致しません。」
ググると、まぁよく見るエラーでSQLでフィールド値を指定する時に型を表現する括り文字が間違ってる時にでるエラーだったりする訳だ。
要するに、数字型のフィールドに対して文字列型を表す'シングルクオーテーション'なんかで括ってるとこうなる訳だ。
でも、ちょっと待ってよ。
アンタ、Excelでしょ？ってさ。
Accessでテーブル作る時みたいに列に対して型定義してる訳じゃないしさ。
パッと見わかんない訳ですよ。何列が何のデータ型なのか。
だから、SELECTでRecordset取得してFieldsから１個づつFieldオブジェクト取得してNameとTypeプロパティを調べてみました。
Typeの値は数値で返ってしまうので、こちら（DataTypeEnum）を参考に見てきます。
そしたら、文字列のところは202(adVarWChar)となってるんですが、ここがまた曲者。
adVarWChar 202 null で終わる Unicode 文字列を示します。
要するに、Nullを許容するタイプな訳なんですが、セルの値を消そうとする際に「[セル名]=''(空白)」ってすると「抽出条件でデータ型が一致しません。」がでちゃうんですねー。
この謎に気付くのに時間がかかった・・・。
セルのデータを消す時は、「[セル名]=Null」って指定する必要があるんですねー
いやー、わかった時は気持ちよかった。。。
５．Accessの場合はこんな感じ。
Excelはいろいろドラマがあったから説明も長くなっちゃったケド、気を取り直して。
Accessを操作する場合の魔法詠唱(笑)、実はいろいろあるんですねぇ。
簡単に言うと普通に、拡張子がaccdbの場合は以下でOKみたい。
AccessPath = "Accessのファイルパス" objADO.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessPath objADO.CursorLocation = 3
ちなみに、この「.CursorLocation = 3」っていうのでクライアントカーソルっていうのを指定していて、これが漏れてるとRecordCountが取得できないみたいねー。
で、「ハイハイ、後はAccessでDB操作なら任せてよ。( ^ω^ )」なーんて思ってたら、この
後も更に落とし穴にハマった訳ですよ。
６．LIKE演算子で詰まる。
なんだなんだ、全然レコード取れねーぞ。ってなった。
AccessでSQL文書く時はLIKE演算子のワイルドカードは「*」ですよね。
でも、「Microsoft.ACE.OLEDB.12.0」でDBに接続する時は、ワイルドカードが「%」になるんだって。。。
・・・ん？ちょっと待てよ。
ACE.OLEDBってExcelでも使ってたじゃーん。
Excelの時は表の大きさって大したことないからわざわざLIKE演算子で絞らなくてもいいかなーってボリュームだったから気付かなかっただけか。
っていうことで、いろいろ詰まりながらもなんとか自己解決しながらCSV、Excel、Accessにクエリをたたくっていうのを使えるようになりました。
なんか、そのうち使い方をうまいこと纏めてWinActorのスクリプト実行に組み込めるようにしていきたいですねぃ。