【エクセルVBA】CSVをエクセルに読み込む（ADODB – Recordset）

Sub Test()
    Const CSV_FILE_PATH As String = "C:\00_myenv\10_macro\01_test\厄介なCSV.csv"
    Call ReadCSV_WorkbooksOpen(CSV_FILE_PATH, ThisWorkbook.Worksheets("WorkbooksOpen"))
End Sub

'「エクセルブック」としてCSVを読み込む
Sub ReadCSV_WorkbooksOpen(ByVal csvFilePath As String, ByRef outputWs As Worksheet)
    With Workbooks.Open(Filename:=csvFilePath)
        .Worksheets(1).Cells(1, 1).CurrentRegion.Copy outputWs.Cells(1, 1)
        .Close
    End With
End Sub

Sub Test()
    Const CSV_FILE_PATH As String = "C:\00_myenv\10_macro\01_test\厄介なCSV.csv"
    Call ReadCSV_OpenStatement(CSV_FILE_PATH, ThisWorkbook.Worksheets("OpenStatement"))
End Sub

'「Openステートメント」でCSVを読み込む
Sub ReadCSV_OpenStatement(ByVal csvFilePath As String, ByRef outputWs As Worksheet)

    Open csvFilePath For Input As #1
    
    Dim i As Long: i = 1
    Do Until EOF(1)
        Dim oneLine As String
        Line Input #1, oneLine                             '1行読み込み
        
        Dim oneLineArr() As String
        oneLineArr = Split(oneLine, ",")                   'カンマ区切りで配列に格納
        
        Dim j As Long
        For j = 0 To UBound(oneLineArr)
            outputWs.Cells(i, j + 1).NumberFormat = "@"    'セルの書式を「文字列」に設定
            outputWs.Cells(i, j + 1).Value = oneLineArr(j)
        Next j
        i = i + 1
    Loop
    Close #1
End Sub

Sub Test()
    Const CSV_FILE_PATH As String = "C:\00_myenv\10_macro\01_test\厄介なCSV.csv"
    Call ReadCSV_FileSystemObject(CSV_FILE_PATH, ThisWorkbook.Worksheets("FileSystemObject"))
End Sub

'「FileSystemObject.OpenTextFile」でCSVを読み込む
Sub ReadCSV_FileSystemObject(ByVal csvFilePath As String, ByRef outputWs As Worksheet)
    With CreateObject("Scripting.FileSystemObject").OpenTextFile(csvFilePath, 1)
        Dim i As Long: i = 1
        Do While .AtEndOfStream = False
            Dim oneLineArr() As String
            oneLineArr = Split(.ReadLine, ",")                 '1行読み込み、カンマ区切りで配列に格納
            
            Dim j As Long
            For j = 0 To UBound(oneLineArr)
                outputWs.Cells(i, j + 1).NumberFormat = "@"    'セルの書式を「文字列」に設定
                outputWs.Cells(i, j + 1).Value = oneLineArr(j)
            Next j
            i = i + 1
        Loop
        .Close
    End With
End Sub

Sub Test()
    Const CSV_FILE_PATH As String = "C:\00_myenv\10_macro\01_test\厄介なCSV.csv"
    Call ReadCsv_QueryTables(CSV_FILE_PATH, ThisWorkbook.Worksheets("QueryTables"))
End Sub

Sub ReadCsv_QueryTables(ByVal csvFilePath As String, ByRef outputWs As Worksheet)
    With outputWs.QueryTables.Add(Connection:="Text;" & csvFilePath, Destination:=outputWs.Range("A1"))
        .AdjustColumnWidth = False
        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2)    '各カラムの型に「文字列（2）」を指定
        .TextFilePlatform = 65001                                '文字コードに「UTF-8（65001）」を指定
        .TextFileCommaDelimiter = True
        .RefreshStyle = xlOverwriteCells
        .Refresh BackgroundQuery:=False
        .Delete
    End With
End Sub

Sub Test()
    Const CSV_FILE_PATH As String = "C:\00_myenv\10_macro\01_test\厄介なCSV.csv"
    Call ReadCsv_ADODBStream(CSV_FILE_PATH, ThisWorkbook.Worksheets("ADODBStream"))
End Sub

Sub ReadCsv_ADODBStream(ByVal csvFilePath As String, ByRef outputWs As Worksheet)
    With CreateObject("ADODB.Stream")
        .charSet = "UTF-8"                                     '文字コードに「UTF-8」を指定
        .LineSeparator = 10                                    '改行コードに「LF」を指定
        .Open
        .LoadFromFile csvFilePath
        
        Dim i As Long: i = 1
        Do Until .EOS
            Dim oneLineArr As Variant
            oneLineArr = Split(.ReadText(-2), ",")             '1行読み込み、カンマ区切りで配列に格納

            Dim j As Long
            For j = 0 To UBound(oneLineArr)
                outputWs.Cells(i, j + 1).NumberFormat = "@"    'セルの書式を「文字列」に設定
                outputWs.Cells(i, j + 1).Value = oneLineArr(j)
            Next j
            i = i + 1
        Loop
        .Close
    End With
End Sub

Sub Test()
    Const CSV_FILE_PATH As String = "C:\00_myenv\10_macro\01_test\厄介なCSV.csv"
    Call ReadCSV_ADODBRecordset(CSV_FILE_PATH, ThisWorkbook.Worksheets("ADORecordset"))
End Sub

Sub ReadCSV_ADODBRecordset(ByVal csvFilePath As String, ByRef outputWs As Worksheet)
    'CSVのファイル名を取得
    Dim csvFileName As String
    csvFileName = CreateObject("Scripting.FileSystemObject").GetFileName(csvFilePath)
    
    'CSVのフォルダパスを取得
    Dim csvFolderPath As String
    csvFolderPath = CreateObject("Scripting.FileSystemObject").GetParentFolderName(csvFilePath)

    'SQL文の準備
    'FROM句は、CSVのファイル名を"[]"で括る
    Dim sql As String
    sql = ""
    sql = sql & "SELECT * "
    sql = sql & "FROM [" & csvFileName & "]"
    
    'CSVへのConnectionを生成
    Dim adoCon As Object
    Set adoCon = CreateObject("ADODB.Connection")
    
    With adoCon
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties").Value = "Text"
        .Properties("Data Source").Value = csvFolderPath    'CSVのフォルダパスを指定
        .Open
    End With
    
    'Recordsetを生成し、SQLを実行
    Dim adoRs As Object
    Set adoRs = CreateObject("ADODB.Recordset")
    adoRs.Open sql, adoCon

    'ヘッダー部の出力
    Dim i As Long: i = 1
    Dim field As Variant
    For Each field In adoRs.Fields
        outputWs.Cells(1, i) = field.Name
        i = i + 1
    Next field

    'データ部の出力
    outputWs.Range("A2").CopyFromRecordset adoRs
    outputWs.Range("A1").CurrentRegion.NumberFormat = "@"    'セルの書式を「文字列」に設定
    
    adoCon.Close
    Set adoRs = Nothing
    Set adoCon = Nothing
End Sub

;CSVファイル名の指定。[]で括る。
[厄介なCSV.csv]

;区切り文字の指定
;CSVDelimited：カンマ区切り
;TabDelimited：タブ区切り
Format=CSVDelimited

;ファイルの文字コードの指定
;UTF-8：65001
;SJIS:932
;EUCJP：51932
CharacterSet=65001

;CSVのヘッダー部有無の指定
ColNameHeader=True

;以降、CSVのカラム名と読み込み時のデータ型を指定
;今回は全てテキストとする
Col1=No Text
Col2=商品コード Text
Col3=識別コード Text
Col4=商品名 Text
Col5=発売日 Text
Col6=価格 Text
Col7=備考 Text

[サンプル１.csv]
Format=CSVDelimited
CharacterSet=65001
ColNameHeader=True
Col1=XXX Text
Col2=XXX Text
Col3=XXX Text

[サンプル２.csv]
Format=CSVDelimited
CharacterSet=65001
ColNameHeader=True
Col1=XXX Text
Col2=XXX Text
Col3=XXX Text

・
・
・