この記事では、SQLのUNION ALLで複数のCSVファイルのデータを統合し対象のデータを取得する方法についてご説明します。
今回はUNIONではなく、UNION ALLを使ってご説明します。
【動画】SQLのUNION ALLで複数のCSVファイルのデータを統合し対象のデータを取得する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
6つのCSVファイルをSQLのUNION ALLで統合し、Select文で対象のデータを検索・抽出しています。
マクロ作成の流れ
SQLのUNION ALLで複数のCSVファイルのデータを統合し対象のデータを取得するSelect文を生成します。
Select文が実行されると、Excelのシートにデータが貼り付けられます。
CSVファイルの例
今回使うテストデータのCSVファイルは次の通りです。
以上の6つのCSVファイルをマクロが読み込みUNION ALLで統合します。
マクロを実行すると、統合されたCSVファイルのデータにある各生徒の点数がExcelのシートのF列からJ列に出力されます。(D列には月、E列には生徒の名前が出力)
なお、1行目には「月」「名前」と科目名のヘッダが入力されています。
Excelファイルの例
今回は次のExcelファイルを用意しました。
マクロを実行すると、CSVファイルにある各生徒の点数がF列からJ列に出力されます。(D列には月、E列には生徒の名前が出力)
ちなみに下の画像は、名前が「生徒2」のデータを条件に指定してマクロを実行した結果です。
6つのCSVにある、名前が「生徒2」のデータを検索・抽出した結果をExcelのシートに貼り付けています。
各セルの名前
配置されているセルの名前は次の通りです。
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週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。