ビッグデータの抽出
ビッグデータの抽出をSQLを使用して抽出するためのプロシージャーで、接続から簡単なSQL文を作成して抽出まで行っています。
Public Sub ExcelConnect()
Const adOpenKeyset = 1
Const adOpenStatic = 3
Const adLockReadOnly = 1
Dim dbCon As Object
Dim dbRs As Object
Dim strSQL As String
Set dbCon = CreateObject("ADODB.Connection")
Set dbRs = CreateObject("ADODB.Recordset")
dbCon.Provider = "Microsoft.ACE.OLEDB.12.0"
'HDR YES:シートの１行目をヘッダ列として扱う、NO:１行目から行データとして扱う フィールド名は、F+列番号
'IMEX 0:エクスポート モード、1:インポート モード、2:リンク モード
dbCon.Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"
dbCon.Open ThisWorkbook.FullName
strSQL = ""
strSQL = strSQL & "SELECT F1, F2 "
'Sheet1のA2:B1001を対象とする
strSQL = strSQL & " FROM [Sheet1$A2:B1001] " 'Cells(1,1)からシート全体を取得したい場合 [Sheet1$]
strSQL = strSQL & " WHERE F2 > 40000 "
strSQL = strSQL & " ORDER BY F2;"
dbRs.Open strSQL, dbCon, adOpenStatic, adLockReadOnly
'HDR=YESとした場合、フィールド名をセットする
'For i = 0 To dbRs.Fields.Count - 1
' Sheet1.Cells(1, 10 +i).Value = dbRs.Fields(i).Name
'Next i
Sheet1.Cells(2, 10).CopyFromRecordset dbRs
dbRs.Close
Set dbRs = Nothing
dbCon.Close
Set dbCon = Nothing
End Sub
接続
列ヘッダーを結果セットに読み込み（ヘッダーがあっても HDR = NO を使用）、列データが数値である場合は、IMEX = 1 を使用してクラッシュを回避します。 IMEX = 1 を常に使用することは、混合データ列のデータを取得するより安全な方法です。
Excel 2007以降 Xlsx files Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx; Extended Properties="Excel 12.0 Xml;HDR=YES"; Treating data as text Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx; Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"; Xlsb files Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\myFolder\myBinaryExcel2007file.xlsb; Extended Properties="Excel 12.0;HDR=YES"; Xlsm files Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm; Extended Properties="Excel 12.0 Macro;HDR=YES";
VBScript の使用例
VBScript の使用例です。大部分が定型コードです。スクリプトの前半部分では、いくつか定数を定義し、2 つのオブジェクト( ADODB.Connection と ADODB.Recordset )を作成していることを説明するぐらいでしょう。これらのオブジェクトは、データに接続したり、データ ソースからデータを取得するのに必要です。これらの大部分は、ADOスクリプト内で手を加えないでそのまま使用する定型コードです。注意するのは、"Data Source" の部分のみです。この部分では、使用するワークシートへのパスを指定します。ワークシートへのパスに空白が含まれていたらどうなるでしょう。この場合は、まったく問題がないので、次のようにファイル パス全体を空白なども一緒に記述します。
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
'Excel 8.0は古いので下のように修正します。ｓかし・・・・
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Scripts\Test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
objConnection.Open ConnectionString
objRecordset.Open "Select * FROM [Sheet1$] Where Number = 2", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("Name"), _
objRecordset.Fields.Item("Number")
objRecordset.MoveNext
Loop
ACCESSへの接続
「Excel ではなく、Excel へのアクセスに使用される ADO プロバイダを指します。プロバイダを Excel 8.0 のままにしておくことで、すべてがうまくいきます。(https://technet.microsoft.com/ja-jp/library/ee692882.aspx)」と言っているので、下記のようにしない方がいいかも。
objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Scripts\Test.xls;" & _
"Extended Properties=""Excel 12.0;HDR=Yes;"";"
SQL構文の一例
SELECT は、出力する項目のリストアップを記述します。出力項目を シート1 の 列b 、 シート2 の 列2 及び シート1 の 列d の合計、 シート2 の 列4 の合計を項目名 合計d 、 合計4 で追加しています。
FROM シート1 LEFT JOIN シート2 ON 列a = 列1 は2つのシートのリーレーションの設定をしています。 シート1 の 列a と シート2 の 列1 で左の シート1 を中心にして照合して、該当がなくてもすべて表示するというリレーションを作っています。
シートがテーブルだということを表すためにシート名に $ を付加します。また、記述を分かりやすくするために AS を使って別名をつけています。ここでは、 シート1 を A 、 シート2 を B と別名をつけています。
WHERE でグループ化するために拾い上げる条件を指定します。ここでは、 シート1 の 列c が a であるもので、かつ シート2 の 列3 が b であるものを条件としています。
HAVING は、グループ化後に吊り上げる条件を指定します。ここでは、 シート1 の 列d の合計が 30 を超え、かつ シート2 の 列4 の合計が 100 を超えるものを吊り上げます。
ORDER BY は出力の昇順、降順を指定します。ここでは、 シート1 の 列a の昇順を指定しています。降順は項目の後に DESC を追記します。 A.[列a] DESC; のようにします。昇順を明記する場合は、 A.[列a] ASC; とします。 ASC は省略可能です。
mySQL = "SELECT A.[列b], B.[列2], SUM(A.[列d]) AS 合計d, SUM(B.[列4]) AS 合計4" mySQL = mySQL & " FROM [シート1$] AS A LEFT JOIN [シート2$] AS B ON A.[列a] = B.[列1]" mySQL = mySQL & " WHERE (A.[列c])='a' OR B.[列3])='b')" mySQL = mySQL & " GROUP BY A.[列b], B.[列2]" mySQL = mySQL & " HAVING (SUM(A.[列d]) > 30 AND SUM(B.[列4]) > 100)" mySQL = mySQL & " ORDER BY A.[列a];"