2015年8月12日水曜日
EXCEL2013でSQL、EXCELシート同士をクエリする(最終回)まとめ+VBAコード
EXCEL+VBA+SQL(ADO) 外部DBなど使わず、全てをエクセル内で完結させる
2回に渡り記事をUPしたが、まだ完全に纏められるほど頭が固まっていないが、最終回。
現時点での纏めです。※コードは後半
(免責)
・下記はコード作成中に発見したモノで、コードを作った後で改めて検証は行っていないため、不正確な解説が行われている可能性あるが、不正確な点あればスルーの事。
・MSにより随時更新されているEXCELのパッチにて問題が解決する場合もあるが、新たな別の問題が発生する場合もある(現在日:2015/8)。
※MSのOffice系パッチはとてもいい加減で、ある日突然マクロ(VBA)がエラーで動かなくなる(仕様が真逆に変更になっていた)等、過去にMSにしてやられたこと数回=私的には業務PGMでexcelなど決して使いたくない
「 EXCEL エクセル+VBAのコツ 」
「エクセルの要素(cells、sheetなど)は、本処理では、絶対に!決して!直参照しない」
※処理件数が少なければシート直接参照OK!! 問題ない。
理由:処理が数百~数万倍遅くなる
2015年8月現在はBOOKバグも発生するのでSQL使用時は必須と考えた方が良い
・最初に、シートにあるデータは、VBAで配列(若しくは変数)を作りシートを丸ごと入れ、
その配列や変数のみを使って処理を行う
(シートを参照しない=処理速度が異様に高速になる)
・最後に配列や変数の値を、結果表示するEXCELシートへ貼り付ける
・・処理流れ・・
「前処理」 VBAからI/Oするシートやセルのデータは配列や変数を作って入れる
「本処理」 絶対に!!!sheet、cell、等エクセルシート内の要素を直参照・直設定しない。
配列や変数のみを使い処理ロジックを書く。
「後処理」 処理結果をEXCELシートへ一括貼付する(range)
これらは一般的に、VBA高速化テクニック、として公開されているが、私的には必須と思っている。
出来ないならコード書くな!と言いたい。
参考ページ → とても分かりやすい解説
「 EXCEL エクセル+VBA+ADO+SQL (シート同士をjoinしたクエリ) のコツ 」
「シート上で数字に見える列は、事前に全て ' を付けEXCELに文字列と宣言する」
「 教訓 」
VBAでは問題ないが、SQL(ADO)では問題になる事がある(永遠にマッチしない事態に陥る可能性ある)
多分今回発生した一連の問題のキモはこれでした。
ADOに渡ったデータの値が変わってしまうなど思いもしなかったですから。
------------
まず解説)
------------
EXCELセルの中身 A1:1234 A2:0123 A3:'0123
EXCELでの見た目 A1:1234 A2:0123 A3:0123
A2はセルの書式設定で「文字列」と指定してある
------
VBAにて
------
Sub main()
Dim wsMain As Worksheet
Set wsMain = Worksheets("Sheet1")
MsgBox (wsMain.Range("A1").Value)
MsgBox (wsMain.Range("A2").Value)
MsgBox (wsMain.Range("A3").Value)
End Sub
実行すると・・・次のよう表示される
1234
0123
0123
VBA上ではEXCELの数値の仕様とセルの書式設定が効いている。
だから2番目と3番目が「0123」と表示される
------
しかしADOでは!!
------
EXCELの数値仕様 : 適用
セルの書式設定 : 無視
データは其々次のよう読み込まれていた
1234
123
'0123
VBAで表現されるデータがそのままADOに渡ると思っていたため、永遠にjoinしない状況が発生!
※実際にADOに渡されたデータを見ることは不可能。(今回使ったドライバーではこうなった)
------
回避策は・・
------
EXCELの数値列の全セルの頭に、予め ' を付加し、ADOでは「'0123」と言うデータでSQLした。
今回のSQLに使ったドライバ
Microsoft ActiveX Data Objects 6.1 Library
Provider:Microsoft.ACE.OLEDB.12.0
他のドライバ、またバージョンによって動きが異なること、大いにあり得る
< コツ(まとめ) >
・ADO(VBAのADO)では、エクセルで設定したセルの書式設定は無視される
上記より
・数字に関してエクセルには数々の仕様が存在し、事前に仕様が適用されたのち、クエリ(ADO)へ渡される
仕様回避は不可能 上記と記事(2)を参照
・ADOを使用するなら数字列はEXCEL上で事前に ' を付加しておく
処理結果をシートへ結果を貼り付ける時も、EXCELの数値仕様が適用され問題となることがあるので ' を付けると全て上手く行く
・SQLのjoinキーは、エクセルでの全ての数字の制約(仕様)を考慮しないと永遠にマッチしない事態に陥る
今回大問題になった事件(記事(2)を参照)
・対策としてEXCEL数値列に ’ を付加するには
1.別シートを用意し当該シートのセルを其々参照させ、その別シートをVBAに渡しクエリで使用する(簡単)
2.CVSなどをラッパーとし利用I/Oし、書きor読込時に ' を付加し、新シートへ書き込むVBAを作る(面倒)
上記数字列全てに ' を付けてしまう手法は、joinキーに数字を用いない場合もエクセルの各種数字に対する制約がキツイので、なら数字列は予め全て ' 付けてしまえ!!的な手法。 但し美しくないので、机上のプロやIT記者(私はマスタべーションのプロと呼んでいる)は嫌がると思うが、なら現場入ってみろ!と私ならそう言います。
批判されること、あるかも知れない現場的な手法ですが、開発もメンテも楽になりますよ!
今回作ったコードの一部を記します。ご参考まで。
※これがウォータープルーフ開発で用いられていた上から下へ流れるコード一例、でもある
(オブジェクト指向でない = 今どきこんなコードを書いたら張り倒されるかクビになるかも?)
※VBAは、VBA<VB6 要するにVBAは20年前の代物=学習教材としては最悪です
java(JavaScriptではない)、PHP、C#を勉強しましょう
※解説はしませんので質問もしないでください、少々雑でいい加減な個所もありますが動きます
※不明なパラメータなど、ググれば腐るほど解説ページが出てきますので、そちらを参照のこと
※エラー処理は外しているので、On Error GoTo、して下さい
※下記コードの著作権は私にあるので好き使っていただいて結構です (但し、使えるものなら!)
'-----------------------------------------
' サンプルコード START
'
' Windows10(64bit)、Excel2013(64bit)、拡張子~.xlsm、にて稼働中
'-----------------------------------------
Sub CollectAccountDue()
'出力する未収金反映済の請求シート名
Dim OutSheetName As String
OutSheetName = "当月請求"
'SQL文
Dim SQLSheet As String
Dim SQLCell As String
SQLSheet = "tempSQL"
SQLCell = "A2"
'ワークシート
Dim wsMain As Worksheet
Dim wsSql As Worksheet
Dim wsOut As Worksheet
Set wsMain = Worksheets("メイン") 'メイン画面
Set wsSql = Worksheets(SQLSheet) '当該セルよりSQL読込
Set wsOut = Worksheets(OutSheetName) '出力先シート名
'ツール → 参照設定 → Microsoft ActiveX Data Objects 6.1 Library
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
'出力STARTセル情報
Dim oColumn As Long '出力カラム
Dim oRow As Long '出力行
oColumn = 1
oRow = 1
Application.ScreenUpdating = False
'出力先シートをクリア
wsOut.Cells.Clear
'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
If rs.Fields.Count < 1 Then
MsgBox ("エラー発生!!該当者がマスター上に存在しません。処理を終了します")
Exit Sub
End If
'(未使用)新シート作成(現在のシートの最後に追加)
'(未使用)Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = Range(NewSheetNameCell).Value
'---------------------------------------
'--- Query Data -> New Sheet ---
'---------------------------------------
Dim rsFieldsCount As Long
rsFieldsCount = rs.Fields.Count
'出力
Dim rowArray(1 To 3000, 1 To 100)
Dim colCount As Long
Dim rowCount As Long
colCount = 0
rowCount = 1
'未収金、請求額算出
Dim Claim As Long
Dim Claim1 As Long
Dim Claim2 As Long
Dim Claim3 As Long
'========================
Dim dblStart As Double
Dim dblEnd As Double
Dim dbltime As Double
dblStart = Timer
Debug.Print dblStart
'========================
'クエリ結果を配列へ挿入
' EXCELバグが発生しなければ配列へ挿入する倍の時間で直接シートへ挿入可能
' だがEXCELのBook内バグが発生しだすと300倍の時間が掛かり回避不可能
' 従って回避策として配列へ挿入する
colCount = rsFieldsCount + 3 '未払金、小計、請求額を付加
Do Until rs.EOF
'ヘッダー行セット(1行目)
If rowCount = 1 Then
For i = 1 To rsFieldsCount
rowArray(1, i) = rs(i - 1).Name
Next
rowArray(1, 45) = "未払額"
rowArray(1, 46) = "小計"
rowArray(1, 47) = "請求額"
rowCount = rowCount + 1
End If
'データ行セット(2行目以降)
If rs(2).Value <> "'" Then
'col
For i = 1 To rsFieldsCount
If IsNull(rs(i - 1).Value) = False Then
rowArray(rowCount, i) = rs(i - 1).Value
Else
rowArray(rowCount, i) = ""
End If
Next
'========================================
' 未払い金、小計、請求額を付加
'========================================
'請求金額(32)
If (rowArray(rowCount, 32) = "") Then
Claim1 = 0
Else
Claim1 = rowArray(rowCount, 32)
End If
'当月合計(20)
If (rowArray(rowCount, 20) = "") Then
Claim2 = 0
Else
Claim2 = rowArray(rowCount, 20)
End If
'現金入金(21)
If (rowArray(rowCount, 21) = "") Then
Claim3 = 0
Else
Claim3 = rowArray(rowCount, 21)
End If
'** 未払額 ** ** 小計 **
If (rowArray(rowCount, 37) = 0) Then
rowArray(rowCount, 45) = Claim1
Claim = Claim1 + Claim2 + Claim3
Else
rowArray(rowCount, 45) = 0
Claim = Claim2 + Claim3
End If
rowArray(rowCount, 46) = Claim
'** 請求額算出 **
If (Claim <= 0) Then
rowArray(rowCount, 47) = 0
Else
rowArray(rowCount, 47) = Claim
End If
'行カウント
rowCount = rowCount + 1
Else
Exit Do
End If
rs.MoveNext
Loop
Dim rowCnt
rowCnt = UBound(rowArray, 1) '1368
'======================
'出力先シート ← 配列
'======================
'1Startを0Startにする(正常に戻す)
rowCount = rowCount - 1
'-1を行った後のrowCountは、1行目のヘッダーを含めた件数なので注意!!!
wsOut.Select 'アクティブにしておかないと「rangeオブジェクトは失敗しました 1004 」が発生する
wsOut.Range(Cells(1, 1), Cells(rowCount, colCount)) = rowArray ’配列→シート:オーバーフローしているが問題ない(切り捨てられる)
'========================
dblEnd = Timer
Debug.Print dblEnd
dbltime = dblEnd - dblStart
Debug.Print "処理時間は" & Format$(Int(dbltime)) & "秒でした"
Debug.Print "処理時間は" & Format$(Int(dbltime * 10 ^ 4 + 0.5) / 10 ^ 4) & "秒でした"
'========================
rs.Close
cn.Close
'============================
'口振シート作成(CSV化の元データ)
'============================
Dim rowBankArray()
ReDim rowBankArray(1 To rowCount - 1, 1 To colCount) '1行目はヘッダー行なので不要、-1する)
Call CreateBankTransferData(rowArray(), rowBankArray())
'================================
'コンビニシート作成(CSV化の元データ)
'================================
Dim rowCvsArray()
ReDim rowCvsArray(1 To rowCount - 1, 1 To 80) '1行目はヘッダー行なので不要、-1する)
Call CreateCVSTransferData(rowArray(), rowCvsArray())
Application.ScreenUpdating = True
'メインシートをアクティブにする
wsMain.Select
Dim ret
ret = MsgBox("処理は正常に終了しました", vbOKOnly, "当月請求データ作成")
End Sub
'-----------------------------------------
' サンプルコード END
'-----------------------------------------
※↑ブログUP時、特殊文字が自動変換されてしまうので大文字にしている箇所があります
※仕様としては、顧客マスタと未払金CSVをSQLでマッチングさせ、通常請求額+未払い額を算出し、当月請求額としてFIXさせている。
一応クエリも貼っておきます
select * from [請求データ$] a left outer join [CSV1$] b ON mid(a.顧客ID,2,5) = mid(b.請求番号,12,5)
・SQLはこんな感じにセルに記入
・VBAでシート「SQL」の「A2」を読み込み、SQL文として使用する
※データは機密のため見せられませんが、EXCELで管理している「顧客情報」と「請求情報」なので大したことありませんから特に記述しません。
参考までに 第1回記事 で解説した、手作業で行っていたEXCELを使った請求業務。
上記EXCEL+SQLを使うことで、パートさんが行っていた毎月7人日必要としていた請求処理は、わずか8秒で完了できるようになった。
開発費用はたった半年で回収でき、あとはボロ儲け?めでたしめでたし。
( まとめ 余談 オチ )
3回に渡り、EXCELのシート同士を其々テーブルと見立て、とあるシートのセルに書いたSQL文をVBAで読み取り、ADOを使いそのSQLを実行、結果を新しいシートへ貼り付ける。と言うVBAコードと解説?意見?日記?を長々書いてきましたが、最後に余談とオチ。
私はEXCELには詳しくない普通の職業プログラマーだったため、基本EXCEL関数は使いません。と言うより信用していない、私には必要ない、と言った方が正しい。
やはり速度が非常に遅い点、応用が利かない点、MSが予告なく仕様を勝手に変更する点、EXCELやVBA特有の関数を覚えたところでEXCEL以外では使えない点=それでは金にならない=食っていけない、からですね。(VB6やっていたので対した苦も無くVBAでコード書けますが・・)
職業プログラマーはプログラミングや設計で生計を立てるので、いかに将来性があり(市場シェア)効率よく金になるモノに注力し取り組めるか(学習勉強)、これが注力対象を選択する一番のポイント。しかも腐るほど金を持っている巨大企業が好む言語等を選択するしかない。中小企業(金がない=単金も安く金払いも悪い)が好む事が多いEXCEL VBAの市場シェアは低く(無に等しい)覚えたところで永年に渡り食っていけません(寝る間を惜しんで死ぬほど学習しているなら別!)。それならjava、.net、c#、各種web系言語、開発、運用ノウハウ、コンサル等に注力した方がよっぽど良い。と言うよりそれ以外の選択肢はない。数十年に渡り家族食わしていかないとでしょ?これがEXCEL・VBAが私に必要ない理由であり覚えない理由。ノウハウは今回の記事だけで十分、もうイラナイ。
そして最後にオチ
今回の、シートAとBをマッチングし、結合結果をシートCへ書く、と言う処理は、EXCEL VBA関数である「Find」を使い応用すれば出来ます。
「VBA シート同士の結合」とググれば例は山ほど出てきます。コード量も大変少なく、、さて、あなたならどちらの手段を選択しますか?
SQLを使い処理件数が増えても速度も速く汎用性を持たせるが面倒なロジック、FINDを使い短いコードで処理をこなす。
FINDを使った処理群、速度に関してはどうなのでしょう?試してないのでわかりませんのでご自身でお試しください。
私はFINDもですがエクセル提供の関数など信用しないので基本EXCELは使いませんが(EXCEL関数群があそこまで処理が強烈に遅くハングする等のバグも多いとはイマドキ夢にも思いませんでした)、数百件程度の少量データならFINDを使う事を大いに検討するかな?と考えます。
おわり
パート1はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(第1回)
パート2はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(第2回)
2015年8月5日水曜日
EXCEL2013でSQL、EXCELシート同士をクエリする(第2回)
先回のパート1、EXCEL2013でSQL、EXCELシート同士をクエリする(第1回)、に続き、
今回はEXCELの仕様(制約)、自動処理、EXCELのBOOKバグ、などEXCEL+SQL時の問題点を具体的に記す。
環境としてはWindows10(64bit)とWindows7(64bit)、EXCEL2013(64bit)。
Windows10だから発生した、と言う問題は皆無。win7、win10、共に事象は同じ。
※実際のVBAコードは 最終回 に記しています
「 教訓 」
VBAでは問題ないが、SQL(ADO)では問題になる事がある
「 処理 」
1.シートAの列A left outer join シートBの列A
2.結果をシート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とほぼ同じ。ただ特徴的な事項がある
1.シートを指定する場合 [シート名$ ] と書く
上記、請求データ、と、CSV1、はシート名
2.関数は結構違う
一例は、VBAにはsubstringがないのでmidを使ったりと、関数の違いはある
3.シート = テーブル と考える
シートAとシートBをjoinする = テーブルAとテーブルBをjoinする、そう読み替える
4.シートの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) 4つの問題点 START
========================
1.データをADOへ渡すとき、EXCEL書式設定の文字列指定は無視され、結果数字として扱われる
→ そのためADOがSQLを実行する時点では頭の0が消去されており、結果JOINキーがマッチしなくなる
→ 対策)顧客No(数字5桁)の頭に ' を予め付加しEXCELに文字列と認識させる
※ユーザーに入力の度に ' を付加しろとは言えない
※そのため開発側で別シートを用意し ' を付加し、このシートをクエリ対象とする必要ある
※若しくはSQLを実行するVBAでSQLを実行する前に全行のjoin対象セルに ' を付加(この対処は不可能)
2.EXCELの制約(仕様)のため、デフォルトでは数字17桁を表示できない
ex. 99999001070000103
エクセルでの表示 : 2.3716E+16
→ これは表示上の問題でVBAで扱う場合問題ではないが、クエリ結果をシートへ書き込む場合に問題となる
「 対策 」
・頭に ' を付加し文字列とすれば問題ない
・VBAからシートへ書き込む場合に注意する程度
3.EXCELでは15桁以上の数値は扱えない(EXCELの15桁問題)
99999001070000103
上記のよう入力すると、以下のよう15桁以降は0に置換されてしまう
99999001070000100
これは知る人ぞ知る、IEEE754、問題。
これがよく言われるEXCELで計算結果が正しくない=誤差が生じる問題として話題に上がる。
またクレジットカード番号(16桁)を扱う場合も問題になる。
詳しくはwikiを参照いただきたいが、EXCELはIEEE浮動小数点数演算標準(IEEE754)を仕様として取り込んでいる。
よって15桁以上の数値は扱えない(仕様のため回避は不可能)
「 対策 」
・今回の場合は文字列とするため ' 付加すれば問題なし
・よってVBAからシートへ書き込む場合に注意する程度
4.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キーとしてすると、前述の2,3、の問題が発生する。
「対策」
・CSVをEXCELシートへインポートする際、自作コードを作り必要な列へ ' を付加する
・別シートを作り各セルを参照させ、当該列の参照に "'"& をつけEXCELに文字列であると宣言する
EXCELとVBA上のADOを考えるとき、渡されるデータは通常のDBなどにはないEXCEL独自の仕様を踏まえ、どうデータが渡されるか、若しくはデータがシート上でどう表現されるか、EXCELの仕様(制約)を考慮配慮する必要ある。
※ADOが受け取った生データを見ることはできないので、理詰め+勘で対処する
だが上記問題は、見た目数値だが文字列として扱えば問題は発生しない。
しかしそのためには物理的に頭に ' を付加し、EXCELに文字列として認識させねばならない。
VBA+ADOではセルの書式設定は無視される、と考えておけば良い。
但し別の考慮点も
今回使用したドライバではそのような動きをしたが、他のドライバでは別の動きをする可能性もある(2015年現在では{driver}は使えないので問題ないと思うが?)。するとドライバに応じた対策が必要になるケースも考慮する必要あり。
========================
EXCEL+VBA+ADO(SQL) 4つの問題点 END
========================
制約(EXCELの仕様)はまだあるので引き続き記す。
5.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にはこういった仕様もある
6.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の各種仕様(制約)を書き出すと大したことではなく、回避も容易。しかし全く知らない状態から数々の問題を解決しながら理詰めと勘を駆使し進むことは、これは非常に大きな問題であり発見に時間がかかり、参った・・という感想しかない。
次回パート3は最終回、なるべく纏めた情報を記したい。実際のVBAコードもUPする予定。
パート1はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(第1回)
パート3はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(最終回)
2015年8月4日火曜日
EXCEL2013でSQL、EXCELシート同士をクエリする(第1回)
EXCEL2013での話
< お題 >
口振の引落結果/コンビニ振替の支払結果/口振依頼データ作成/CVS振込票データ作成/全顧客の情報管理
これ等を全てをエクセルのブックだけで管理しているが、毎月2度発生する請求処理が大変。これは顧客一覧と未払者(銀行WebよりDLしたCSV)を突合し、未払額を上乗せした当月請求額を算出しなければならない。
処理としては該当する顧客を探し足し算で終わりなので、全てEXCELと手作業だけで行っている。
しかし・・、顧客は数千人、しかも毎月増え続けているので手作業での突合がとても大変。随時顧客のメンテナンスもある。よって最重要以外の処理を手順書を作ってパートさんに説明するが覚えさせるのに一苦労。そしてミス多発。
よくある話です。
私たちからすると、そんな情報や処理をエクセル+手で管理しようとするから大変になる=当たり前の結果、な訳でDBとC#があれば一瞬で解決でしょ!!
と考えるのが普通。
が、色々と制約があり社内の規定によりPCへのプログラムインストールは一切禁止。ACCESSさえ導入不可。
要するに何もインストールせずエクセルとSQL(VBA)のみを使い問題を一瞬で解決させる、そんな夢?のような話。
が、sqliteやMSのcompact DB(VSのサポートがVS2013以降なくなったが)ならインストール不要。
C#もXCOPY配置でOK??必須.NETフレームワークはMS製なのでWindowsUpdateで取込まれるから問題なし。
結論から言えば、インストール不要DB+インストール不要C# EXEを使えば問題なく対処できる可能性は高い。
しかし・・・そんな人気のない仕組みを使う=不具合で嵌ると抜け出せなくなる可能性大。ただEXCEL(VBA+ADO)も情報は非常に少ないが、どうせ嵌るならexcelの方が面白そうなので、excelで解決する手段を選択。その方がお客にも説明し易いからね。
という事で!エクセルだけを使い、他のDBなど一切使わず、エクセルシート同士をSQLでクエリし、業務を行う、ですね。
Googleで「EXCEL SQL 」とググると簡単に出来るらしい。
方法はエクセルの裏にあるVBAでADOを使うとエクセルのシートをテーブルとして扱え、すると普通にSQLが書けるので実行させ、結果をとあるシートへ貼りつけるだけ。なんだかとても簡単そう。
と思ってやってみたら、これがとんでもない!!!
joinキーがちょうどEXCELの数字制約に引っかかるケースだったため、他にもエクセルの制約に引っかかる箇所多発!最終形に落ち着くまでが非常に大変!!
結局調べ+試行錯誤で丸々1週間。SQLServer使ってSQL+C#を書けば一瞬で終わるのに、何故こんな苦労を・・・?。結果もうヘトヘト。
それとnet上に多々あるエクセル+SQL解説はとても素晴らしいHPもあるが、どうも教科書的な解説で美し過ぎる!必要なのは裏のキタナイ部分であり、発生する問題解決事例なのだか、、それ等は皆無。従って残念ながらほぼ参考にならなかった。もっとも今回EXCEL+VBA+SQL(ADO)で苦労したが、C#+DBを使えば一瞬で解決するので、やはり一般的でない事はするものではない、ようです。実はEXCEL VBA関数を応用しても出来ますが・・第3回に記す。
普段の開発?とは異なる点
・とにかくエクセルには数々の制約があり過ぎる
内部で勝手に行われる数字の自動処理が非常にやっかい
・エクセルシートは自動処理され過ぎるため普通にjoinさせるだけのSQLでも苦労する
ドライバからADOに渡される生データを見ることはできないので、ホボ勘で勝負するしかない
・ADOが古すぎる
OSはWindows10だが20年前VB6時代に使っていたado、古過ぎ
・VBAは、、、久々のVB6>VBAだが素晴らしい!!
VB6がなければ現代のプログラマ人口は1/1000?
感動的な適当さでプログラムが組め、物凄く素晴らしい事を再確認!!
VB6はソフトウェア業界最大の発明では!?
前置?が長くなったので続きは次回。 具体的な問題点などUPします。
パート2はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(第2回)
パート3はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(最終回)
============
VBAについて
============
上記ではVBAを褒めましたが、それはVB6を褒めただけ。そのVBAはVB6のサブセットのようなモノ。VB6は20年前に流行った言語(VBAは未だ生き続けているが)。よって2015年現在VBAは学習教材としては 「 最悪! で 極悪! 」
素人が遊びでやるならOK?ですが、VBAを使ってソフトの学習をしようなどと決して!考えない方が良いです。こんなに楽でいい加減に組んでも動く言語は他になく、もしこれで覚えてしまうと他の言語で超苦労、、というよりプロと言われる人々の中にも無知人間が多数存在し、その無知が作った穴だらけのシステムのせいで(特にSQL!)情報漏えいしたと騒がれている時代。楽なVBA組んで喜んでいては一生Web系アプリなど、危険過ぎ組めません。やるならC#のコンソールアプリかWindowsフォームアプリでも組んで学習しましょう。
また初心者がいきなりWeb系アプリを作ることもお勧めしません(スマホ用アプリは別)。無知なときに作ると脆弱性多々でサーバー乗っ取られるか情報漏えいして会社クビになりますよ!まず普通に言語使えるようになり、十二分にセキュリティーの勉強を行ってからWeb系アプリ、組みましょう。Web系アプリは最新技術満載でどんどん新しく進化しているので面白いです。JavaScriptなども古いですが面白く怖く、ですね。スマホアプリ開発も新時代に入ってきましたし、スマホアプリなら脆弱性などさほど気にせず組めるので良いかも?知れません。
でもそろそろ・・ロボットの時代?かも知れませんね。労働人口減少?怖いですね・・。
============
< お題 >
口振の引落結果/コンビニ振替の支払結果/口振依頼データ作成/CVS振込票データ作成/全顧客の情報管理
これ等を全てをエクセルのブックだけで管理しているが、毎月2度発生する請求処理が大変。これは顧客一覧と未払者(銀行WebよりDLしたCSV)を突合し、未払額を上乗せした当月請求額を算出しなければならない。
処理としては該当する顧客を探し足し算で終わりなので、全てEXCELと手作業だけで行っている。
しかし・・、顧客は数千人、しかも毎月増え続けているので手作業での突合がとても大変。随時顧客のメンテナンスもある。よって最重要以外の処理を手順書を作ってパートさんに説明するが覚えさせるのに一苦労。そしてミス多発。
よくある話です。
私たちからすると、そんな情報や処理をエクセル+手で管理しようとするから大変になる=当たり前の結果、な訳でDBとC#があれば一瞬で解決でしょ!!
と考えるのが普通。
が、色々と制約があり社内の規定によりPCへのプログラムインストールは一切禁止。ACCESSさえ導入不可。
要するに何もインストールせずエクセルとSQL(VBA)のみを使い問題を一瞬で解決させる、そんな夢?のような話。
が、sqliteやMSのcompact DB(VSのサポートがVS2013以降なくなったが)ならインストール不要。
C#もXCOPY配置でOK??必須.NETフレームワークはMS製なのでWindowsUpdateで取込まれるから問題なし。
結論から言えば、インストール不要DB+インストール不要C# EXEを使えば問題なく対処できる可能性は高い。
しかし・・・そんな人気のない仕組みを使う=不具合で嵌ると抜け出せなくなる可能性大。ただEXCEL(VBA+ADO)も情報は非常に少ないが、どうせ嵌るならexcelの方が面白そうなので、excelで解決する手段を選択。その方がお客にも説明し易いからね。
という事で!エクセルだけを使い、他のDBなど一切使わず、エクセルシート同士をSQLでクエリし、業務を行う、ですね。
Googleで「EXCEL SQL 」とググると簡単に出来るらしい。
方法はエクセルの裏にあるVBAでADOを使うとエクセルのシートをテーブルとして扱え、すると普通にSQLが書けるので実行させ、結果をとあるシートへ貼りつけるだけ。なんだかとても簡単そう。
と思ってやってみたら、これがとんでもない!!!
joinキーがちょうどEXCELの数字制約に引っかかるケースだったため、他にもエクセルの制約に引っかかる箇所多発!最終形に落ち着くまでが非常に大変!!
結局調べ+試行錯誤で丸々1週間。SQLServer使ってSQL+C#を書けば一瞬で終わるのに、何故こんな苦労を・・・?。結果もうヘトヘト。
それとnet上に多々あるエクセル+SQL解説はとても素晴らしいHPもあるが、どうも教科書的な解説で美し過ぎる!必要なのは裏のキタナイ部分であり、発生する問題解決事例なのだか、、それ等は皆無。従って残念ながらほぼ参考にならなかった。もっとも今回EXCEL+VBA+SQL(ADO)で苦労したが、C#+DBを使えば一瞬で解決するので、やはり一般的でない事はするものではない、ようです。実はEXCEL VBA関数を応用しても出来ますが・・第3回に記す。
普段の開発?とは異なる点
・とにかくエクセルには数々の制約があり過ぎる
内部で勝手に行われる数字の自動処理が非常にやっかい
・エクセルシートは自動処理され過ぎるため普通にjoinさせるだけのSQLでも苦労する
ドライバからADOに渡される生データを見ることはできないので、ホボ勘で勝負するしかない
・ADOが古すぎる
OSはWindows10だが20年前VB6時代に使っていたado、古過ぎ
・VBAは、、、久々のVB6>VBAだが素晴らしい!!
VB6がなければ現代のプログラマ人口は1/1000?
感動的な適当さでプログラムが組め、物凄く素晴らしい事を再確認!!
VB6はソフトウェア業界最大の発明では!?
前置?が長くなったので続きは次回。 具体的な問題点などUPします。
パート2はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(第2回)
パート3はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(最終回)
============
VBAについて
============
上記ではVBAを褒めましたが、それはVB6を褒めただけ。そのVBAはVB6のサブセットのようなモノ。VB6は20年前に流行った言語(VBAは未だ生き続けているが)。よって2015年現在VBAは学習教材としては 「 最悪! で 極悪! 」
素人が遊びでやるならOK?ですが、VBAを使ってソフトの学習をしようなどと決して!考えない方が良いです。こんなに楽でいい加減に組んでも動く言語は他になく、もしこれで覚えてしまうと他の言語で超苦労、、というよりプロと言われる人々の中にも無知人間が多数存在し、その無知が作った穴だらけのシステムのせいで(特にSQL!)情報漏えいしたと騒がれている時代。楽なVBA組んで喜んでいては一生Web系アプリなど、危険過ぎ組めません。やるならC#のコンソールアプリかWindowsフォームアプリでも組んで学習しましょう。
また初心者がいきなりWeb系アプリを作ることもお勧めしません(スマホ用アプリは別)。無知なときに作ると脆弱性多々でサーバー乗っ取られるか情報漏えいして会社クビになりますよ!まず普通に言語使えるようになり、十二分にセキュリティーの勉強を行ってからWeb系アプリ、組みましょう。Web系アプリは最新技術満載でどんどん新しく進化しているので面白いです。JavaScriptなども古いですが面白く怖く、ですね。スマホアプリ開発も新時代に入ってきましたし、スマホアプリなら脆弱性などさほど気にせず組めるので良いかも?知れません。
でもそろそろ・・ロボットの時代?かも知れませんね。労働人口減少?怖いですね・・。
============
登録:
投稿 (Atom)