先回のパート１、EXCEL2013でSQL、EXCELシート同士をクエリする（第１回）、に続き、
今回はEXCELの仕様（制約）、自動処理、EXCELのBOOKバグ、などEXCEL＋SQL時の問題点を具体的に記す。
環境としてはWindows10（64bit）とWindows7（64bit）、EXCEL2013（64bit）。
Windows10だから発生した、と言う問題は皆無。win7、win10、共に事象は同じ。
※実際のVBAコードは 最終回 に記しています
「 教訓 」
VBAでは問題ないが、SQL（ADO）では問題になる事がある
「 処理 」
１．シートAの列A left outer join シートBの列A
２．結果をシートCに貼り付ける
「 クエリするシート 」
・シートA 行2000、列13
・シートB 行230、列6
「 JOINキー 」
・シートAの顧客No（数字5桁）
・シートBの請求番号（数字17桁）の中にある顧客No（数字5桁）
上記非常に単純なleft joinクエリ
一般的なDB（sqlserver、ｵﾗｸﾙ）を使いt-sqlを使えば（オラクルはPL/SQL）上記sql作成に15秒・実行に1秒、どんな書き方をしようとこんな単純なクエリはsql作成～実行完了まで16秒で終わるが、EXCEL＋VBA＋ADO（SQL）では多くの問題が発生したためsql作成～実行完了までに、なんと約1万倍以上の時間を要した（約5日）
＜ ポイント ＞
・最初に断っておきますが、joinキーに数字が入った列を使ったため問題山積みになりました。
・excelに文字列と認識される文字列が入った列を使えば問題は発生しません。
・よって、excelのSQLではjoinキーに数値列を使うと問題が起こる！と記憶しておくと良いかと思います。
対処法は下記以降を参照下さい。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
参考までに （初心者編） START
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
EXCEL VBAでのクエリ（SQL）の書き方は普通のクエリと少し異なり、こんな感じ（ADO）
select * from [請求データ$] a left outer join [CSV1$] b ON mid(a.顧客ID,2,5) = mid(b.請求番号,12,5)
構文は普通のSQLとほぼ同じ。ただ特徴的な事項がある
１．シートを指定する場合 [シート名$ ] と書く
上記、請求データ、と、CSV1、はシート名
２．関数は結構違う
一例は、VBAにはsubstringがないのでmidを使ったりと、関数の違いはある
３．シート ＝ テーブル と考える
シートAとシートBをjoinする ＝ テーブルAとテーブルBをjoinする、そう読み替える
４．シートの1行目は項目名として認識されSQLが実行される
これは大変便利でとても面白い機能
今回、同一BOOK内の、シートAとシートBをSQLでJOINするが、シート1行目は見出し行でデータ行は2行目から。 通常DBでは1レコード目からJOINが開始されるが、ADOで使用するEXCELドライバーは1行目をJOINせず見出し行として無条件に出力してくれ、実際のJOINは2行目以降のデータ同士で行ってくれる。 これは便利でとても面白く結構感動ですね。
ざっと目だった点を言えば上記な感じ。
あっと、、もっと基本的な話が1点
・EXCELマクロはマルウェアやウィルスの温床として世間からは悪として扱われている。MSも悪として扱っている。
そのため「マクロを有効にする！」そうわざわざEXCELの設定画面で指定しないとマクロ（VBA）は動かない。
しかし便利な拡張子xlsmと言う「マクロが書かれたエクセルですよ！」と予め宣言している拡張子がある。
もちろん、今回のようマクロ（VBA）を使う場合は拡張子をxlsmとして保管は必須。
Windows10ではエクセルを開いたときに上部にマクロを有効にするか？ボタンをクリックするだけでマクロが動く。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
参考までに （初心者編） END
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
＜ 本題 ＞
結論を先に言えば・・
シートのデータをVBAに渡して処理だのクエリ（SQL）だのやらせ、結果をシートへ書き込む、と言うEXCEL＋VBAでの処理。このとき全てのセルの頭に強制的に ' を付加し、文字列としてEXCELに認識＋処理させれば、シートから読み込むときも、シートへ書き込むときも、問題は何も発生しない。
ただ使う側の人間に、例えば新データ1行追加する度、全てのセルの頭に ' を付けて！とは言えない（ミスも増える＝人間のオペレーションを信用してはいけない）従ってその辺は別シートから参照を使ったり、VBAで対処させることが必要。
（免責）
以下、検証漏れ、解説ミスが発生している可能性もあるので、最終的にはご自身で検証下さい
また今回の問題は、joinキーとなる列が全桁数字表記されているため、excelの数字に対する仕様、が働いてしまい問題になった。要するにexcelが最初からこの列は文字列だ！と、そう判断してくれる列同士のjoinであれば、何ら問題ない。
例えば0100はexcelに数字と認識されてしまうが、A000は文字列なのでjoinキーに使っても何ら問題は発生しない。要するに変わったことをすると問題が多発する、と言う典型的例でもある。そのための長い解説と次回のコードがある。
ただEXCELとADOで使うEXCELドライバーの、数字に対する様々な仕様が見れるので、それはそれで面白いかも？
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
EXCEL＋VBA＋ADO（SQL） ４つの問題点 START
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
１．データをADOへ渡すとき、EXCEL書式設定の文字列指定は無視され、結果数字として扱われる
→ そのためADOがSQLを実行する時点では頭の0が消去されており、結果JOINキーがマッチしなくなる
→ 対策）顧客No（数字5桁）の頭に ' を予め付加しEXCELに文字列と認識させる
※ユーザーに入力の度に ' を付加しろとは言えない
※そのため開発側で別シートを用意し ' を付加し、このシートをクエリ対象とする必要ある
※若しくはSQLを実行するVBAでSQLを実行する前に全行のjoin対象セルに ' を付加（この対処は不可能）
２．EXCELの制約（仕様）のため、デフォルトでは数字17桁を表示できない
ex. 99999001070000103
エクセルでの表示 ： 2.3716E+16
→ これは表示上の問題でVBAで扱う場合問題ではないが、クエリ結果をシートへ書き込む場合に問題となる
「 対策 」
・頭に ' を付加し文字列とすれば問題ない
・VBAからシートへ書き込む場合に注意する程度
３．EXCELでは15桁以上の数値は扱えない（EXCELの15桁問題）
99999001070000103
上記のよう入力すると、以下のよう15桁以降は0に置換されてしまう
99999001070000100
これは知る人ぞ知る、IEEE754、問題。
これがよく言われるEXCELで計算結果が正しくない＝誤差が生じる問題として話題に上がる。
またクレジットカード番号（16桁）を扱う場合も問題になる。
詳しくはwikiを参照いただきたいが、EXCELはIEEE浮動小数点数演算標準（IEEE754）を仕様として取り込んでいる。
よって15桁以上の数値は扱えない（仕様のため回避は不可能）
「 対策 」
・今回の場合は文字列とするため ' 付加すれば問題なし
・よってVBAからシートへ書き込む場合に注意する程度
４．CSVをEXCELシートへ取り込む際も、数値と文字列問題が発生する
EXCELの機能にあるインポートをマクロ記録すると以下のコードが生成される
Sheets(xxx).Select '出力先シートxxxをアクティブにする
ActiveSheet.Cells.Clear '出力先シート内をクリア
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & CsvLocation, Destination:=Range("$A$1"))
.Name = "bank"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 932
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
下から4行目、CSVの3番目の要素を文字列としてインポートしている。これはセルの書式設定で文字列と指定した事と同じ。
これは前述したシートBの請求番号（数字17桁）であり、シートには問題なく17桁全桁表示される
※エクセル上では表示書式＝文字列が有効になるため
しかしこのシートのデータをSQLで使うため、かつ、この3番目の要素をjoinキーとしてすると、前述の２，３、の問題が発生する。
「対策」
・CSVをEXCELシートへインポートする際、自作コードを作り必要な列へ ' を付加する
・別シートを作り各セルを参照させ、当該列の参照に "'"＆ をつけEXCELに文字列であると宣言する
EXCELとVBA上のADOを考えるとき、渡されるデータは通常のDBなどにはないEXCEL独自の仕様を踏まえ、どうデータが渡されるか、若しくはデータがシート上でどう表現されるか、EXCELの仕様（制約）を考慮配慮する必要ある。
※ADOが受け取った生データを見ることはできないので、理詰め＋勘で対処する
だが上記問題は、見た目数値だが文字列として扱えば問題は発生しない。
しかしそのためには物理的に頭に ' を付加し、EXCELに文字列として認識させねばならない。
VBA＋ADOではセルの書式設定は無視される、と考えておけば良い。
但し別の考慮点も
今回使用したドライバではそのような動きをしたが、他のドライバでは別の動きをする可能性もある（2015年現在では｛driver｝は使えないので問題ないと思うが？）。するとドライバに応じた対策が必要になるケースも考慮する必要あり。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
EXCEL＋VBA＋ADO（SQL） ４つの問題点 END
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
制約（EXCELの仕様）はまだあるので引き続き記す。
５．jetエンジンは64bit環境サポートしていない
Windows10が発売されている2015年現在においても、VBAは20年前のVB6と何ら変わりない
VBA＝VB6のようなもの（VBAはVB6のサブセット）＝古すぎ終わっている言語。
結論 ： jetなど化石なので無視すればよいだけ。
余談だが、最新ライブラリのコネクションをウォッチするとjet表記を見て取れる。不気味だが詳細は不明のためスルーする。
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\xxx\xxx.xlsm;Mode=Share Deny None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engi"
Windows10上でEXCEL2013を使用するような2015年の現在、コネクションのプロバイダーは、Microsoft.ACE.OLEDB.12.0、を使う。 参照設定は以下。
32bitWindowsでは検証していないが、64bitWindowsでは使用できないため、もう使えない、そう考えておけばよい。
まだあるEXCELの仕様（制約）の例
以下は今回問題にならなかったが、EXCELにはこういった仕様もある
６．EXCELがデータ型を勝手に決定してしまうためnullが返される
これはEXCELドライバ（なんのドライバだ？）の仕様。
https://support.microsoft.com/ja-jp/kb/194124
リンク先の情報はかなり古いが、20年前のVB6のサブセットであるVBAの場合、決して古い情報とは言えない。
リンク先のデータアクセスはDAOだが、もしや後発のADOも仕様を引き継いでいるのでは？！と一応情報を載せた。
＝＝＝＝＝＝＝
その他の問題 複雑なプログラムであるエクセルに重いバグが入っていました
＝＝＝＝＝＝＝
エクセルBOOKのバグに遭遇。一旦バグスイッチが入るとそのブックは破棄するしかない。
下記例はごく単純なコード。
俗に言うEXCEL VBA初心者のコードで、直接セルを参照・設定し処理している＝極悪コード＝業務で使ってはいけないコード。
だが、2千件（13列）と300件（6列）のouter joinクエリ＋画面貼り付けを20秒で完了出来る。
・
・
'ADO
Set cn = New ADODB.Connection
cn.Provider = "MSDASQL"
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& ThisWorkbook.FullName _
& ";Extended Properties=""Excel 12.0 Xml;IMEX=1;HDR=YES;"""
sql = wsSql.Range(SQLCell).Value '当該セルよりSQL文を読込
cn.Open
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenStatic 'open&sqlが実行される
'新シート作成（現在のシートの最後に追加）
Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = Range(NewSheetNameCell).Value
'見出し行
For i = 0 To rs.Fields.Count - 1
Cells(oRow, i + 1).Value = rs(i).Name '・・本処理中にexcelのセルを直参照している（極悪コード）
Next
データ行
j = oRow + 1
Do Until rs.EOF
For i = 0 To rs.Fields.Count - 1
Cells(j, i + 1).Value = rs(i).Value '・・本処理中にexcelのセルを直参照している（極悪コード）
Next
j = j + 1
rs.MoveNext
Loop
・
・
結果行2000、結果列19列の、left outer join 。このクエリ実行とシート貼り付け時間は約20秒弱。
しかしEXCELにてシート編集やセル編集VBA編集→実行を行っていると、ある時点でバグスイッチが入り、20秒で完了した当処理が300倍程の時間がかかるようになった。（要するに終わらなない）
こうなるとBOOKは壊れた。そう考え新しい真っ新のEXCELを作成し、シート、VBAを貼り付け、作り直すとまた20秒で処理が終わる。しかしまたバグスイッチが入るともう駄目。そのBOOKは破棄するしかない。
このBOOKバク回避に、とにかく考えられるあらゆる事を行ったが（シートを最小限まで減らすに始まり・・・）このバグは回避できなかった。
そこからが至難の連続が始まる。
VBAでは使用するEXCELシート全要素を、全て配列か変数に代入し、それらのみで本処理を書き、最後に配列toシート（range）、と言うプログラミングを行わないと駄目、そう考えそう実施する必要がある。
新しいofficeパッチで問題が出る可能性もあるので、売り物にする場合はそのよう作るしかない。
これは 次回記事 で コツ として記します。
（ まとめ ）
こうやってEXCELの各種仕様（制約）を書き出すと大したことではなく、回避も容易。しかし全く知らない状態から数々の問題を解決しながら理詰めと勘を駆使し進むことは、これは非常に大きな問題であり発見に時間がかかり、参った・・という感想しかない。
次回パート３は最終回、なるべく纏めた情報を記したい。実際のVBAコードもUPする予定。
パート１はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする（第１回）
パート３はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする（最終回）
0 件のコメント:
コメントを投稿