VBS 64bit ANSI Ado で CSV を DBQ でOpenして 集計してSelect Intoで別のCSVに入れる
でUTF-8は安定しないまま終わっていた。
数多くのサイトが、実際に挫折したまま終わっていることが多い。
結論からいうとできないことはないのだが、ANSIよりはるかに難しい。
補足:データベース本体はUTF-8N(UTF-8BOMなし) でもUFT-8（BOMあり）でも動きます
少なくとも以下のSampleとSchema.ini、odbcad32に登録すると、問題なく読み込まれ出力されました。
ただし、出力結果はUFT-8N つまりUTF-8BOMなしになります。
そこで今気がついたのですが、これでBOMの有無にかかわらずBOMなしにすることがSelect Intoでできることになります。
（ただし、日付等はFormatする必要性があります）
補足その2:Notepad.exeの特性について
Windows10 May 2019 以降のNotepad.exeはUTF-8(BOMなし）が標準です。
このため、下記のバッチファイルは英文のみしかないためANSIで保存していてもUTF-8と表示されます。そしてUTF-8で全角文字を入れて保存すると、本当にUTF-8になってしまいます。
上書き保存するときは、ANSIで保存することを強制してください。
補足その3:各ファイルの文字コードについて
cmd.exe コマンドプロンプトがchcp 932の時
なにも指定しない状態だと、chcp932になっている
| 拡張子 | 成功する文字コード | 失敗する文字コード |
|---|---|---|
| bat | ANSI UTF-16LE UTF-8 | UTF-8(BOMあり） ・・'@' は、内部コマンドまたは外部コマンド、操作可能なプログラムまたはバッチ ファイルとして認識されていません。それでもバッチファイルは動いて処理できる部分は処理しようとする。 |
| vbs | ANSI UTF-16LE | UTF-8 |
| Schema.ini | ANSI | UTF-8 UTF-16LE 出力ファイルを開くときにエラー（CSVが作成されない) Schema.iniが破壊 Export 以下のテーブルが消失して文字化け |
| csv | UTF-8 BOM UTF-8 No BOM |
cmd.exe を chcp 65001にした時
| 拡張子 | 成功する文字コード | 失敗する文字コード |
|---|---|---|
| bat | ANSI UTF-16LE UTF-8 UTF-8(BOMあり） | |
| vbs | ANSI UTF-16LE | UTF-8(MS明朝にない絵文字を取り扱うと失敗する） |
| Schema.ini | ANSI UTF-16LE | UTF-8 出力ファイルを開くときにエラー（CSVが作成されない) Schema.iniが破壊 Export 以下のテーブルが消失して文字化け |
| csv | UTF-8 BOM UTF-8 No BOM |
CHCPとは
現在のコード ページ番号を表示または設定します。
CHCP [nnn] nnn コード ページ番号を指定します。
現在のコード ページ番号を表示するときは、パラメーターを指定せずに CHCP と入力してください。
CHCPnの解説は「現在の」となっているが、「Activeな」という意味。その証拠にコマンドプロンプト画面をとじて起動してCHCPを実行すると日本語版のWindows10では932に戻ってしまう。
batファイル補足
ここまでを整理すると
- batファイルは基本的にANSIでANSIで使う文字のみに限定したほうが良い
- 実験時に
`@REM アANSI saved bat file only successed. Notepad.exe Windows 10 May 2019 Later May showst his bat file utf-8`
のように注釈で全角文字を使うとUTF-8 BOMはエラーが表示された。 - つまりバッチファイルを起動してからchcpを実行してもだめなので、これを回避するため `chcp 65001`を実行してから、バッチファイルを起動する。（しなくても、コメント欄がエラーになるだけで最後まで処理される。
- Batファイルの中にchcp 65001を書いてもコメント行は文字化けが発生して上記のエラーが表示される。
- また、この時、エラー表示は英語になる。無効なクエリ > Invailed Query
UTF-8で保存したバッチファイルを実行すると文字列がコマンドとして認識されてしまう
バッチファイルの文字コードは？ 2008/12/25
シフトJIS(932) だけ？ いいえ、そんなことはありません。例えば、EUC-JP(20932) も可能です。しかし、Unicode(1200)、UTF-7(65000)、UTF-8(65001)、JIS(50220) はダメです。
なので、実際上？は、シフトJIS(932) だけのようなものです。
これらコードページのバッチファイル可否は、%SystemRoot%System32\C_コードページ.NLS の有無によるようです。
CMD.EXEで実行して確認してみた。（100個以上あるので省略）
Dir "%Systemroot%\System32\C_*.NLS"
これで見ると、当方の環境では100ほどあり、最大が30000未満だった。
つまり、65001が存在しないため、UTF-8のbatファイルは作れないし、作った場合は安定しない。
コマンドプロンプト起動時、自動的に文字コードをUTF-8にして日本語もちゃんと表示できるようにする方法
これも絵文字を扱わない（MSゴシックにあるユニコードの文字に限定する）場合は通用する。
2014年当時はこれでよく、業務であればいまでも通じるが、絵文字が入る場合には通用しない。
Code Page Identifiers コードページ識別子
2018/05/31
Windows Application の国際化の下にある
Windows Apps Win32 Desktop Technologies Desktop App User Interface Internationalization
ANSI コード ページは、コンピュータによってなにを指しているかが異なりうるため、単独のコンピュータで変更は問題がないようでも、結果的にデータの破損が発生する場合があります。最も一貫した結果を得るには、アプリケーションは特定のコード ページではなく、UTF-8 や UTF-16 などの Unicode を使用すべきです。
ANSI code pages can be different on different computers, or can be changed for a single computer, leading to data corruption. For the most consistent results, applications should use Unicode, such as UTF-8 or UTF-16, instead of a specific code page.
1200 utf-16 Unicode UTF-16, little endian byte order (BMP of ISO 10646); available only to managed applications マネージ アプリケーションでのみ使用できます。
1201 unicodeFFFE Unicode UTF-16, big endian byte order; available only to managed applications
するとバッチファイルは1200 UTF-16になりそうだが、
chcp 1200
はエラーになり、決め手に欠いている。ここにある記述と現在のWindows10は整合していない。
コンソール（cmd.exe）の文字コードを UTF-8 に miau's blog
posted at 07:06:53 on 2009-03-23 by miau - Category: General No Trackbacks - Permalink
windows7 コマンドプロンプトのフォント一覧にレジストリで追加したフォントが表示されない
こちらの PC で、chcp 65001 を実行してプロパティのフォントを確認したところ、ラスターフォント以外に「Consolas」、「Lucida Console」というフォントも表示されて選択できました。
uchiyasu さんの PC ではこれらのフォントはプロパティでも表示されていますか?
(もし、「Consolas」のキーを変更して「MSゴシック」にしていたら「Consolas」は無いかもしれません)
いちおうLucidaについて確認してみると、
Windows 10 によってインストールされるフォント
ADOBEのヘルプのほうが充実しているが、標準でインストールされ、
https://www.fileformat.info/info/unicode/font/lucida_console/list.htm
収録文字も多い。しかし、絵文字はない。上記のフォントをインストールする状況はWindows7時代のもので、Windows10は変わっている。おそらくLucidaと同じレベルでMSゴシックもフォローしているだろう。つまりこのMSゴシックはMS UI ゴシックに近いはずだ。絵文字はGithubの2018のスレッドでも今後の課題のようだ。
これがWindows10の現在の限界だと言える。
Batファイルの文字コードのセカンドベストな選択方法
とするとbatファイルについて現実的な選択は
- chcp 932ではANSIを使用する。
- chcp 65001ではUTF-8が使用できるがUTF-16LEのほうが確実。UTF-8を扱うときは特にこちらに特化してバッチを組むほうがより安定する。若しくはVBSをwsfで書く。
- どちらでも絵文字は直接扱うことができない。
- Windows10ではフォントはインストールしないが、chcpの効果が一時的なものなのは同じ。
- 現時点ではセカンドベストな選択しかないと考えられる
VBSファイル補足
UTF-8でも動くが、おそらくWindowsの標準であるMS明朝にない絵文字を使うと失敗する。
また拡張子をwsfにすると、utf-8で記述できる。
<?XML version="version" standalone="yes" encoding="UTF-8"?>
<package>
<job>
<script language="VBScript">
<![CDATA[
WScript.stdout.writeLine "ここにコードを記載。保存はUTF-8"
]]>
</script>
</job>
</package>
JScript(Windows スクリプト ファイル)をUTF-8で書く
しかしインクルード（呼び出し先）がUTF-8だと失敗する。ここではBOM付きとなっているがUTF-16LEがよい。
log_master1.csvを変更
BOMがあった場合以下のlog_master1.csvをメモ帳で開き、UTF-8(BOMつき）に変換します。（Windows10のメモ帳(notepad.exe)は最新版はこのような機能が可能になっています。
メモ帳の文字コード既定値がUTF-8に、Windows 10「May 2019 Update」
あとは以下のコードで稼働
今回のテーマの確認
- Windows 64bit(つまり現在市販のWindows10 ほぼすべて）で
- テストのフォルダ名を仮に`c:\csvtest\`として
- UTF-8 BOMなし、CSVファイルを
- Access 2016 をもっていない場合には https://www.microsoft.com/en-us/download/details.aspx?id=54920 をダウンロード、インストールし、
- `%SystemRoot%\System32\oedbad32.exe`で
- Microsoft Access Test Driverで登録し（これをbatで行う）
- Schema.iniを作成する。
- 何度も繰り返すのでBatファイルを作り、エクスプローラーで設定のフォルダを開き、Schema.iniを含めてファイルを開く。
- これでVBScriptを起動し、SQLで集計する。あわせて別のcsvも作る。
- 最後にすべてのファイルをメモ帳で開き、終了する。
- 実験が終わったら、データベースのCSVを削除する前に、ODBCアドミニストレータのユーザーDSNのcsvを削除する。
- Access2010再頒布コンポーネントではなく、Access2016を使う
- 64bit では Microsofot Access Text Driverを使う
- batで登録できるようにする
- 変換できるCSVの作り方
- Schema.iniのコツ
- Microsoftの解説が間違っていることを知る
ODBCアドミニストレーターへの登録 Batファイル
手作業でしなくても良い
CONFIGSYSDSNに対してCONFIGUSERDSNかと思ったら、エラー表示でCONFIGDSNだった
log_master1.csvが c:\csvtest にあるとする
cd /d c:\
cd /d csvtest
cd /d %~dp0
set WORK_DIR=%~dp0
odbcconf /Ld %WORK_DIR%log_test.txt /A {CONFIGDSN "Microsoft Access Text Driver (*.txt, *.csv)" "DSN=log_master1.csv;DBQ=C:\csvtest\log_master1.csv"}
管理者権限が必要かもしれない。
なお、削除は手動のほうが良いようだ。
UTF-8 BOMなしCSVの場合のポイント
すべてダブルクォーテーションで囲む
フィールド名に空白がある場合まで実験していないが、結論からいうとまず全部ダブルクォーテーションで囲む
[]角カッコは使わない
"[これは だめ]"
csvファイルのヘッダー（フィールド名）はスペースがあっても角カッコ（ブラケット）は使わない。
フィールド名の一部とみなされるが、禁止文字のためバグになる。
SQLで使うからと言ってフィールド名で囲むとおかしくなる。
これはSchemaで指定してもだめ。
CsvとSchema.iniは空白があるばあい "二重 引用符"のようにする
特に数字、日付は桁を揃える
おそらく桁がずれると文字化けする。
たとえば前回は
"PC1","2019/2/06 9:56","終了","0:00:05","1","吉田1"
"PC2","2019/2/06 15:18","終了","2:19:53","15","田中2"
ということも許された。しかし今回は厳密に桁を合わせる。
どうもこうしないとどこかで文字化けを起こすらしい。
そこで読み誤りの余地がないよう、厳密にダブルクォーテーションで覆う。
特に日付は最新の注意をもって合わせる。
"コンピューター名","日時","操作種別","期間","端末機No","端末機名"
"PC1","2019/02/06 09:56","終了","00:00:05","01","吉田1"
"PC1","2019/02/06 09:56","開始","00:00:05","01","吉田1"
"PC1","2019/02/06 09:41","終了","00:02:23","01","吉田1"
"PC1","2019/02/06 09:39","開始","00:02:23","01","吉田1"
"PC1","2019/02/06 09:23","終了","00:05:08","01","吉田1"
"PC1","2019/02/06 09:18","開始","00:05:08","01","吉田1"
"PC2","2019/02/06 15:18","終了","02:19:53","15","田中2"
"PC2","2019/02/06 12:58","開始","02:19:53","15","田中2"
"PC2","2019/02/06 12:06","終了","00:00:44","15","田中2"
"PC2","2019/02/06 12:05","開始","00:00:44","15","田中2"
"PC2","2019/02/06 12:01","終了","01:27:49","15","田中2"
"PC2","2019/02/06 10:33","開始","01:27:49","15","田中2"
"PC1","2019/02/08 09:56","終了","01:00:05","01","吉田1"
"PC1","2019/02/08 09:56","開始","10:00:05","01","吉田1"
"PC1","2019/02/08 09:41","終了","01:02:23","01","吉田1"
"PC1","2019/02/08 09:39","開始","01:02:23","01","吉田1"
"PC1","2019/02/08 09:23","終了","01:05:08","01","吉田1"
"PC1","2019/02/08 09:18","開始","01:05:08","01","吉田1"
"PC2","2019/02/08 15:18","終了","02:19:53","15","田中2"
"PC2","2019/02/08 12:58","開始","02:19:53","15","田中2"
"PC2","2019/02/08 12:06","終了","01:00:44","15","田中2"
"PC2","2019/02/08 12:05","開始","01:00:44","15","田中2"
"PC2","2019/02/08 12:01","終了","01:27:49","15","田中2"
"PC2","2019/02/08 10:33","開始","01:27:49","15","田中2"
SQL ServerのSchema.iniとAccessのSchema.iniは違う
SQL ServerのSchema.ini
ニセモノ
Docs SQL ODBC ODBC 詳細設定 Microsoft Open Database Connectivity (ODBC) Microsoft 提供の ODBC ドライバー デスクトップのデータベースの Microsoft ODBC ドライバー テキスト ファイル ドライバーのプログラミングに関する考慮事項 その他のテキスト ファイル ドライバーのプログラミングの詳細 Schema.ini ファイル (テキスト ファイル ドライバー)
https://docs.microsoft.com/ja-jp/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver15
スキーマ情報ファイルでは、ファイルの一般的な形式、列名とデータ型の情報、およびその他のいくつかのデータ特性に関する情報が IISAM に提供されます。 スキーマ .ini ファイルは、固定長のデータにアクセスするために常に必要です。 テキストテーブルに DateTime、Currency、または Decimal データが含まれている場合、またはテーブル内のデータの処理をより細かく制御する必要がある場合は、schema.ini ファイルを使用する必要があります。
文字セットの選択
ANSI と OEM の2つの文字セットから選択できます。 Schema.ini のCharacterSet設定は、Windows レジストリの設定 (ファイル別) を上書きします。 次の例は、文字セットを ANSI に設定する schema.ini エントリを示しています。
CharacterSet=ANSI
AccessのSchema.iniの解説はマイクロソフトが検索避けをかけているというレベルで絶対にわからない場所にある
ドキュメント Office クライアントの開発 Access Access デスクトップ データベース リファレンス Microsoft Access SQL リファレンス 外部データ ソース用の Windows レジストリ設定 テキスト データ ソース ドライバーの初期化
https://docs.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/initializing-the-text-data-source-driver
このタイトルとURLはすごい。Schema.iniという文字が全く出てこない。しかし実はここに書いてあるのがSchema.iniの内容。
だが、この解説のデタラメさはまだもっとひどい。
だれもわかるわけがない
1行目から完全にデタラメ
Access Connectivity Engine
テキストデータソースドライバーの初期化
2015/09/18
適用先: Access 2013、Office 2013
テキストデータソースと HTML データソースの両方に同じデータベースドライバーが使用されます。
テキストデータソースデータベースドライバーをインストールすると、セットアッププログラムによって、エンジンおよび ISAM 形式のサブキーに、Microsoft Windows レジストリに既定値のセットが書き込まれます。 これらの設定は直接変更しないでください。アプリケーションのセットアッププログラムを使用して、これらの設定を追加、削除、または変更します。 次のセクションでは、テキストデータソースデータベースドライバーの初期化と ISAM 形式の設定について説明します。
テキストデータソースの初期設定
Access Connectivity Engine\ISAM 形式\のテキストフォルダーには、テキストデータファイルへの外部アクセスに使用される acetxt.dll ドライバーの初期化設定が含まれています。 通常、このキーのエントリの設定は次のようになっています。
win32=\ ACETXT.DLL
MaxScanRows=25
FirstRowHasNames=True
CharacterSet= ANSI
Format=CSVDelimited
Extensions= txt,csv,tab,asc
ExportCurrencySymbols=Yes
Microsoft Access データベース エンジンで使用される、Text キーのエントリを次に示します。
説明
win32
Acetxt.dll の格納場所です。このフル パスはインストール時に決まります。値の型は REG_SZ 型です。
MaxScanRows
列のデータ型を推測する場合にスキャンする行の数です。0 に設定した場合はファイル全体が検索されます。既定値は 25 です。値の型は REG_DWORD 型です。
FirstRowHasNames テーブルの 1 行目に列名があるかどうかを示すバイナリ値です。値 01 では、インポート時に列名が 1 行目に設定されます。
CharacterSet
テキスト ページの格納方法を示すインジケーターです。設定可能な値は次のいずれかです。
ANSI - ANSI コード ページで格納します。AnsiToUnicode および UnicodeToAnsi による変換が行われます
OEM - OEM コード ページで格納します。OemToUnicode および UnicodeToOem による変換が行われます。
Unicode - コード ページの変換は行われません。
<10 進数> - 特定の文字セットのコード ページ番号です。Unicode との変換が行われます。
既定値は ANSI です。値の型は REG_SZ 型です。
形式
TabDelimited、CSVDelimited、Delimited (<1 文字>) のいずれかを指定できます。 区切り記号付きの1文字の区切り文字には、二重引用符 (") 以外の任意の1文字を指定できます。 既定値は CSVDelimited です。 値の型は REG_SZ 型です。
拡張機能Extension(拡張子)
テキスト ベースのデータを探す場合に参照されるファイルの拡張子です。既定値は、txt、csv、tab、および asc です。値の型は REG_SZ 型です。
ExportCurrencySymbols
通貨フィールドをエクスポートする場合に、適切な通貨記号を追加するかどうかを示すバイナリ値です。値 01 は通貨記号を追加することを示し、値 00 は数値データのみをエクスポートすることを示します。既定値は 01 です。値の型は REG_BINARY 型です。
まずこの解説はすべてデタラメ
この場所にたどり着くのにはいくつものトラップを抜ける
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access\Access Connectivity Engine\ISAM Formats
ExportWizardFunction
ImportWizardFunction
一番厄介なトラップはこれ
常識的に考えるとSoftwareのところにあると考えるだろう
\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\ISAM Formats\Text
実際にこういうもっともらしいトラップがある。
だが、これも嘘だ。
CMD画面で出力してクリップする。
reg query "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\XX.0\Access Connectivity Engine\ISAM Formats\Text" | clip
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\XX.0\Access Connectivity Engine\ISAM Formats\Text
Engine REG_SZ Text
ExportFilter REG_SZ Text Files (.txt;.csv;.tab;.asc)
ImportFilter REG_SZ Text Files (.txt;.csv;.tab;.asc)
CanLink REG_BINARY 01
OneTablePerFile REG_BINARY 01
IsamType REG_DWORD 0x2
IndexDialog REG_BINARY 00
CreateDBOnExport REG_BINARY 00
SupportsLongNames REG_BINARY 01
OutputFormat REG_SZ MS-DOS Text (.txt)
ReportFormat REG_SZ MS-DOS Text
FormatFunction REG_SZ txt,SOA_RptToAscii,1,MS-DOS Text (.txt)
そして64bit版の場合、この記事が該当する場所はここであるが、ISMA形式云々など、レジストリの場所のどこにも書いていない。
(以下の環境変数のところは実際のフォルダ名が出ます。またXXはバージョン番号です。上記の記述が2013で止まっているので、少なくともVersion番号は15以上だと考えられます。
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\XX.0\Access Connectivity Engine\Engines\Text
reg query "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\XX.0\Access Connectivity Engine\Engines\Text" | clip
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\XX.0\Access Connectivity Engine\Engines\Text
CharacterSet REG_SZ ANSI
DisabledExtensions REG_SZ !txt,csv,tab,asc,tmp,htm,html,log
ExportCurrencySymbols REG_BINARY 01
Extensions REG_SZ txt,csv,tab,asc
FirstRowHasNames REG_BINARY 01
Format REG_SZ CSVDelimited
ImportFixedFormat REG_SZ RaggedEdge
ImportMixedTypes REG_SZ Majority Type
MaxScanRows REG_DWORD 0x19
UseZeroMaxScanAs REG_SZ One
win32 REG_SZ %CommonProgramfiles%\Microsoft Shared\OFFICEXX\ACETXT.DLL
TextDelimeterの説明がない
この解説には、TextDemiliterの項目がない。Sampleでちょっと載っているだけになっている。
TextDelimeter="
このTextDemiliterの指定がダブルクォーテーション1個で囲まない（文字リテラルが不要）というのもポイント
これは特にテキスト形式のフィールドについて適用する。
しかし実は、テキストに見えても日付に変えられるものは変えて把握している。
注釈の説明がない
どちらもそうだが、実はSchema.iniに注釈ができる
;セミコロンで注釈だよ。改行はできないよ
出力するcsvファイルにExportがいらない
If you are exporting data into a delimited text file, specify the format for that file as well
[Export: My Special Export]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=25
CharacterSet=OEM
DateTimeFormat=mm.dd.yy.hh.mm.ss
CurrencySymbol=Dm
これは正直嘘っぽい。こうすると全く効いていない。スペースなどにも気を配ってみたが、効かない。こういうメチャクチャなことをやるのはよほどAccessがにくいのだろう。もはや絶対にAccessを使わせない！という強固なマイクロソフトの意思しか感じない。ただし、本物は書き換わる。特に文字化けすると文字化けを書き込む場合がある。そういったときのために書いておくことはできる。これ自体が一種の注釈となる。しかし多分これも誤り。
さらにここに書いていないのが次。
カラム名はわからないときは指定しない。とくに今回の場合は日付も指定しない
時間を計算しているところは0日扱いになりおかしくなるが出力するファイルのフィールド名、データ型は決めない
決めて間違うとSchemaを文字化けのような状態で書き換え、以後間違ったままになる時がある。
もし指定するときはOEDCの型を使う
今回はSelect intoを2つ用意する
一つはそのまま単純に出力するtime_master1.csv
もう一つは集計して出力するtime_master.csv
[Export:time_master.csv]は実は全く効いていない。
しかし、このダミーを残すことによって、勝手に書き換わるのを防いでいる。
実は効いていると書き換わってしまうのである。
特にコラム名は効いている。
このため、一旦型が合わないと即エラーになる。
このため、必要がなければ指定しないほうが良い。
[log_master1.csv]
ColNameHeader=True
Format=CSVDelimited
CharacterSet=65001
MaxScanRows=2
textdelimiter="
Col1="コンピューター名" Char Width 10
Col2="日時" Date
Col3="操作種別" TEXT Width 10
Col4="期間" Date
Col5="端末機No" Integer
Col6="端末機名" Char Width 10
[Export:time_master.csv]
ColNameHeader=True
CharacterSet=65001
Format=CSVDelimited
Col1=コンピューター名 Char Width 255
Col2=F2 Date
Col3=F3 Date
[Export: time_master1.csv]
ColNameHeader=True
CharacterSet=65001
Format=CSVDelimited
textdelimiter="
Col1=コンピューター名 Char Width 10
Col2=日時 Date
Col3=操作種別 Char Width 10
Col4=期間 Date
Col5=端末機No Integer
Col6=端末機名 Char Width 10
[time_master.csv]
ColNameHeader=True
CharacterSet=65001
Format=CSVDelimited
textdelimiter="
[time_master1.csv]
ColNameHeader=True
CharacterSet=65001
Format=CSVDelimited
textdelimiter="
そのまま出力された結果
"コンピューター名","日時","操作種別","期間","端末機No","端末機名"
"PC1",2019/02/06 9:56:00,"終了",1899/12/30 0:00:05,1,"吉田1"
"PC1",2019/02/06 9:56:00,"開始",1899/12/30 0:00:05,1,"吉田1"
"PC1",2019/02/06 9:41:00,"終了",1899/12/30 0:02:23,1,"吉田1"
"PC1",2019/02/06 9:39:00,"開始",1899/12/30 0:02:23,1,"吉田1"
"PC1",2019/02/06 9:23:00,"終了",1899/12/30 0:05:08,1,"吉田1"
"PC1",2019/02/06 9:18:00,"開始",1899/12/30 0:05:08,1,"吉田1"
"PC2",2019/02/06 15:18:00,"終了",1899/12/30 2:19:53,15,"田中2"
"PC2",2019/02/06 12:58:00,"開始",1899/12/30 2:19:53,15,"田中2"
"PC2",2019/02/06 12:06:00,"終了",1899/12/30 0:00:44,15,"田中2"
"PC2",2019/02/06 12:05:00,"開始",1899/12/30 0:00:44,15,"田中2"
"PC2",2019/02/06 12:01:00,"終了",1899/12/30 1:27:49,15,"田中2"
"PC2",2019/02/06 10:33:00,"開始",1899/12/30 1:27:49,15,"田中2"
"PC1",2019/02/08 9:56:00,"終了",1899/12/30 1:00:05,1,"吉田1"
"PC1",2019/02/08 9:56:00,"開始",1899/12/30 10:00:05,1,"吉田1"
"PC1",2019/02/08 9:41:00,"終了",1899/12/30 1:02:23,1,"吉田1"
"PC1",2019/02/08 9:39:00,"開始",1899/12/30 1:02:23,1,"吉田1"
"PC1",2019/02/08 9:23:00,"終了",1899/12/30 1:05:08,1,"吉田1"
"PC1",2019/02/08 9:18:00,"開始",1899/12/30 1:05:08,1,"吉田1"
"PC2",2019/02/08 15:18:00,"終了",1899/12/30 2:19:53,15,"田中2"
"PC2",2019/02/08 12:58:00,"開始",1899/12/30 2:19:53,15,"田中2"
"PC2",2019/02/08 12:06:00,"終了",1899/12/30 1:00:44,15,"田中2"
"PC2",2019/02/08 12:05:00,"開始",1899/12/30 1:00:44,15,"田中2"
"PC2",2019/02/08 12:01:00,"終了",1899/12/30 1:27:49,15,"田中2"
"PC2",2019/02/08 10:33:00,"開始",1899/12/30 1:27:49,15,"田中2"
このように使用時間しか決めていないところが勝手に日付が加わる。これは0日とみなされているからだろう。
もともとのレコードが時間しかなくシステムの日付が採用されるためであり、合計も24時間を超えないため、これは問題がない。
SQLに型を指定すればきれいに出るだろう。
MAXScanRowsを今回2行で切っている。このほうが早くなるが、一度間違うと死ぬ。
起動については64bit Cscriptを用いる。
CMDを起動し、CurrentDirectoryをあわせて
Drive:\CurrentDirectory>%Systemroot%\System32\Cscript.exe filename.vbs //Nologo
"コンピューター名","日付","利用時間計"
"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"
やっとUTF-8（BOMなし）で出力できた。
個人的な感想だが、やはりANSIのほうが読み取ってくれるので、UTF-8はとても辛い。
フォルダのファイル構成
C:\csvtest
OpenCmdAndCurrentExecute.bat ***なんども実験すると開くのすら面倒になってきた
SETUSERDSN.bat *** Access Text Driverを設定してくれる。ただしシスアドに怒られたりする。
log_master1.csv *** データベース、今回はUTF-8BOMなしありどちらでもよい
schema.ini *** ANSIで作る
filename.vbs *** vbscript ANSI形式
time_master.csv *** 計算結果のcsv utf-8 BOMなしで出力される VBSの起動時に削除される
time_master1.csv ***データをそのまま出力したcsv utf-8 BOMなしで出力される VBSの起動時に削除される
@echo off
@REM ANSI saved bat file only successed. Notepad.exe Windows 10 May 2019 Later May showst his bat file utf-8
@REM UTF-16LE Failed. UTF-8 with BOM Failed. UFT-8 Without BOM failed
taskkill /IM notepad.exe >nul
taskkill /F /IM %systemroot%\sysWow64\odbcad32.exe >nul
taskkill /F /IM %systemroot%\system32\odbcad32.exe >nul
SET TARGET_DIR1=H:\testado\
cd /d H:\
cd testado
START """ "%TARGET_DIR%"
%systemroot%\syswow64\cscript.exe //NOLOGO testadogroupby.vbs
@echo on
exit
64bit csv UTF-8を前提としたコード
あらためて注意点
Accessがない場合、若しくは動かない場合は、2010はAccess2010は再頒布可能コンポーネントをインストールする。それ以降は2016で良い、そのうえで事前設定が必要
Microsoft Access Text Driverを使用している
事前に設定が必要
前回紹介したこちらを参照
haneimo.hatenablog.com/entry/20160830/1472527773
Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント
https://www.microsoft.com/ja-jp/download/details.aspx?id=13255
しかし、2014年は共存させる方法が必要だった。
32/64ビット版双方のAccess ODBCドライバを共存させる方法
https://www.idearu.info/article/data/ds1046
インストールしていないもう一方を /passiveをつけてインストールする
今はAccess2016再頒布可能コンポーネントがあるため、上記記事はこれをインストールすることで読み替えたほうが良い。
なお、こちらは自動的に共存しているらしい。
Microsoft Access Database Engine 2016 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?id=54920
英語版のみで日本語や他の言語は存在しないらしい。
これはAccessが入っていない場合に入れる必要があるが、インストールされていれば不要。
逆に言うとAccessがなくてもaccdbファイルは作れないことはない。これは何回かやってみている。
なお、CSVファイルは多分2gbが上限なので、1gbになったら分割すること。
Accessが2gbのため、SQLで絞らないと読めない。
しかし全体が見えないため、1GBで安全を保つべきだろう。
Schema.iniの作成
コードの実行とそのフロー
batがあることを前提に進める
メモ帳を開いていたら、とりあえずすべて保存すること。できれば閉じること。
batをダブルクリックする。
batファイルは既存のメモ帳(Notepad.exe)を強制終了する）
batは今回、データベースとなるcsv,schema.iniを保存している c:\csvtestを開き、カレントディレクトリにする。
そしてvbsを起動する。このときcmd画面が2つ開くが気にしないこと。
vbsは既存の結果ファイルを削除し、新たに
すべて単純に出力したselect into
計算して出力したSelect into
2つ出す。出したあとにデータベース、出力ファイル、VBS、Schema、bat ODBCデータソースアドミニストレーター64BIT/32Bitを開く。
Const testPath = "c:\csvtest\" 'ここはルートではないことが望ましい。また、フォルダの末尾に円マーク(Qiitaの表示ではバックスラッシュ)をつけること
'%Systemroot%\System32\Cscript.exe testadogroupby.vbs //Nologo
Dim objADO
Dim strPath
Dim strDelFile5, strfile5
strPath = testPath
strfile5 = "schema.ini"
strDelFile5 = strPath & strfile5
With CreateObject("Scripting.FileSystemObject")
' Schema.iniは削除しない(自動で作成されるとは限らないため)
'If .FileExists(strDelFile5) Then
' .DeleteFile strDelFile5
'End If
' 既存の出力結果を削除
If .FileExists(testPath & "time_master.csv") Then
.DeleteFile testPath & "time_master.csv"
End If
If .FileExists(testPath & "time_master1.csv") Then
.DeleteFile testPath & "time_master1.csv"
End If
End With
' ADO 元記事よりゴージャスに
Set objADO = CreateObject("ADODB.Connection")
objADO.Open "Provider=MSDASQL;Driver={Microsoft Access Text Driver (*.txt, *.csv)};DBQ=" & _
strPath & ";DefaultDir='" & Left(strPath,Len(strPath) -1) &"';ReadOnly=0"
' Select Into 全部そのまま出すのと、集計クエリを用いるのを同時にやる
objADO.Execute "Select * INTO time_master1.csv From log_master1.csv;"
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
' 出来上がったらばんばん全部開く。このスクリプトもバッチファイルもすべて。ODBCアドミニストレータも(フォルダはBatファイルで開いている)
With CreateObject("Wscript.Shell")
Wscript.Sleep 1000
.Run "Notepad.exe """ & testPath & "time_master1.csv" & """"
Wscript.Sleep 1000
.Run "Notepad.exe """ & testPath & "time_master.csv" & """"
Wscript.Sleep 1000
.Run "Notepad.exe """ & testPath & "Schema.ini" & """"
Wscript.Sleep 1000
.Run "Notepad.exe """ & testPath & "filename.vbs" & """"
Wscript.Sleep 1000
.Run "Notepad.exe """ & testPath & "log_master1.csv" & """"
Wscript.Sleep 1000
.Run "Notepad.exe """ & testPath & "OpenCmdAndCurrentExecute.bat" & """"
Wscript.Sleep 1000
.Run "%systemroot%\sysWow64\odbcad32.exe"
Wscript.Sleep 1000
.Run "%systemroot%\system32\odbcad32.exe"
End With
データ型
データ型BIT、BYTE、CHAR、CURRENCY、DATE、FLOAT、INTEGER、LONGCHAR、SHORT、または SINGLE を指定できます。 日付データ型は、"dd-mmm-yy"、"mm-dd-yy"、"mmm-dd-yy"、"yyyy-mm-dd"、または "yyyy-mm-dd" の形式にすることができます。 "mm" は月の数字を表します。"mmm" は月の文字を表します。
それでもMicrosoft Text Driverが使いたいとき
この場合32bit ODBC ("%systemroot%\sysWow64\odbcad32.exe"に登録し) %Systemroot%\SysWow64\Cscript.exe filename.vbs
で起動すると良いようだ。力技もあるが。
以前VBSの32bit強制起動は調べてあるので、過去の記事を調べるとできるかもしれない。
この方法はなにがすごいかというと
ただでUTF-8 Windows 10 64bitという条件でデータベースが作れて、かつ集計ができる。
ということになる。
エラーメッセージ
Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] データ ソース名および指定された既定のドライバーが見つかりません。
これはMicrosoft Access Text Driver (.txt;.csv)と間違えていたとき出現。セミコロンが誤り
Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Text Driver] Too few parameters. Expected 4.
パラメーターの数が少なすぎます。これも接続文字列が間違っている。
Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Text Driver] The Microsoft Access database engine could not find the object 'log_master1.txt'. Make sure the object exists and that you spell its name and the path name correctly. If 'log_master1.txt' is not a local object, check your network connection or contact the server administrator.
これはファイルが見つかりませんといい、だからと言って形だけtime_master.csv
Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Text Driver] Table 'time_master#csv' already exists.
今度はファイルがあるからできないという。日本政府みたいだ。
これはなんとSchema.iniのtime_master1.csvの部分を削除すると治った（自動的に作り直す）
これもわかるわけがない。
Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Text Driver] Too few parameters. Expected 2.
これも接続文字列。このエラーはSchema.iniとデータベースのフィールド名と接続文字列の中のフィールド名が間違っていることが多い。
どうしてもだめなときはSchema.iniのカラム名の定義col1=field1 text width(255)のような部分を削除する。
文字化け
Schemaが効いていない。Schemaが聞かないと932、つまりShift-Jisで出力している。
あとあり得るもの
フィールド名を数字で始めない フィールド名に予約語を使わない(nameとか) フィールド名に記号を入れない
ただ、Powershellでファイル名をCSVで出すとNameというフィールドができてしまい、素直に変換できない時がある。
データに空白" "、空""を作らない。
Accessは空や空白に意味があるので、なるべくデータを入れる。ないときはないでもいい。とにかく空白にするとだめ。
データ型の種類
Csvの場合オブジェクトは持てない。GUIDはテキストにHyperLinkはメモ型になる。
ただし、メモ型にするとフィールドの比較ができない。ただの文字の羅列になりかねない。
このため可能な限り区切って入れる。
ファイル名もネットワークだと300以上になるので、ドメインは別のフィールドにするなど、極力255で切っていくこと。
最後に、どうでもいい疑問かもしれないけど、SQLサーバーではUTF-8のCSVって読めないんだろうか。
OEMというのは文字コード一覧のOEMって入っているやつだと思う。
公式だと探しにくいので https://qiita.com/Q11Q/items/cc267d52afa366667402 この後ろに寄せてある
msoEncodingOEMUnitedStates 437 米国で使用される OEM
このあたりだ
しかし、個別の言語でUnicodeはない。
参考文献
大きい数値データ型を使用する
Access と SQL Server のデータ型の比較
Microsoft Access（.mdb、.accdb）のODBC、OLEDBドライバーに関するまとめ
コマンドラインからプログラムを閉じる（Windows）
Windows、バッチファイル（.bat）からフォルダを開く
64bit版WindowsPCで32bit用ODBCドライバーを使用する方法 nanana blog 2019/06/26
パソコンは64bitなので64bit版のODBCを使ってデータベースに接続しようとしますが
インストールされているODBCドライバーは32bit版のため
「[Microsoft][ODBC Driver Manager]データソース名および指定された既定のドライバー
が見つかりません。」というエラーメッセージが表示されていたのです。
そこでまさかのTaskScheduler
接続文字列一覧
テキスト版にはこのMicrosoft Access Text Driverは載っていない
カレント・ディレクトリの取得と変更 ―― CurrentDirectoryプロパティ ――
WScript.Shell# CurrentDirectory
[VBScript] カレントディレクトリを変更する
バッチファイルでのODBC設定まとめ
ODBCのText DriverとExcelとを使ってCSVファイルをSQLで検索・抽出する
64bit OSでADODBテキストドライバを使う -備忘録 2012/08
64bit OSでADODBテキストドライバを使う
32bit版OSではADODBでCSVファイルを扱う場合ドライバは
以下のように指定する。
"Driver={Microsoft Text Driver (*.txt; *.csv)};"
だが、64bit版OSにはこのドライバは使えないため、この指定では
エラーになってしまう。
64bit版OSでCSVを処理する方法としては以下の２つの方法がある
- 32bit版CScript.exeでVBSを実行する
- 64bit版のテキストドライバをインストールして使用する
ODBC データ ソースを管理する
ODBC プログラマー's リファレンス
MySQLへのODBC接続設定をする
64 ビット版の Windows で、ODBC アドミニストレーター ツールが 32 ビットと 64 ビット両方のユーザー DSN を表示する
コマンドラインでのODBCのDSNの登録
odbcconf /A { CONFIGDSN "Microsoft Access Driver (*.mdb)" "DSN=MyDSN;DBQ=C:\mdb\mydb.mdb" }
ここまではわかった
mshta vbscript:execute("set app=CreateObject(""Excel.Application""):app.ExecuteExcel4Macro(""CALL(""""odbccp32.dll"""",""""SQLConfigDataSource"""",""""JJJCC"""",0,3,""""Microsoft Access Driver (*.mdb)"""",""""DSN=MyDB"""")""):close()")
これはHTAとExcel4Macro、めちゃめちゃだ。
How to add system DSN into odbc 32-bit using bat file? Sql server
SQL Server のコマンドライン。管理者権限が必要としている。
[コンピュータ]ODBCデータソース - システムDSN にドライバSQL Serverをバッチ(スクリプト)で登録する
VBSを使う方法
UserIDはレジストリ見ていると"LastUser"というのを使うことも出来るようだ。
こちらは環境によっては原因不明だがレジストリに登録されて、アプリケーションからも接続できるが、ODBC データソース アドミニストレータの「システムDSN」タブで開く画面上に名前等が表示されないことがあったので私的にはお勧めできない。
こういう方法もあるが、今はもっと無理そうだ。
とりあえずVBSで登録する方法は保留とせざるを得ない。batがあるだけまだいい。