こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

[Excel ADO] WITH句の可否

Excel2007のADOを使ってCSVファイルのデータを集計する方法を習得中です
http://okwave.jp/qa/q9081610.htmlで質問させていただきました内容で、

uriage.csv
伝票日付,伝票番号,取引先名,明細番号,納入先コード,商品名,単位,数量,単価,金額,取引区分
270901,00000664,X社,1,0071,商品A,個,4,2250,9000,1
270901,00000664,X社,2,0071,商品B,個,4,1750,7000,1
270901,00000664,X社,3,0071,消費税,,0,0,1280,2
270903,00000739,X社,1,0067,商品C,式,1,0,0,1
270903,00000739,X社,2,0067,商品D,個,1,0,0,1
270915,00000360,X社,1,,振込,,0,0,4320,0
270915,00000363,X社,1,,振込,,0,0,78840,0
270915,00000363,X社,2,,振込,,0,0,135540,0
271022,00000831,Y社,1,0090,商品B,式,1,10000,10000,1
271022,00000831,Y社,2,0090,消費税,,0,0,800,2
271022,00000832,Y社,1,0144,商品D,個,1,2000,2000,1
271022,00000832,Y社,2,0144,消費税,,0,0,160,2
271105,00000447,Y社,1,,振込,,0,0,2160,0
271110,00000459,Y社,1,,振込,,0,0,10800,0

nonyu.csv
納入先コード,納入先名
0067,本社
0071,M支店
0084,N支店
0090,本社
0109,S支店
0144,T支店

というテーブルに対して、以下のようなSQLを発行することで、VBAによらずSQLのみで処理することができるようになりました(実際はもう少し複雑なSQLですが、質問に際して簡略化しました)

Sub sqltest()
Dim cn As ADODB.Connection
Dim rs As Recordset

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.Path & ";" & _
"Extended Properties='Text;HDR=YES'"
cn.CursorLocation = adUseClient
Sql = "SELECT * FROM ( "
Sql = Sql & "SELECT 明細番号,伝票日付,伝票番号,商品名,単位,数量,単価,IIF(取引区分>0,金額),NULL,IIF(取引区分=0,金額) FROM uriage.csv 売上"
Sql = Sql & " UNION ALL " & _
"SELECT 0,伝票日付,伝票番号,取引先名,NULL,NULL,NULL,NULL,NULL,NULL FROM uriage.csv 売上" & _
" GROUP BY 取引先名,伝票日付,伝票番号"
Sql = Sql & " UNION ALL " & _
"SELECT 99,伝票日付,伝票番号,'納入先: ' & 納入先名,NULL,NULL,NULL,NULL,SUM(金額),NULL FROM uriage.csv 売上" & _
" LEFT JOIN nonyu.csv AS 納入先台帳 ON 売上.納入先コード=納入先台帳.納入先コード" & _
" WHERE NOT ISNULL(売上.納入先コード) " & _
" GROUP BY 取引先名,伝票日付,伝票番号,納入先名"
Sql = Sql & ") ORDER BY 伝票日付,伝票番号,明細番号"

Set rs = cn.Execute(Sql)
Range("A2").CopyFromRecordset rs

End Sub

一応できたことはできたのですが、データが大量になってくるとSELECT文を3回発行しているためかレスポンスが非常に悪くなってしまっています(上記では、全データを対象にしていますが、実際はWHEREにより日付、取引先名、納入先名などで絞り込んでいます。これを3回やるのは目に見えて無駄とは思うのです)

高速化の方策をいろいろ検索してみましたところ、UNIONで繋げるのは愚策で、共通するSQL文をWITH句でまとめるのが良いという記事がありました
しかし、WITHを使おうとしてもエラーとなってしまいます。Excel ADOでは使えないのでしょうか?

あるいは、まったく異なるアプローチなど、何かありましたらご示唆ください。
よろしくお願いします

投稿日時 - 2016-08-07 13:27:03

QNo.9212118

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

こんにちは
IIFを使った方が速かったですか。
提示されたデータ量だけだとWHERE分けた方が速かったのですが、ダメですね。
あと、ORDER BY は元々UNION後でした済みません。
for文で回しているのはシートの1行目にフィールド名セットしていだけです。
コメントアウトしておいて下さい。

投稿日時 - 2016-08-10 08:05:23

ANo.4

こんにちは、間違えました。
UNION後にしたのはWHEREじゃなくてSORTです。(ORDER BY)

投稿日時 - 2016-08-09 17:07:40

お礼

すみません、これはどういう意味でしょうか?
元々のコードでも、UNION後にORDER BYをかけていたのですが。

ちなみに、for文で回している
Cells(1, i) = Replace(rs.Fields(i - 1).Name, "'", "")
は何をやっているのでしょうか?

投稿日時 - 2016-08-09 22:02:47

ANo.3

こんにちは、No2です
Oracleは持っていないですし、OraOLEDB.OracleでCSVファイルが扱えるのかも
分からないので、SQL文で・・・
UNIONするデータのフィールドでIIFで演算するのはやめてみて、
気休めでUNION後のWHEREにしてみると、
Sub sqltest_a_2()
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim Sql As String
  Dim i As Long
  Dim ss As Single
  
  Range("A1").CurrentRegion.ClearContents
  
  ss = Timer
  
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ActiveWorkbook.Path & ";" & _
    "Extended Properties='Text;HDR=YES'"
    
  cn.CursorLocation = adUseClient
  
  Sql = "SELECT 明細番号,伝票日付,伝票番号,摘要,単位,数量,単価,合計,納入先伝票計,振込計 FROM ( "
  Sql = Sql & "SELECT 明細番号,伝票日付,伝票番号,商品名 AS 摘要,単位,数量,単価,金額 AS 合計,NULL AS 納入先伝票計,0 AS 振込計 FROM uriage.csv WHERE 取引区分>0 "
  Sql = Sql & " UNION ALL "
  Sql = Sql & "SELECT 明細番号,伝票日付,伝票番号,商品名,単位,数量,単価,NULL,NULL,金額 FROM uriage.csv WHERE 取引区分=0 "
  Sql = Sql & " UNION ALL "
  Sql = Sql & "SELECT 0,伝票日付,伝票番号,取引先名,NULL,NULL,NULL,NULL,NULL,NULL FROM uriage.csv "
  Sql = Sql & "GROUP BY 取引先名, 伝票日付, 伝票番号 "
  Sql = Sql & " UNION ALL "
  Sql = Sql & "SELECT 99,伝票日付,伝票番号,'納入先: ' & 納入先名,NULL,NULL,NULL,NULL,SUM(金額),NULL FROM uriage.csv 売上 "
  Sql = Sql & "LEFT JOIN nonyu.csv AS 納入先台帳 ON 売上.納入先コード=納入先台帳.納入先コード "
  Sql = Sql & "WHERE NOT ISNULL(売上.納入先コード) "
  Sql = Sql & "GROUP BY 取引先名,伝票日付,伝票番号,納入先名 "
  Sql = Sql & ") ORDER BY 伝票日付,伝票番号,明細番号"


  Set rs = cn.Execute(Sql)
  For i = 1 To rs.Fields.Count
    Cells(1, i) = Replace(rs.Fields(i - 1).Name, "'", "")
  Next
  Range("A2").CopyFromRecordset rs

  Debug.Print Timer - ss

End Sub
多少は速くなりますか?

投稿日時 - 2016-08-09 16:50:09

お礼

コメントありがとうございます

rs.RecordCountが10万ほどのデータを対象に試してみたところ、
IIFを使った場合が、6.953125
WHEREを使った場合が、8.3125
となりました。
やはりSELECT文を増やすのは負荷になるような気がします

投稿日時 - 2016-08-09 21:53:28

ANo.2

こんにちは
何故、
VBAによらずSQLのみで処理すること
に拘るのですか?

UNION ALL で遅いのなら、個別にExcelシートに抽出してソートするとか、
VBAでMDBを作成してCSVファイルへのリンクテーブルを作成して予めクエリで
処理して取り込むとか、色々試して処理時間の比較してみてはどうですか?

投稿日時 - 2016-08-08 17:08:09

お礼

コメントありがとうございます

> VBAによらずSQLのみで処理することに拘るのですか?

あえて言えば技術的興味でしょうか
SQL処理のことを知ってまだ日が浅いですが、
手続き言語とは違う記述方法が新鮮で楽しいです
このCSV処理は業務上必要なことではありますが、
それをSQL習得のための課題ととらえ、
敢えてSQLだけでやる方法を模索しています

投稿日時 - 2016-08-08 20:41:12

ANo.1

プロバイダが"MSDAORA"だとWITH区は使えません。"OraOLEDB.Oracle"を使ってください。

投稿日時 - 2016-08-07 18:55:07

お礼

コメントありがとうございます

当方、Oracleの環境は一切所持していないのですが
フリーで入手可能なものはありますか?
また、DBMSでなくCSVファイルに対して使用可能ですか?

ざっと検索してみましたが、それらの情報が見つかりませんでした
よろしければ教えてください

投稿日時 - 2016-08-08 07:23:53

あなたにオススメの質問