EXCEL VBA でEXCELデータとCSVデータをJoinして読込む方法

Question

前提

これまで２つのエクセルブックのデータをVBAでJoinし別のエクセルブックを作成していましたが
上流のシステムが変更になり、１つがCSV(UTF-8(BOM付き))のデータになりました。

CSVになったデータのプロパティ設定を変更しただけでは、エラーになり読み込めませんでした。
EXCELデータとCSVデータをJoinして読込む方法はありますでしょうか。

実現したいこと

EXCELデータとCSVデータをJoinして読込む。

EXCELデータ

項目1	項目2
A1	B1
A2	B2
A3	B3

CSVデータ
項目1,項目3
A1,C1
A2,C2

結果

項目1	項目2	項目3
A1	B1	C1
A2	B2	C2
A3	B3

※実際はWhere句でデータの絞り込み等を行っています。

発生している問題・エラーメッセージ

実行時エラー　-214721865(80040e)
オブジェクト 'C:\temp\CSV\test[BOM].CSV' が見つかりませんでした。
オブジェクトが存在していること、名前やパス名が正しいことを確認してください。

該当のソースコード

'エクセルファイル読み込み用
Dim SelFile1    As Variant
Dim ConFile1    As String
Dim cn1         As New ADODB.Connection
Dim Rs1         As New ADODB.Recordset
Dim sEXTENDED1  As String
Dim sSrcDir1    As String   ' 接続先フォルダ
Dim select1     As String
Dim FSO1, PathName1 As String, FileName1 As String

'CSVファイル読み込み用
Dim SelFile2    As Variant
Dim ConFile2    As String
Dim cn2         As New ADODB.Connection
Dim Rs2         As New ADODB.Recordset
Dim sEXTENDED2  As String
Dim sSrcDir2    As String   ' 接続先フォルダ
Dim select2     As String
Dim FSO2, PathName2 As String, FileName2 As String

Dim Wb          As Workbook

’※EXCELとCSVを個別に読込テストしたロジックも残していますので、無意味な個所もあり。
Sub TEST1()

    'エクセルファイル指定
    SelFile1 = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
    If VarType(SelFile1) = vbBoolean Then
        Exit Sub
    End If
    ConFile1 = "TEXT;" & SelFile1
    
    'CSVファイル指定
    SelFile2 = Application.GetOpenFilename("CSVファイル(*.csv),*.csv?")
    If VarType(SelFile2) = vbBoolean Then
        Exit Sub
    End If
    ConFile2 = "TEXT;" & SelFile2
    
    Set FSO1 = CreateObject("Scripting.FileSystemObject")
    FileName1 = FSO1.GetFileName(SelFile1)
    PathName1 = FSO1.GetParentFolderName(SelFile1)
    sSrcDir1 = PathName1
    
    Set FSO2 = CreateObject("Scripting.FileSystemObject")
    FileName2 = FSO2.GetFileName(SelFile2)
    PathName2 = FSO2.GetParentFolderName(SelFile2)
    sSrcDir2 = PathName2
    
    'CSV（UTF-8)用にSchema.iniを作成
    Open PathName2 & "\Schema.ini" For Output As #1
        Print #1, "[" & FileName2 & "]"
        Print #1, "CharacterSet=65001"
        Print #1, "Format = CSVDelimited"
        Print #1, "ColNameHeader = True"
        Print #1, "MaxScanRows = 0"
    Close #1
    
    ' プロパイダの設定
    cn1.Provider = "Microsoft.ACE.OLEDB.16.0"

    ' 読み込むファイルの格納フォルダのパス
    cn1.Properties("Data Source") = sSrcDir1
    
    ' その他のプロパティの設定
    sEXTENDED1 = "Excel 12.0"
    sEXTENDED1 = sEXTENDED1 & ";HDR=Yes"
    cn1.Properties("Extended Properties").Value = sEXTENDED1
    
    ' 接続開始
    cn1.Open SelFile1   

    ' プロパイダの設定
    cn2.Provider = "Microsoft.ACE.OLEDB.16.0" 
    ' 読み込むファイルの格納フォルダのパス
    cn2.Properties("Data Source") = sSrcDir2
    
    ' その他のプロパティの設定
    sEXTENDED2 = "TEXT"
    sEXTENDED2 = sEXTENDED2 & ";FMT=Delimited"
    sEXTENDED2 = sEXTENDED2 & ";HDR=Yes"
    cn2.Properties("Extended Properties").Value = sEXTENDED2
    
    ' 接続開始
    cn2.Open
     
    select1 = "SELECT t01.*,t02.*  "
    select1 = select1 & "FROM [" & SelFile1 & "].[Sheet1$]  as t01  "
    select1 = select1 & "LEFT OUTER JOIN [" & SelFile2 & "] as t02  "
    select1 = select1 & "on t01.[項目１] = t02.[項目１] "
    ' SQL実行
    Rs1.Open select1, cn1
   
    If Rs1.EOF Then
        ' 結果が1行もない場合終わり       
    Else
        ' 結果をそのまま表示
        Set Wb = Workbooks.Add
        Wb.Worksheets(1).Select
      
        Cells(20, 1).CopyFromRecordset Rs1
        'Cells(20, 20).CopyFromRecordset Rs2 '個別に読み込んだ結果の格納時に使用
        
    End If
      
    Rs1.Close
    cn1.Close
    Rs2.Close
    cn2.Close
    
PROC_EXIT:
    On Error Resume Next
    
    ' 後処理
    Set Rs1 = Nothing
    Set cn1 = Nothing
    Set Rs2 = Nothing
    Set cn2 = Nothing
    
    Exit Sub
    
PROC_ERR:
    MsgBox "ADO接続(CSV/TEXT)エラー：" & Err.Description & "(" & Err.Number & ")" & vbCrLf & sSrcDir1 & vbCrLf & sSrcDir2, vbCritical
    GoTo PROC_EXIT
    
End Sub

試したこと

Joinしなければ、EXCELデータ、CSVデータを個別には読み込むことはできました。

補足情報（FW/ツールのバージョンなど）

EXCEL 2016

Answer 1

SQLのFROM句に、異なる接続先のテーブルを指定することはできません。
CSVとエクセルシートでは別の接続先になりますので、それをJOINするのは不可能ということになります。

対処法としては、CSVファイルを同じブックのシートに読み込むか、シートをCSVファイルと同じフォルダーに出力すれば、同じ接続先になりますので、Connectionに接続すればJOINが可能になります。

別の方法としては、SQLのIN句で別の接続先を指定できるの Connection はエクセルブックでIN句でCSVに接続という方法もあります。ただし、同じFROM区内では接続先は一つという制限がありますので、IN句で接続する方はサブクエリにする必要があります。

ADOでの異なる種類のファイルへのSQL (FROM ... IN ... / INTO ... IN ...) : スクリプトちょっとメモ

Comments

[km0815.lj]

ご回答ありがとうございます。

リンク先のメモも参考になりました。
こちらの方法でも試してみます。

Answer 2

オブジェクト 'C:\temp\CSV\test[BOM].CSV' が見つかりませんでした。

CSV ファイルのパスには "[" や "]" が含まれないようにして下さい。

Sub TEST2()
On Error GoTo PROC_ERR

    Dim cn              As New ADODB.Connection
    Dim rs              As New ADODB.Recordset
    Dim sSQL            As String
    Dim oFSO            As Object
    Dim wb              As Workbook

    'エクセルファイル読み込み用
    Dim vSelFile1       As Variant
    Dim sConnectString1 As String
    Dim sPathName1      As String
    Dim sFileName1      As String
    Dim sTableName1     As String
    
    'CSVファイル読み込み用
    Dim vSelFile2       As Variant
    Dim sConnectString2 As String
    Dim sPathName2      As String
    Dim sFileName2      As String
    Dim sTableName2     As String
    
    'エクセルファイル指定
    vSelFile1 = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
    If VarType(vSelFile1) = vbBoolean Then
        Exit Sub
    End If
    
    'CSVファイル指定
    vSelFile2 = Application.GetOpenFilename("CSVファイル(*.csv),*.csv?")
    If VarType(vSelFile2) = vbBoolean Then
        Exit Sub
    End If
    
    '不正文字チェック
    If InStr(1, vSelFile2, "[", vbBinaryCompare) + InStr(1, vSelFile2, "]", vbBinaryCompare) > 0 Then
        MsgBox "パスに ""["" または ""]"" が含まれている CSV ファイルを読み込むことは出来ません。", _
               vbExclamation
        Exit Sub
    End If
    
    '取得したそれぞれのフルパスからフォルダパスとファイル名を取得
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    With oFSO
        sFileName1 = .GetFileName(vSelFile1)
        sPathName1 = .GetParentFolderName(vSelFile1)
        sFileName2 = .GetFileName(vSelFile2)
        sPathName2 = .GetParentFolderName(vSelFile2)
    End With
    Set oFSO = Nothing
    
    'CSV（UTF-8)用にSchema.iniを作成
    Open sPathName2 & "\Schema.ini" For Output As #1
        Print #1, "[" & sFileName2 & "]"
        Print #1, "CharacterSet=65001"
        Print #1, "Format = CSVDelimited"
        Print #1, "ColNameHeader = True"
        Print #1, "MaxScanRows = 0"
    Close #1
    
    With cn
        ' プロパイダの設定
        .Provider = "Microsoft.ACE.OLEDB.16.0"
    
        ' 読み込むファイルのパス
        .Properties("Data Source") = vSelFile1
        
        ' その他のプロパティの設定
        sConnectString1 = "Excel 12.0"
        sConnectString1 = sConnectString1 & ";HDR=Yes"
        .Properties("Extended Properties").Value = sConnectString1
        
        ' 接続開始
        .Open vSelFile1
    End With

    sTableName1 = "Sheet1$"

    ' csvファイルが保存されているフォルダへの接続文字列
    sConnectString2 = "TEXT"
    sConnectString2 = sConnectString2 & ";FMT=Delimited"
    sConnectString2 = sConnectString2 & ";HDR=Yes"
    sConnectString2 = sConnectString2 & ";CharacterSet=65001"
    sConnectString2 = sConnectString2 & ";DATABASE=" & sPathName2
     
    sTableName2 = sFileName2
    sTableName2 = Replace(sTableName2, ".", "#", , , vbBinaryCompare)
     
    sSQL = "SELECT t01.*,t02.* "
    sSQL = sSQL & "FROM [" & sTableName1 & "] AS t01 "
    sSQL = sSQL & "LEFT JOIN [" & sConnectString2 & "].[" & sTableName2 & "] AS t02 "
    sSQL = sSQL & "ON t01.[項目1] = t02.[項目1] "
    
    Debug.Print sSQL
    
    ' SQL実行
    rs.Open sSQL, cn
   
    If rs.EOF Then
        ' 結果が1行もない場合終わり
    Else
        ' 結果をそのまま表示
        Set wb = Workbooks.Add
        With wb.Worksheets(1).Cells(20, 1)
            .CopyFromRecordset rs
            .CurrentRegion.Select
        End With
    End If
    
PROC_EXIT:
    On Error Resume Next
    
    ' 後処理
    Set oFSO = Nothing
    Set wb = Nothing
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
    Exit Sub
    
PROC_ERR:
    MsgBox "ADO接続(CSV/TEXT)エラー：" & Err.Description & "(" & Err.Number & ")" & vbCrLf & vSelFile1 & vbCrLf & vSelFile2, _
           vbCritical
    
    Resume PROC_EXIT
End Sub

Comments

[km0815.lj]

ご回答ありがとうございます。

実際のファイルは[]ではなく()になっておりますが、エラーメッセージでは[]で表示されていました。

デバックでSQL文を表示すると以下の様になっておりました。
SELECT t01.*,t02.* FROM [C:\temp\CSV\test.xlsx].[Sheet1$] as t01 LEFT OUTER JOIN [C:\temp\CSV\test(BOM).CSV] as t02 on t01.[項目１] = t02.[項目１]

また()もやめ、testBOM.CSVとしても
「C:\temp\CSV\testBOM.CSV' が見つかりませんでした」となりました。

[sk.exe]

修正版のコードの方で実行してみて下さい。

[km0815.lj]

すみません。

記載いただいたソースで実行すれば正しく動作致しました。
これで解決しそうです。
ありがとうございました。

ひとつご確認ですが、CSVのファイル名を「.」を「#」にしているのは、なぜでしょうか。
もしよろしければご教授ねがいます。

[sk.exe]

・このコードにおいて使用されているのは Access のデータベースエンジン（ACE）である。

・Access の命名規則において、"." をオブジェクトの名前に使用しないことが推奨されている。

https://learn.microsoft.com/en-us/office/troubleshoot/access/error-using-special-characters

・ACE によってテキストファイルにアクセスする際、そのファイル名をテーブル名として扱うことになるが、ファイル拡張子（.csv, .txtなど）を含んでいるため、上記の非推奨事項に引っ掛かる。

・この場合、"." を "#" に置き換えてエスケープすることが可能である。

・実際のところ、テーブル名（ファイル名）を[]で囲んで記述していれば "." のままでも一応は SQL を実行することは出来るが、安全性を考慮して念のため "#" に置換するようにしている。

私の意図をまとめるとこういうことです。
必須というわけではないので、そちらの分かりやすい方でどうぞ。

[km0815.lj]

ご丁寧なご説明ありがとうございます。
良くわかりました。