SIBAINUのメモ帳

ビッグデータの抽出

ビッグデータの抽出をSQLを使用して抽出するためのプロシージャーで、接続から簡単なSQL文を作成して抽出まで行っています。

COPY

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 を常に使用することは、混合データ列のデータを取得するより安全な方法です。

COPY

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" の部分のみです。この部分では、使用するワークシートへのパスを指定します。ワークシートへのパスに空白が含まれていたらどうなるでしょう。この場合は、まったく問題がないので、次のようにファイルパス全体を空白なども一緒に記述します。

COPY

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)」と言っているので、下記のようにしない方がいいかも。

COPY

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 は省略可能です。

COPY

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];"