【VBA】SQL使ってcsvをJOINする

VBAでSQLを使う準備

JOIN to the love 眠れない～♪

どうも。jimです(〃▽〃)

今回は、前回の予告通り『SQL』について記事を書きます😊

普通に書いても面白くないので、JOINまで紹介っていうオマケ（メイン？）付き🤘

SQLは、奥が深いし難しい。でも便利。。。

ってことで、SQLをVBAで使っていきます。（初心者にはハードなので注意）

自宅のパソコンにAccessないから、csvをデータベースに見立てて説明します。

先ずは、csvの準備。。。

『受付日』に、先週から今週の日付があるデータです。

このcsvから、『前回の記事の条件（受付日が先週分）』のデータを読み込むSQLを記述します。

んで、コードを記述していくわけだけど、もう一つだけVBEで準備が必要です。

参照設定から『ActiveX Data Objects』にチェックを入れます。

『ActiveX Data Objects』は、

簡単に言えば、データベース操作する時のために、Microsoftが用意してるAPI。

これでデータベースを操作できるようになりました👍

 

SQL

とりあえず、例の条件からSQLを書いてみます。

↓条件のおさらい

前回の記事の条件＋α

・受付日が先週

・取り出したい項目は顧客IDとお客様名と電話番号と受付日（追加）

・お客様名を降順

↓SQL

SELECT 顧客ID,お客様名,電話番号,受付日 FROM リスト.csv WHERE 受付日 BETWEEN #2020/07/13# AND #2020/07/19# ORDER BY カタカナ DESC;

んで、以下は上記SQL文の構成説明、、、

・SELECTのあとに欲しいフィールド名

・FROMのあとにテーブル名

・WHEREのあとに条件

って感じだね😄

今回は条件が厄介？かな？

↓こんな感じで、２つの変数に日付（開始日＆終了日）ぶち込みました。

Dim i As Long
Dim SDate, EDate As Date
    For i = 0 To 5
        EDate = DateAdd("d", -i, Date)
        If Weekday(EDate) = vbSunday Then
            SDate = DateAdd("d", -6, EDate)
            Exit For
        End If
    Next

『BETWEEN #2020/07/13# AND #2020/07/19#』

SQLの赤字部分を計算するために、日付型の変数を二つ用意します。

んで、直近の日曜日を検索して変数にぶち込みます。これが、最終日。

それでもって、その最終日から６日前を変数にぶち込む。これが、開始日。

↓あとは、ADO使う時の決まり文句？のコード＆説明。

Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim DBPath As String
    DBPath = "csvがあるディレクトリ指定\"
    Set CN = New ADODB.Connection
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & DBPath & ";Extended Properties='Text;HDR=YES'"
    '---SQL文長いので変数使って組み立てます---
    Set RS = New ADODB.Recordset
    Set RS = CN.Execute(SQL文)

・まず、7行目までの説明。

ADOコネクションオブジェクト用の変数を用意して、SetでADOコネクションオブジェクトを作る。

そして、OpenでMicrosoft～ってドライバー使って、csvがあるディレクトリ（データベース）に接続します。

・次に、9～10行の説明。

んで、ADOレコードセットオブジェクト用の変数用意しておいて、setでADOレコードセットオブジェクト作って、SQLを実行してADOレコードセットにぶち込む。

↓最後にSQL文入れた通し＋項目と貼り付けまで。

Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim FL As ADODB.Field
Dim MYStr As String
Dim DBPath As String
Dim DBName As String
Dim TBook As Workbook
Dim PSheet, FSheet As Worksheet
Dim i As Long
Dim SDate, EDate As Date
    Set TBook = ThisWorkbook
    Set PSheet = TBook.Worksheets("ぺたん")
    For i = 0 To 5
        EDate = DateAdd("d", -i, Date)
        If Weekday(EDate) = vbSunday Then
            SDate = DateAdd("d", -6, EDate)
            Exit For
        End If
    Next
    DBPath = "csvがあるディレクトリ指定\"
    DBName = "リスト.csv" 'csv=テーブルと思ってください
    Set CN = New ADODB.Connection
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & DBPath & ";Extended Properties='Text;HDR=YES'"
    MYStr = "SELECT 顧客ID,お客様名,電話番号,受付日 FROM " & DBName
    MYStr = MYStr & " WHERE 受付日 BETWEEN #" & SDate & "# AND #" & EDate & "# ORDER BY カタカナ DESC;"
    Set RS = New ADODB.Recordset
    Set RS = CN.Execute(MYStr)
'    RS.Open MYStr, CN
    i = 1
    For Each FL In RS.Fields
        PSheet.Cells(1, i) = FL.Name
        i = i + 1
    Next
    PSheet.Range("A2").CopyFromRecordset RS

実行すると『ぺたん』シートにデータが貼り付きます😝

 

LEFT JOIN

オマケのLEFT JOINです😊

分かりやすく例えると、Excel関数のVlookupと一緒です👀

ちなみに、INNER JOINは、Vlookupで「#N/A」が出た行が削除された感じのデータになります。

以下のようなcsvを作成して、『リスト.csv』と同一階層に格納します。

↓んでもって、LEFT JOIN使ったSQL入りのコード。

Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim FL As ADODB.Field
Dim MYStr As String
Dim DBPath As String
'Dim DBName1, DBName2 As String
Dim TBook As Workbook
Dim PSheet, FSheet As Worksheet
Dim i As Long
Dim SDate, EDate As Date
    Set TBook = ThisWorkbook
    Set PSheet = TBook.Worksheets("ぺたん")
    For i = 0 To 5
        EDate = DateAdd("d", -i, Date)
        If Weekday(EDate) = vbSunday Then
            SDate = DateAdd("d", -6, EDate)
            Exit For
        End If
    Next
    DBPath = "csvがあるディレクトリ指定\"
'    DBName1 = "リスト.csv"
'    DBName2 = "商品.csv"
    Set CN = New ADODB.Connection
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & DBPath & ";Extended Properties='Text;HDR=YES'"
    MYStr = "SELECT [リスト#csv].顧客ID,[リスト#csv].お客様名,[リスト#csv].電話番号,[リスト#csv].受付日,[商品#csv].商品名"
    MYStr = MYStr & " FROM リスト.csv LEFT JOIN 商品.csv ON [リスト#csv].商品番号 = [商品#csv].商品番号"
    MYStr = MYStr & " WHERE [リスト#csv].受付日 BETWEEN #" & SDate & "# AND #" & EDate & "# ORDER BY [リスト#csv].カタカナ DESC;"
    Set RS = New ADODB.Recordset
    Set RS = CN.Execute(MYStr)
'    RS.Open MYStr, CN
    i = 1
    For Each FL In RS.Fields
        PSheet.Cells(1, i) = FL.Name
        i = i + 1
    Next
    PSheet.Range("A2").CopyFromRecordset RS

テーブル名を変数にすると、SQLが半端なく長くなるので変数止めました。（コメントアウト）

↓それでもSQL文は長くなるねぇ。。。

SELECT [リスト#csv].顧客ID,[リスト#csv].お客様名,[リスト#csv].電話番号,[リスト#csv].受付日,[商品#csv].商品名 FROM リスト.csv LEFT JOIN 商品.csv ON [リスト#csv].商品番号 = [商品#csv].商品番号 WHERE [リスト#csv].受付日 BETWEEN #2020/07/13# AND #2020/07/19# ORDER BY [リスト#csv].カタカナ DESC;

長文だけど、簡単に構成を説明すると、、、

・SELECTの後に欲しいフィールド名（どっちのcsvか指定したもの）

・FROMのあとにテーブル名

・LEFT JOINのあとに関連付けしたいテーブル名

・ONのあとに関連付けするフィールドの指定

・WHEREのあとに条件（どっちのcsvか指定したもの

csv（テーブル）が増えた分、条件の指定を細かくする感じですね。

↓実行するとこんな。

INNER JOINは、商品名が空白になってる行が無いデータになります🙈

 

最後に

今回はAccessが用意できなかったので、csvをデータベースに見立てて、VBAでSQL使ってみました😊

ちょっと難易度高めだけど、便利だと思いませんか？

ちなみに、Excelもcsv同様にデータベースに見立てることができます。そして、今回のようにSQLが使えます。

機会があれば使ってみましょう♩

それでは、長々とお付き合いありがとうございました😂

またねぇ✋

 

SQL,VBA,プログラミング

Posted by jim