いろいろ聞いてると、システムから取り出したCSVから必要な情報だけ他システムに連携したいけど、Excelでフィルタ編集しつつも、もっかいCSV化する時に数値とか電話番号の先頭が0落ちしないように幅広げて書式設定しなおす。
こんな操作を手順化して作業しているところも少なくない様だ。
直接CSV編集できるライブラリがあった方がいいよねってことで用意してみた。
細かい考え方はいいや、ソースコード頂戴！っていう方はページ最後へどうぞ。
このページのライブラリは、わんすけのライブラリにも追加しておきます。
１．CSVにSQL実行するという考え方。
ADODBというライブラリを使えば、CSVファイルに対してSQL実行ができます。
ADODBでCSVをロードする時には、ドライバをスクリプトの中で指定する必要があるのですが、今回は、「Microsoft Text Driver (*.txt; *.csv)」というのを使います。
Microsoft Text Driver を使ってCSVファイルをテーブルとして開く際には、いくつかの作法があるので纏めておきます。
１．ADODBのインスタンスを生成する。
Set objADO = CreateObject("ADODB.Connection")
２．ドライバを指定してコネクションを開く
' 変数 BaseFolderにCSVファイルを配置しているフォルダパスを指定する。
objADO.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & BaseFolder & ";ReadOnly=1"
ここで、CSVファイルが保存されているフォルダへのパスをしています。
オプションの最後の「ReadOnly=1」は、UPDATE文を使うときには「ReadOnly=0」にする感じです。
３．SQL文を実行する
' テーブルを指定するFROM句にCSVファイル名を指定する。
objADO.Execute("SELECT * FROM [sample.csv] WHERE [No]=1")
コネクション開くときにフォルダパス指定してるので、FROM句でファイル名だけ指定すればOKってこと。
同じフォルダに複数CSVファイルを配置すれば、INNER JOINとかも使えます。
２．取り扱いの注意点
まず、データソースとかドライバーのお話。
仮に、ADODB使ったスクリプトをvbsファイルとして保存して実行した時にこんなエラーが出てしまうケースがあります。
[Microsoft][ODBC Driver Manager] データソース名および指定された既定のドライバーが見つけりません。
コード：80004005
実際、このエラーがでないでvbsとしても実行できる様に環境をセットアップする方法もあるのですが、そこはすこし話が長くなるので興味があればページの最後に参照リンク付けておいたので調べてみて下さい?
ただ、WinActorのスクリプト実行ノードで使う分にはWinActor自体がもともとDB接続とかできるようにセットアップされた環境になってるのか、32bitのCScript.exe使ってるのかわかんないけど、上記のエラーはでないと思います?
で、このMicrosoft Text Driverなんだけども、SQLでなんでもできるかっていうと実はDELETE文はサポート外だったりします。
実際に使うとこんなエラーがでます。
[Microsoft][ODBC テキスト Driver] この ISAM では、リンク テーブル内のデータを削除することはできません。
なので、この記事のタイトルでは【抽出・削除】としてますが、厳密には削除の処理を実現したい場合には「削除後に残したいレコードの抽出条件でSELECT文を書いて新しいCSVを作る。」という実装方法になります。
３．実装例
上記を踏まえ、実装を考えるとこうなります。
Dim BaseFolder, objFS, SrcFN
Dim SrcTxt, OutPutFile, OutPutStr
Set objFS = CreateObject("Scripting.FileSystemObject")
SrcFN = !CSV元ファイル!
partWHERE = !WHERE句!
OutPutFile = !CSV出力ファイル!
' フルパスから格納フォルダパスを取得
BaseFolder = objFS.GetParentFolderName(SrcFN)
If Not objFS.FileExists(SrcFN) Then
Err.Raise 404, "", "「" & SrcFN & "」ファイルが見つかりません。"
WScript.Quit
End If
' フルパスからファイル名のみを抽出
SrcFN = objFS.GetFileName(SrcFN)
Set objFS = Nothing
strSQL = "SELECT * FROM [" & SrcFN & "] WHERE " & partWHERE
Set aq = New QueryADO
Set rec = aq.GetRecordFromCSV(BaseFolder, strSQL)
For Each fld In rec.Fields
OutPutStr = OutPutStr & fld.Name & ","
Next
OutPutStr = OutPutStr & vbNewLine
Do While Not rec.EOF
lnStr = ""
For Each fld In rec.Fields
If Len(lnStr) > 0 Then lnStr = lnStr & ","
If InStr(rec(fld.Name).Value, ",") > 0 Then
lnStr = lnStr & """" & rec(fld.Name).Value & """"
Else
lnStr = lnStr & rec(fld.Name).Value
End If
Next
OutPutStr = OutPutStr & lnStr & vbNewLine
rec.MoveNext
Loop
Set aq = Nothing
Write_TEXT OutPutStr, OutPutFile
WScript.Quit
'■ メイン処理ここまで ■ 以下、呼び出し関数 ■
' ADOでCSVにSQL発行
Class QueryADO
Private objADO
Private objRS
Public Sub Class_Initialize
Set objADO = CreateObject("ADODB.Connection")
End Sub
Public Sub Class_Terminate
On Error Resume Next
objRS.Close
Set objRs = Nothing
objADO.Close
Set objADO = Nothing
On Error GoTo 0
End Sub
Function GetRecordFromCSV(BaseFolder, pSQL)
objADO.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & BaseFolder & ";ReadOnly=1"
Set GetRecordFromCSV = objADO.Execute(pSQL)
End Function
End Class
Function Write_TEXT(ContentStr, FilePath)
Dim objFS, objTXT
On Error Resume Next
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTXT = objFS.OpenTextFile(FilePath, 2, True)
objTXT.Write ContentStr
objTXT.close
Set objTXT = Nothing
Set objFS = Nothing
If Err.Number > 0 Then
Write_TEXT = False
Err.Clear
Else
Write_TEXT = True
End If
End Function
読み取り対象のCSVがあるかチェックして、SQL文をビルドして実行してテキスト出力と。
４．適用イメージはこんな感じかな
備考
CSVに対してSELECT文のSQL実行を行い、結果を別ファイルとしてCSV出力を行います。
WHERE句には、 [項目名]=値 の様な形で抽出条件を指定します。
レコードを削除したい場合は、残したいレコードを選択するWHERE句を指定して下さい。
あー・・・、あとSQL知らない人もいるのか?
そのうちコピペライブラリのコンテンツにSQL追加しようかな?
※ 参考リンク ※
64bit OSでADODBテキストドライバを使う
64 ビット版の Windows で、ODBC アドミニストレーター ツールが 32 ビットと 64 ビット両方のユーザー DSN を表示する