【ExcelVBA】SQLのUNION ALLで複数のCSVファイルのデータを統合し対象のデータを取得する方法について

この記事では、SQLのUNION ALLで複数のCSVファイルのデータを統合し対象のデータを取得する方法についてご説明します。

今回はUNIONではなく、UNION ALLを使ってご説明します。

すぐにソースコードを見たい場合はこちらをクリック

【動画】SQLのUNION ALLで複数のCSVファイルのデータを統合し対象のデータを取得する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

6つのCSVファイルをSQLのUNION ALLで統合し、Select文で対象のデータを検索・抽出しています。

マクロ作成の流れ

STEP.1

ADOを使用してCSVファイルに接続する

ADOを使用してCSVファイルに接続します。

STEP.2

Select文を生成する

Select文を生成します。
SQLのUNION ALLで複数のCSVファイルのデータを統合し対象のデータを取得するSelect文を生成します。

STEP.3

ADODB.ConnectionオブジェクトのExecuteメソッドを呼び出してSelect文を実行する

ADODB.ConnectionオブジェクトのExecuteメソッドを呼び出してSelect文を実行します。
Select文が実行されると、Excelのシートにデータが貼り付けられます。

目次へ戻る

CSVファイルの例

今回使うテストデータのCSVファイルは次の通りです。

CSVファイル(6つ)

1月データ

2月データ

3月データ

4月データ

5月データ

6月データ

以上の6つのCSVファイルをマクロが読み込みUNION ALLで統合します。

マクロを実行すると、統合されたCSVファイルのデータにある各生徒の点数がExcelのシートのF列からJ列に出力されます。(D列には月、E列には生徒の名前が出力)

なお、1行目には「月」「名前」と科目名のヘッダが入力されています。

目次へ戻る

Excelファイルの例

今回は次のExcelファイルを用意しました。

マクロを実行すると、CSVファイルにある各生徒の点数がF列からJ列に出力されます。(D列には月、E列には生徒の名前が出力)

ちなみに下の画像は、名前が「生徒2」のデータを条件に指定してマクロを実行した結果です。

6つのCSVにある、名前が「生徒2」のデータを検索・抽出した結果をExcelのシートに貼り付けています。

【参考】名前が「生徒2」のデータ

1月データ

2月データ

3月データ

4月データ

5月データ

6月データ

各セルの名前

配置されているセルの名前は次の通りです。

csvFilePath

CSVファイルが置いてある場所を入力します。

searchFld

検索条件を指定する場合に、このセルに検索対象の項目名(フィールド名)を入力します。

searchVal

検索条件を指定する場合に、このセルに検索対象の項目名(フィールド名)に対する検索値を入力します。

目次へ戻る

コードの例

Option Explicit

Private Sub btn_getDirFileList_Click()

    Dim adoCON      As New ADODB.Connection     'Connection用変数
    Dim rs          As ADODB.Recordset          'レコードセット用変数
    Dim conStr      As String                   'データソース接続情報用変数
    Dim sqlStr      As String                   'SQL文用変数
    Dim csvFilePath As String                   'CSVファイルの在り処
    Dim csvFile()   As Variant                  'CSVファイル
    Dim cnt         As Integer                  'カウンタ
        
    '対象のCSVファイル全てを取得する
    csvFile = Array("1月データ.csv", _
                    "2月データ.csv", _
                    "3月データ.csv", _
                    "4月データ.csv", _
                    "5月データ.csv", _
                    "6月データ.csv")
    
    'CSVファイルの在り処を取得する
    csvFilePath = Worksheets("top").Range("csvFilePath").Value
    
    'データソース接続情報を取得する
    conStr = "Provider=Microsoft.ACE.OLEDB.12.0;" _
           & "Data Source=" & csvFilePath & ";" _
           & "Extended Properties=""Text;HDR=YES;FMT=Delimited"""

    'コネクションを開く
    adoCON.Open conStr
    
    'ここからデータを取得するSQL文を作成する
    
    'UNIONした結果に対してSelect文を実行するために以下の「select distinct * from」を用意する
    sqlStr = "select distinct * from ("
    
    'UNIONするSelect文を組み立てる
    For cnt = 0 To UBound(csvFile)
    
        If cnt > 0 Then
        
            '2件目のSELECT文の前にUnion ALLを付与する
            sqlStr = sqlStr & " Union ALL"
            
        End If
                
        sqlStr = sqlStr & " select"
        sqlStr = sqlStr & " *"
        sqlStr = sqlStr & " from "
        sqlStr = sqlStr & csvFile(cnt)
        
    Next cnt
    
    'UNIONした結果に対してSelect文を実行するための閉じ括弧
    sqlStr = sqlStr & ")"
    
    'WHERE句
    sqlStr = sqlStr & " where "
    
    Select Case Worksheets("top").Range("searchFld").Value
    
        Case "月", "名前"
        
            '月か名前の場合(文字列データ)
        
            sqlStr = sqlStr & Worksheets("top").Range("searchFld").Value & _
              " = '" & _
              Worksheets("top").Range("searchVal").Value & "'"
        
        Case Else
            
            '月か名前以外場合(数値データ)
            
            sqlStr = sqlStr & Worksheets("top").Range("searchFld").Value & _
                          " = " & _
                          Worksheets("top").Range("searchVal").Value
    
    End Select
    
    'SQL文を実行する
    Set rs = adoCON.Execute(sqlStr)
    
    'データをシート「top」に貼り付ける
    Sheets("top").Range("D2").CopyFromRecordset rs
        
    '後処理
    adoCON.Close
    Set rs = Nothing
    Set adoCON = Nothing

End Sub

コードの解説

注目すべきコード①

最初に見て頂きたいのは22行目から29行目です。

    'CSVファイルの在り処を取得する
    csvFilePath = Worksheets("top").Range("csvFilePath").Value
    
    'データソース接続情報を取得する
    conStr = "Provider=Microsoft.ACE.OLEDB.12.0;" _
           & "Data Source=" & csvFilePath & ";" _
           & "Extended Properties=""Text;HDR=YES;FMT=Delimited"""
    'コネクションを開く
    adoCON.Open conStr

以上のコードは、CSVファイルの在り処を元に、マクロがデータソース接続情報を取得して接続するコードです。

以上のコードを実行することで、CSVファイルに接続する準備が整いました。

ポイントは、CSVファイル名を以上のコードで明記するのではなく、CSVファイルの置き場(csvFilePath)を指定しているところです。

CSVファイルを参照するコードはFROM句をご覧ください。

注目すべきコード②

次に見て頂きたいのは28行目から52行目です。

    'ここからデータを取得するSQL文を作成する
    
    'UNIONした結果に対してSelect文を実行するために以下の「select distinct * from」を用意する
    sqlStr = "select distinct * from ("
    
    'UNIONするSelect文を組み立てる
    For cnt = 0 To UBound(csvFile)
    
        If cnt > 0 Then
        
            '2件目のSELECT文の前にUnion ALLを付与する
            sqlStr = sqlStr & " Union ALL"
            
        End If
                
        sqlStr = sqlStr & " select"
        sqlStr = sqlStr & " *"
        sqlStr = sqlStr & " from "
        sqlStr = sqlStr & csvFile(cnt)
        
    Next cnt
    
    'UNIONした結果に対してSelect文を実行するための閉じ括弧
    sqlStr = sqlStr & ")"
    
    'WHERE句
    sqlStr = sqlStr & " where "
    
    Select Case Worksheets("top").Range("searchFld").Value
    
        Case "月", "名前"
        
            '月か名前の場合(文字列データ)
        
            sqlStr = sqlStr & Worksheets("top").Range("searchFld").Value & _
                     " = '" & _
                     Worksheets("top").Range("searchVal").Value & "'"
        
        Case Else
            
            '月か名前以外場合(数値データ)
            
            sqlStr = sqlStr & Worksheets("top").Range("searchFld").Value & _
                     " = " & _
                     Worksheets("top").Range("searchVal").Value
    
    End Select

以上のコードは、Select文を生成するコードです。

UNION ALLで統合したSelect文に対するSelect

35行目では、UNION ALLで6つのCSVファイルを統合したSelect文の結果に対してWHERE句を使うために「select distinct * from」で囲むSQL文を用意します。(distinctは重複するデータを取り除くために使用)

    'UNIONした結果に対してSelect文を実行するために以下の「select distinct * from」を用意する
    sqlStr = "select distinct * from ("

閉じ括弧は55行目で記述しています。

    'UNIONした結果に対してSelect文を実行するための閉じ括弧
    sqlStr = sqlStr & ")"

6つのCSVファイルをUNION ALLで統合

38行目から52行目では、Select文をUNION ALLで統合するSQL文です。

今回は6つのCSVファイルを統合するので、6回ループさせます。

    'UNIONするSelect文を組み立てる
    For cnt = 0 To UBound(csvFile)

CSVファイルが2つ以上のある場合は、2つ目以降にUnion ALLを付けます。(40行目から45行目)

        If cnt > 0 Then
        
            '2件目のSELECT文の前にUnion ALLを付与する
            sqlStr = sqlStr & " Union ALL"
            
        End If

FROM句

FROM句の指定は49行目と50行目で行っています。

        sqlStr = sqlStr & " from "
        sqlStr = sqlStr & csvFile(cnt)

配列「csvFile」にCSVファイルが格納されています。

WHERE句

58行目以降はWHERE句を組み立てるコードですが、文字列データの場合は「’」で値を囲まないといけないので、文字列データと数値データそれぞれにコードを用意する必要があります。

文字列データの場合は66行目から68行目、数値データの場合は74行目から76行目のコードが呼ばれます。

実際のSQL文(例)

コードだけではSelect文が分かりづらいと思うので、Select文の例を以下にお見せします。

検索対象の項目が文字列データの場合

6つのCSVファイルのデータを統合して、名前が「生徒2」のデータを抽出する場合は次のSelect文になります。

select distinct
    *
from
    (
        select
            *
        from
            1月データ.csv
        Union ALL
        select
            *
        from
            2月データ.csv
        Union ALL
        select
            *
        from
            3月データ.csv
        Union ALL
        select
            *
        from
            4月データ.csv
        Union ALL
        select
            *
        from
            5月データ.csv
        Union ALL
        select
            *
        from
            6月データ.csv
    )
where
    名前 = '生徒2'

検索対象の項目が数値データの場合

6つのCSVファイルのデータを統合して、「英語」の点数が「72」のデータを抽出する場合は次のSelect文になります。

select distinct
    *
from
    (
        select
            *
        from
            1月データ.csv
        Union ALL
        select
            *
        from
            2月データ.csv
        Union ALL
        select
            *
        from
            3月データ.csv
        Union ALL
        select
            *
        from
            4月データ.csv
        Union ALL
        select
            *
        from
            5月データ.csv
        Union ALL
        select
            *
        from
            6月データ.csv
    )
where
    英語 = 72

注目すべきコード③

次に見て頂きたいのは81行目から84行目です。

    'SQL文を実行する
    Set rs = adoCON.Execute(sqlStr)
    
    'データをシート「top」に貼り付ける
    Sheets("top").Range("D2").CopyFromRecordset rs

以上はSelect文を実行し、CSVファイルから検索・抽出したデータをExcelのシートに貼り付けるコードです。

81行目では、Executeの引数にSelect文を指定してExecuteを呼び出すことでSelect文が実行されます。

抽出されたデータはRecordsetから取得することができます。

84行目で、Recordsetからデータを取り出してExcelのシートに貼り付けています。

目次へ戻る

動作確認

マクロを実行した実行結果は、本記事内のExcelファイルの例の内容をご覧ください。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」と6行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。

    Dim adoCON      As New ADODB.Connection     'Connection用変数
    Dim rs          As ADODB.Recordset          'レコードセット用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る

最後に

本記事では、SQLのUNION ALLで複数のCSVファイルのデータを統合し対象のデータを取得する方法についてご説明しました。

複数のCSVファイルから検索対象のデータをまとめてExcelのシートに取得したい時はSQLのUNION ALL(またはUNION)を使うと便利になる場合があるので参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間無料体験」はこちら

目次へ戻る