いろいろ聞いてると、システムから取り出したCSVから必要な情報だけ他システムに連携したいけど、Excelでフィルタ編集しつつも、もっかいCSV化する時に数値とか電話番号の先頭が0落ちしないように幅広げて書式設定しなおす。
こんな操作を手順化して作業しているところも少なくない様だ。
直接CSV編集できるライブラリがあった方がいいよねってことで用意してみた。
細かい考え方はいいや、ソースコード頂戴！っていう方はページ最後へどうぞ。
このページのライブラリは、わんすけのライブラリにも追加しておきます。
１．CSVにSQL実行するという考え方。
ADODBというライブラリを使えば、CSVファイルに対してSQL実行ができます。
ADODBでCSVをロードする時には、ドライバをスクリプトの中で指定する必要があるのですが、今回は、「Microsoft Text Driver (*.txt; *.csv)」というのを使います。
Microsoft Text Driver を使ってCSVファイルをテーブルとして開く際には、いくつかの作法があるので纏めておきます。
１．ADODBのインスタンスを生成する。
|
1 |
Set objADO = CreateObject("ADODB.Connection") |
２．ドライバを指定してコネクションを開く
|
1 2 |
' 変数 BaseFolderにCSVファイルを配置しているフォルダパスを指定する。 objADO.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & BaseFolder & ";ReadOnly=1" |
ここで、CSVファイルが保存されているフォルダへのパスをしています。
オプションの最後の「ReadOnly=1」は、UPDATE文を使うときには「ReadOnly=0」にする感じです。
３．SQL文を実行する
|
1 2 |
' テーブルを指定する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を作る。」という実装方法になります。
３．実装例
上記を踏まえ、実装を考えるとこうなります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
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 を表示する