More than 3 years have passed since last update.

VBS 64bit Ado で CSV を DBQ でOpenして　集計してSelect Intoで別のCSVに入れる

Last updated at 2020-05-08Posted at 2020-05-02

VBS 64bit Ado で CSV を DBQ でOpenして　集計してSelect Intoで別のCSVに入れる
 VBS 64bit Ado で utf-8(BOMありなし両方) csv を DBQ で Open して Select Into で別のcsvに集計クエリも使用して入れる
 VBS ADO CSV のText Driver の64 bit Windows と32 Bit Windowsの違いと対応策
派生
今日のトリビア Notepadのバグ？を利用すると、最新のWin10(2019 May Later)はFilesystemObjectで UTF-8のテキストファイルが作成できる
 [VBS] ADODB.Streamで使える文字コード一覧の取得

Textドライバーが64bitと32bitで違う

ODBCのText DriverとExcelとを使ってCSVファイルをSQLで検索・抽出する　haneimo.hatenablog.com

注意しなければならないのは、、、

Excelが32bit版ならOS付属の32bit版のTextDriverを使う。
Excelが64bit版ならAccessに付属している64bit版のText Driverを使う。

ただし、64bit版のText DriverはAccessを買わなくても以下のページで無料で入手可能らしいです。（僕はAccessを買っているのでダウンロードしたランタイムは試してないです。）

ここでもう一つ気づくところがある。

vbsのadodb処理について（集計関数） 2019/3/26投稿　teratail.

Dim objADO
Dim strPath

strPath = "c:\csvdata\"

Set objADO = CreateObject("ADODB.Connection")
objADO.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & _
strPath & ";ReadOnly=0"

objADO.Execute _
"SELECT コンピューター名, Format([日時],'yyyy/mm/dd') AS 日付, Format(Sum([期間]),'hh:nn:ss') AS 利用時間計" & _
" INTO time_master.csv " & _
" FROM log_master1.csv where 操作種別 = '終了'" & _
" GROUP BY コンピューター名, Format([日時],'yyyy/mm/dd');"

objADO.Close
Set objADO = Nothing

64bit化

Microsoft Text Driver > Microsoft Access Text Driver

このVBSは32bitだ
Driver={Microsoft Text Driver (*.txt; *.csv)
上記のHatenablogのように
%systemroot%\system32\odbcad32.exe
これをアドレスバーに入れて起動させ
ユーザーDSNに登録する
32bitの方に登録してもうまく行かないようだ。

そして、これでコードを書き換えていくのだが、ここで一つ罠がある。

objADO.Open "Driver={Microsoft Access Text Driver (*.txt, *.csv)};DBQ=" & _
strPath & ";ReadOnly=0"

カッコの中がコンマ

細かい違いだが、これを間違うと動かない。というか気づくわけがない。普通はAccessと入れて以下のエラーを見て頭を抱えることになる。
[Microsoft][ODBC Driver Manager] データソース名および指定された既定のドライバーが見つかりません。
というエラーが生じる。

このサンプル、なぜみんなくもなく回答しているのか理解できない。
たとえばADOでの異なる種類のファイルへのSQL (FROM ... IN ... / INTO ... IN ...)スクリプトちょっとメモ
というような記事を参考にするとき、
call cnn_txt.Open("Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=<フォルダパス>;")
というのは
call cnn_txt.Open("Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=<フォルダパス>;")
と読み替えることになる。
もしかすると64bitと32bitに登録し、C:\Windows\SysWOW64\cscript.exeか32bit強制起動で動かせば良いのかも知れない。

その他接続文字列

＜ADO CSVでReadOnly=0と.AddNewを使いデータ追加＞三流君
ReadOnly = 0 更新可能 ReadOnly = 1 更新不可
この差はExcelを用いる場合にも現れる。PRB: ODBC を使用して Excel にアクセスする場合、"操作は更新可能なクエリを使用する必要があります"

また次のような欠点もある。必ずコンマ区切りになる。
テキストファイルへのADOでのCREATE/DROP/ALTER TABLEとSchema.ini スクリプトちょっとメモ

' [Note](Microsoft Text Driver では接続文字列でヘッダ行有無、区切り文字の指定不可)

ただし、ここはSchema.iniで指定できる。比較的大きな問題はやはりUTF-8を使うと安定しないことだと思う。
とりあえずここによると、
Provider=MSDASQL;Driver={Microsoft Access Text Driver (*.txt, *.csv)};
このように書いても良いので

objADO.Open "Provider=MSDASQL;Driver={Microsoft Access Text Driver (*.txt, *.csv)};DBQ=" & _
strPath & ";DefaultDir='c:\csvdata';ReadOnly=0"

こんな感じになる。DefaultDirのフォルダ名の末尾には円マークというかバックスラッシュがつかない

SQLを使ったCSVへのINSERT処理はADOよりDAOが高速！
SQL ServerへのCSVファイルinsert高速化
この複数のレコードをまとめてInsert IntoというのはAccessでもできる。今回は扱わなかったが、追記型のLOGをとるようなデータベースでは使う機会がある。
なお、ADOが遅いのはずっと言われている問題で、いまだにMicrosoftが解決できない問題である。

次の問題

テキストファイルのエンコード

相談者によると、
log_master1.csvは次のようになっているという
CSVファイル：log_master1.csv


コンピューター名,日時,操作種別,期間,端末機No,端末機名
PC1,2019/2/6 9:56,終了,0:00:05,1,吉田1
PC1,2019/2/6 9:56,開始,0:00:05,1,吉田1
PC1,2019/2/6 9:41,終了,0:02:23,1,吉田1
PC1,2019/2/6 9:39,開始,0:02:23,1,吉田1
PC1,2019/2/6 9:23,終了,0:05:08,1,吉田1
PC1,2019/2/6 9:18,開始,0:05:08,1,吉田1
PC2,2019/2/6 15:18,終了,2:19:53,15,田中2
PC2,2019/2/6 12:58,開始,2:19:53,15,田中2
PC2,2019/2/6 12:06,終了,0:00:44,15,田中2
PC2,2019/2/6 12:05,開始,0:00:44,15,田中2
PC2,2019/2/6 12:01,終了,1:27:49,15,田中2
PC2,2019/2/6 10:33,開始,1:27:49,15,田中2
PC1,2019/2/8 9:56,終了,1:00:05,1,吉田1
PC1,2019/2/8 9:56,開始,10:00:05,1,吉田1
PC1,2019/2/8 9:41,終了,1:02:23,1,吉田1
PC1,2019/2/8 9:39,開始,1:02:23,1,吉田1
PC1,2019/2/8 9:23,終了,1:05:08,1,吉田1
PC1,2019/2/8 9:18,開始,1:05:08,1,吉田1
PC2,2019/2/8 15:18,終了,2:19:53,15,田中2
PC2,2019/2/8 12:58,開始,2:19:53,15,田中2
PC2,2019/2/8 12:06,終了,1:00:44,15,田中2
PC2,2019/2/8 12:05,開始,1:00:44,15,田中2
PC2,2019/2/8 12:01,終了,1:27:49,15,田中2
PC2,2019/2/8 10:33,開始,1:27:49,15,田中2

このcsvファイルは~~utf-8~~ANSIである。
~~なぜなら上のvbscriptではテキストのコードが指定されていない。adoは指定を省略するとutf-8になる（昨日知った）~~
~~このため、このcsvファイルはutf-8となる。~~
~~最近のNotepadは標準がUTF-8(BOMなし）なので、迷わないかもしれないが。~~
と思ったが実はANSIであった。ANSIでないとSchemaを作ってもエラーになる。
これもまた普通はわからない。

~~次にこのcsvはそのままではなくダブルクォーテーションで包まなければならない。~~と思ったが、これも今までのセオリーと異なり、包むとエラーになる。
Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Text Driver] Data type mismatch in criteria expression.

こういう相談は英語、日本語ともタイトル（列名、カラム名、フィールド名）を布団でつつめと書いてある。
これは包まないほうがいい。
包むと、Accessでインポートして実験してみるときに邪魔になる。
この場合、空白が入る、予約語はうまくいかないが、上記のサンプルを使用する限りでは問題がない。

置換の仕方

Notepadで置換するとき

`,` `","`
`PC` `"PC`
`田1` `田1"`
`中2` `中2"`

とすると、以下のように変わるが、今回は上記のほうが良い。

"コンピューター名","日時","操作種別","期間","端末機No","端末機名"
"PC1","2019/2/6 9:56","終了","0:00:05","1","吉田1"
"PC1","2019/2/6 9:56","開始","0:00:05","1","吉田1"
"PC1","2019/2/6 9:41","終了","0:02:23","1","吉田1"
"PC1","2019/2/6 9:39","開始","0:02:23","1","吉田1"
"PC1","2019/2/6 9:23","終了","0:05:08","1","吉田1"
"PC1","2019/2/6 9:18","開始","0:05:08","1","吉田1"
"PC2","2019/2/6 15:18","終了","2:19:53","15","田中2"
"PC2","2019/2/6 12:58","開始","2:19:53","15","田中2"
"PC2","2019/2/6 12:06","終了","0:00:44","15","田中2"
"PC2","2019/2/6 12:05","開始","0:00:44","15","田中2"
"PC2","2019/2/6 12:01","終了","1:27:49","15","田中2"
"PC2","2019/2/6 10:33","開始","1:27:49","15","田中2"
"PC1","2019/2/8 9:56","終了","1:00:05","1","吉田1"
"PC1","2019/2/8 9:56","開始","10:00:05","1","吉田1"
"PC1","2019/2/8 9:41","終了","1:02:23","1","吉田1"
"PC1","2019/2/8 9:39","開始","1:02:23","1","吉田1"
"PC1","2019/2/8 9:23","終了","1:05:08","1","吉田1"
"PC1","2019/2/8 9:18","開始","1:05:08","1","吉田1"
"PC2","2019/2/8 15:18","終了","2:19:53","15","田中2"
"PC2","2019/2/8 12:58","開始","2:19:53","15","田中2"
"PC2","2019/2/8 12:06","終了","1:00:44","15","田中2"
"PC2","2019/2/8 12:05","開始","1:00:44","15","田中2"
"PC2","2019/2/8 12:01","終了","1:27:49","15","田中2"
"PC2","2019/2/8 10:33","開始","1:27:49","15","田中2"

Schema.ini

Schema.ini ファイル (テキストファイルドライバー)
CharsetがANSIとOEMしかないため、UTF-8は失敗することが多い。
ここでANSIにしているため、スクリプトで指定しなくてもANSIになっていると考えられる。
また、UTF-8のcsvにしてSchemaのCharset=OEMするとフィールド名が文字化けしてフィールドが途中から消える。
なお、Schema.iniがANSIなのはすでに既知のとおり。

[log_master1.csv]
ColNameHeader=True
Format=CSVDelimited
CharSet=ANSI
MaxScanRows=0
Col1=コンピューター名 TEXT Width 10
Col2=日時 Date
Col3=操作種別  TEXT Width 10
Col4=期間 Date
Col5=端末機No Long
Col6=端末機名 TEXT Width 10

Select　Into

INSERT INTO ステートメント (Microsoft Access SQL)

ドキュメント Office クライアントの開発 Access Access デスクトップデータベースリファレンス Microsoft Access SQL リファレンスデータ操作言語 INSERT INTO ステートメント
1 つのテーブルに 1 つのレコードまたは複数のレコードを追加します。追加クエリとも呼ばれます。

複数レコード追加クエリ

INSERT INTO target [(field1[, field2[, …]])] [IN externaldatabase] SELECT [source.]field1[, field2[, …] FROM tableexpression

単一レコード追加クエリ:

INSERT INTO target [(field1[, field2[, …]])] VALUES (value1[, value2[, …])

これは既存のテーブルがないと使えない

SELECT.INTO ステートメント (Microsoft Access SQL)

2018/10/18

適用先: Access 2013、Office 2013

テーブル作成クエリを作成します。
構文

SELECT field1[, field2[, …]] INTO newtable [IN externaldatabase] FROM source

SELECT...INTO ステートメントには、次の指定項目があります。

パーツ	説明
field1、field2	新しいテーブルにコピーするフィールドの名前です。
newtable	作成するテーブルの名前。名前付け規則に従った名前を指定します。引数 newtable と同じ名前のテーブルが既にある場合は、トラップ可能なエラーになります。
externaldatabase	外部データベースのパス。パスの記述方法については、「IN句」を参照してください。
source	レコードの選択元になる既存テーブルの名前です。このテーブルまたはクエリは 1 つでも複数でも構いません。

注釈
テーブル作成クエリを使用し、レコードをアーカイブしたり、テーブルのバックアップコピーを作成したり、別のデータベースにエクスポートするか、データを一定期間表示するレポートの基礎として利用するためのコピーを作成したりできます。たとえば、同じテーブル作成クエリを毎月実行し、地域別の月間売上レポートを作成できます。

新規テーブルに主キーを設定する場合があります。テーブル作成クエリで作成したテーブルのフィールドは、クエリの元になるテーブルのフィールドのデータ型とフィールドサイズを継承しますが、それ以外のフィールドプロパティやテーブルプロパティは継承しません。
既存のテーブルにデータを追加するには、SELECT...INTO ステートメントではなく INSERT INTO ステートメントを使用して追加クエリを作成してください。
どのレコードが選択されるかをあらかじめ確認する場合は、テーブル作成クエリを実行する前に、同じ選択条件を使用する SELECT ステートメントを実行してその結果を調べてください。

とここでエラー

パラメーターの数が少なすぎます。
c:.....vbs(XX, XX) Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Text Driver] Too few parameters. Expected 4.
[Microsoft] [ODBC Text Driver] Too few parameters. Expected:1
これはWhere [Field]=Value'`
Import error: ERROR [07002] [Microsoft][ODBC Text Driver] Too few parameters. Expected #
これはフィールド名があっているか確認すべきというもの
今回のテストで、このほか、

csvの出力は同じフォルダの場合はファイル名のみでよいので、同じフォルダに出力する
文字コードがあっているか、単純な選択クエリで全部返したときうまく出力されているかで確認する(ANSIで揃えたほうが良い）
必ずしもすべてデータがダブルクォーテーションで包まれていないほうが良い。集計されないときは外してみる
Schema.iniの内容に問題がないか。フィールド名がダブルクォーテーションで包まれているか。

ということがチェック項目として上がってきた。ここで悩んだが、Select * Into csv From csvという単純な出力をやってみると、データベースが壊れていた。 UTF-8 Schema.iniのCharsetがOEMで出力させた一部がこちら

コンピューター名,日時,操作種別,期間,端末機No,端末機名
PC1,2019/2/6 9:56,終了,0:00:05,1,吉田1
PC1,2019/2/6 9:56,開始,0:00:05,1,吉田1
PC1,2019/2/6 9:41,終了,0:02:23,1,吉田1

Schema.ini

[log_master1.csv]
ColNameHeader=True
Format=CSVDelimited
TEXTacterSet=OEM
MaxScanRows=0
Col1="[コンピューター名]" TEXT Width 10
Col2="[日時]" DateTime
Col3="[操作種別]"  TEXT Width 10
Col4="[期間]" DateTime
Col5="[端末機No]" Long
Col6="[端末機名]" TEXT Width 10

"コンピューター吁E日晁E操作種別","期間","端末機No","端末機名","F5"
"PC1",2019/02/06 9:56:00,"終亁E0:00:05",1,"吉田1"
"PC1",2019/02/06 9:56:00,"開姁E0:00:05",1,"吉田1"
"PC1",2019/02/06 9:41:00,"終亁E0:02:23",1,"吉田1"
"PC1",2019/02/06 9:39:00,"開姁E0:02:23",1,"吉田1"
"PC1",2019/02/06 9:23:00,"終亁E0:05:08",1,"吉田1"
"PC1",2019/02/06 9:18:00,"開姁E0:05:08",1,"吉田1"
"PC2",2019/02/06 15:18:00,"終亁E2:19:53",15,"田中2"

そこでデータベースをANSIに戻した。
それでも無理だったので、データベースに貼ったダブルクォーテーションを外した。

その結果

"コンピューター名","日付","利用時間計"
"PC1","2019/02/06","00:07:36"
"PC1","2019/02/08","03:07:36"
"PC2","2019/02/06","03:48:26"
"PC2","2019/02/08","04:48:26"

人にはダブルクォーテーションをはずさせて、自分はつけるかね。。。

64bit VBS DBQ 集計クエリ Select INTO　CSV

このの相談は高度すぎる。また非常に珍しいサンプルである。

64bitでcsvファイルを登録するときはMicrosoft Access Text Driverを使い、カッコの中に注意する
データベースはANSIが安定
Accessがなくても使用可能。（コンポーネントのインストールは必要）
今まではダブルクォーテーションをすべてかけていたほうが安全だったが、今回はエラーの原因になった。
Schemaを使う。しかしSchemaができないときは削除しない（もとの記事では削除すると言っていたが、Schema.oldなどで動く分を作っておき、削除されたら復活させる形式がよいと考えられる。）
Select Intoを使う
元記事のVBSは`Format(Sum([期間]),'hh:nn:ss')`とnnを使っている。[Format関数で「hh:nn:ss」と「hh:/mm:ss」（エクセルVBA）](http://blog.livedoor.jp/katsuyausami/archives/52081700.html)によるとこちらが正しいとのこと。
集計とはSumで囲んでいる。今までは出力してからExcelで合算していたが、Sum集計クエリを使用するSampleが実現した。
[Access VBA CSVのインポートとエクスポートをSchema.iniとクエリで行う Import and Export Text DB file with Query](https://qiita.com/Q11Q/items/9c93552a0efd49d06fc4) と比較すると64bitを意識してMicrosoft Access Text Diverを使い、かつ集計している点が異なる。今までこのパターンは動かず避けていたのだが、このサンプルにより可能となった。

フォルダーのファイルの構成

すべて同一のフォルダに
time.master.csvは削除されるので、困る場合は別に保存する。それ以外はあってもなくても気にしなくてよい

C:.
    log_master1.csv
    schema.ini
    test.accdb
    testadogroupby.vbs
    time_master.csv

64bit VBScriptのコード

' 64bit 前提
' 32bit の場合 Microsoft Text Driver (*.txt; *.csv)を使用する
' 事前に登録が必要
' Schema.iniの作成が必要
' 文字コードはすべてANSIを使用すること
Const testPath = "c:\csvdata\"
Dim objADO
Dim strPath
Dim strDelFile5, strfile5
strPath = testPath
strfile5 = "schema.ini"
strDelFile5 = strPath & strfile5
' 出力するCsvファイルがあるとエラーになるので削除する
' Schema.iniは再度作成されないときもあるので、削除しない。このパターンでは出力したcsvはSchema.iniに自動的に追加されていないため、削除する必要がないと考えられる。
With CreateObject("Scripting.FileSystemObject")
If .FileExists(testPath & "time_master.csv") Then
 .DeleteFile testPath & "time_master.csv"
End If
End With

Set objADO = CreateObject("ADODB.Connection")
'DBQでCSVファイルに接続して開くDefaultDirは省略可能。記載するときはフォルダ名の末尾に注意
objADO.Open "Provider=MSDASQL;Driver={Microsoft Access Text Driver (*.txt, *.csv)};DBQ=" & _
strPath & ";DefaultDir='c:\csvdata';ReadOnly=0"

'単純な出力
'objADO.Execute "Select * INTO time_master.csv From log_master1.csv;"

'今回の分、時間を集計するFormat(Sum([期間]),'hh:nn:ss')はFormat(Sum([期間]),'hh:mm:ss')
"SELECT [コンピューター名], Format([日時],'yyyy/mm/dd') AS [日付], Format(Sum([期間]),'hh:mm:ss') AS [利用時間計]" & _
" INTO time_master.csv" & _
" FROM log_master1.csv WHERE [操作種別] = '終了'" & _
" GROUP BY [コンピューター名], Format([日時],'yyyy/mm/dd');" 

objADO.Close
Set objADO = Nothing

補足 AccsessのアクションクエリでSelect Intoを使ったとき

CSVをリンクテーブルにして別のテーブルに出力することはできる。
もちろん、アクションクエリでCSVに出力することはできない。
しかし、同じフォルダで、Schema.iniを自作すると、Excelとは違ってこのAccessのSQLとさほど変わらない。

SELECT [端末機名], Format([日時],'yyyy/mm/dd') AS 日付, Format(Sum([期間]),'hh:nn:ss') AS 利用時間計 INTO T_time_master
FROM log_master1
WHERE 操作種別 = '終了'
GROUP BY [端末機名], Format([日時],'yyyy/mm/dd');

VBSCRIPTのSQL。大文字を調整したが、csvがつく以外はほぼ一緒である。
Select Into の出力先の指定方法は、INに準じるとしているが、InではテキストやCSVについて言及がない。
しかし、言及がないのは、Access側にもテーブル名の規則があり、制限があるものの、Schema.iniがあり、すべて同じフォルダーで、ファイルがANSI形式であれば、SQLがほぼ一緒となるからだろう。
これは、まずAccessでうまくテーブルが作られるか試してからCSVに移行すれば良いということがわかる。

SELECT [コンピューター名], Format([日時],'yyyy/mm/dd') AS [日付], Format(Sum([期間]),'hh:mm:ss') AS [利用時間計] INTO time_master.csv
FROM log_master1.csv
Where [操作種別] = '終了' 
GROUP BY [コンピューター名], Format([日時],'yyyy/mm/dd');

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

VBS 64bit Ado で CSV を DBQ でOpenして 集計してSelect Intoで別のCSVに入れる

Textドライバーが64bitと32bitで違う

64bit化

Microsoft Text Driver > Microsoft Access Text Driver

カッコの中がコンマ

その他接続文字列

テキストファイルのエンコード

置換の仕方

Schema.ini

Select Into

INSERT INTO ステートメント (Microsoft Access SQL)

複数レコード追加クエリ

とここでエラー

64bit VBS DBQ 集計クエリ Select INTO CSV

フォルダーのファイルの構成

64bit VBScriptのコード

補足 AccsessのアクションクエリでSelect Intoを使ったとき

VBS 64bit Ado で CSV を DBQ でOpenして　集計してSelect Intoで別のCSVに入れる

Select　Into

64bit VBS DBQ 集計クエリ Select INTO　CSV