【ExcelVBA】CSVファイルのデータを結合して対象のデータを取得する方法について(LEFT OUTER JOINを使用する)

この記事では、LEFT OUTER JOINを使ってCSVファイルのデータを結合して対象のデータを取得する方法についてご説明します。

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

【動画】LEFT OUTER JOINを使ってCSVファイルのデータを結合して対象のデータを取得する実際の動き

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

LEFT OUTER JOINを使って社員リストのCSVファイルのデータと配属部署リストのデータを外部結合し、対象のデータを取得しています。

マクロ作成の流れ

STEP.1

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

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

STEP.2

Select文を生成する

Select文を生成します。
SQLのLEFT OUTER JOINで複数のCSVファイルのデータを外部結合し対象のデータを取得するSelect文を生成します。

STEP.3

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

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

目次へ戻る

CSVファイルの例

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

CSVファイル(2つ)

社員リスト

配属部署リスト

CSVファイル「社員リスト」の「配属部署ID」(3列目の値)と、CSVファイル「配属部署リスト」の「番号」(1列目の値)がマッチするデータを抽出条件とします。

マッチしないデータは抽出されません。

目次へ戻る

Excelファイルの例

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

マクロを実行すると、CSVファイルにある番号、名前、配属部署がD列からF列に出力されます。

ちなみに下の画像はマクロを実行した結果、配属部署が営業部であることを条件に抽出・検索された結果表示された社員一覧です。

各セルの名前

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

csvFilePath

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

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ファイルの在り処
    
    Const syain_CSVFileNM As String = "社員リスト.csv"      '社員リストのCSVファイル
    Const busyo_csvFileNM As String = "配属部署リスト.csv"  '配属部署の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文を作成する
    sqlStr = "select"
    sqlStr = sqlStr & "  a.番号"
    sqlStr = sqlStr & ", a.名前"
    sqlStr = sqlStr & ", b.配属部署名"
    sqlStr = sqlStr & " from "
    sqlStr = sqlStr & syain_CSVFileNM & " a"        '1つ目のCSVファイル
    sqlStr = sqlStr & " LEFT OUTER JOIN "
    sqlStr = sqlStr & busyo_csvFileNM & " b"        '2つ目のCSVファイル
    sqlStr = sqlStr & " ON"
    sqlStr = sqlStr & " a.配属部署ID = b.番号"      '1つ目のCSVファイルの「配属部署ID」と2つ目のCSVの「番号」の値がマッチするデータを抽出条件とする
    sqlStr = sqlStr & " where "
    sqlStr = sqlStr & "b.配属部署名" & _
                      " = '" & _
                      Worksheets("top").Range("searchVal").Value & "'"
    sqlStr = sqlStr & " order by "
    sqlStr = sqlStr & " a.番号"
        
    'SQL文を実行する
    Set rs = adoCON.Execute(sqlStr)
    
    'データをシート「top」に貼り付ける
    Sheets("top").Range("D2").CopyFromRecordset rs
        
    '後処理
    adoCON.Close
    Set rs = Nothing
    Set adoCON = Nothing

End Sub

コードの解説

注目すべきコード①

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

    'データソース接続情報を取得する
    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ファイルを参照するコードはCSVファイルと外部結合させるCSVファイルの指定で説明しています。

注目すべきコード②

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

    'データを取得するSQL文を作成する
    sqlStr = "select"
    sqlStr = sqlStr & "  a.番号"
    sqlStr = sqlStr & ", a.名前"
    sqlStr = sqlStr & ", b.配属部署名"
    sqlStr = sqlStr & " from "
    sqlStr = sqlStr & syain_CSVFileNM & " a"        '1つ目のCSVファイル
    sqlStr = sqlStr & " LEFT OUTER JOIN "
    sqlStr = sqlStr & busyo_csvFileNM & " b"        '2つ目のCSVファイル
    sqlStr = sqlStr & " ON"
    sqlStr = sqlStr & " a.配属部署ID = b.番号"      '1つ目のCSVファイルの「配属部署ID」と2つ目のCSVの「番号」の値がマッチするデータを抽出条件とする
    sqlStr = sqlStr & " where "
    sqlStr = sqlStr & "b.配属部署名" & _
                      " = '" & _
                      Worksheets("top").Range("searchVal").Value & "'"
    sqlStr = sqlStr & " order by "
    sqlStr = sqlStr & " a.番号 ASC"

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

CSVファイルと外部結合させるCSVファイルの指定

1つのCSVファイルに対して別のCSVファイルを外部結合させる指定を31行目から33行目で行っています。

    sqlStr = sqlStr & syain_CSVFileNM & " a"        '1つ目のCSVファイル
    sqlStr = sqlStr & " LEFT OUTER JOIN "
    sqlStr = sqlStr & busyo_csvFileNM & " b"        '2つ目のCSVファイル

1つ目のCSVファイルの後に「LEFT OUTER JOIN」と記述して2つ目のCSVファイルを指定します。

ちなみに、1つ目のCSVファイルを「a」、2つ目のCSVファイルを「b」という別名を付けています。

この別名は1つ目のCSVファイルと2つ目のCSVファイルを外部結合したあとにSelect文に指定するフィールド名で使います。

補足

先ほどお話した「フィールド名」とは、CSVファイルのヘッダのことを指します。
データベースの場合フィールド名と言いますが、CSVファイルを扱う場合はヘッダがフィールドと同じ扱いになります。

ここからは、CSVファイルのヘッダ情報を「フィールド」と呼ぶことにします。

今回は、ある一つのCSVファイルに別のCSVファイルのデータを外部結合させていますが、Select文に指定するフィールド名がどちらのCSVファイルのフィールド名なのかが分かるように、「a.フィールド名」「b.フィールド名」というように記述します。

    sqlStr = sqlStr & "  a.番号"
    sqlStr = sqlStr & ", a.名前"
    sqlStr = sqlStr & ", b.配属部署名"

27行目から29行目のフィールド名の記述を見て頂きたいのですが、「a.番号」「a.名前」は1つ目のCSVファイル(社員リスト)の「番号」と「名前」というフィールド、「b.配属部署名」は2つ目のCSVファイル(配属部署リスト)の「配属部署名」を取得するという意味になります。

CSVファイルのデータを結合する条件の指定

CSVファイルのデータを結合する条件を、「ON」の後に指定します。(34行目)

    sqlStr = sqlStr & " ON"
    sqlStr = sqlStr & " a.配属部署ID = b.番号"      '1つ目のCSVファイルの「配属部署ID」と2つ目のCSVの「番号」の値がマッチするデータを抽出条件とする

以上は、1つ目のCSVファイルの「配属部署ID」と2つ目のCSVの「番号」の値がマッチするデータを抽出条件としています。

マッチしないデータは抽出されません。

WHERE句

36行目から39行目はWHERE句を組み立てるコードです。

このWHERE句は1つ目のCSVファイルと2つ目のCSVファイルを外部結合したあとのデータに対しての条件指定です。

2つ目のCSVファイル(配属部署リスト)の「配属部署名」の値を条件指定しています記述しています。

ORDER BY句

ORDER BY句は行の並べ替えを指定する句です。

    sqlStr = sqlStr & " order by "
    sqlStr = sqlStr & " a.番号 ASC"

今回は番号で昇順に並び替えしたいので「a.番号」でASCを指定しています。

実際のSQL文(例)

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

「番号」「名前」「配属部署名」を取得したくて、配属部署名が「営業部」の社員データを抽出する場合は次のSelect文になります。

select
    a.番号,
    a.名前,
    b.配属部署名
from
    社員リスト.csv a
    LEFT OUTER JOIN
        配属部署リスト.csv b
    ON  a.配属部署ID = b.番号
where
    b.配属部署名 = '営業部'
order by
    a.番号 ASC

注目すべきコード③

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

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

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

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

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

47行目で、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」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る

最後に

本記事では、LEFT OUTER JOINを使ってCSVファイルのデータを結合して対象のデータを取得する方法についてご説明しました。

CSVファイルのデータを外部結合してデータを集計すると便利になることがあるので参考にしてみてくださいね。

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

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

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

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

目次へ戻る