[Excel][VBA] Schema.iniを用意してADODB.RecordsetでCSVを読み込む試み

0.README.md

[Excel][VBA] Schema.iniを用意してADODB.RecordsetでCSVを読み込む試み

フォローしている方々の間でCSVの取り扱いについて盛り上がっているみたいだったので（発端は吉田 拳さんのツイート？）、以前目にしたもののスルーしていたSchema.iniを用いる方法を試してみました。

※その後、独自のCsvParserクラスモジュールを試作してみました
ただし、そちらはパフォーマンスが低下してしまうため、

• 文字数制限(1セル255文字まで)
• 列数制限(最大255列まで)

という制限があっても問題なければ、こちらの方法（ADODB.Recordsetを用いてCSVファイルの内容をSELECTで取得）がいいかと思われます。

試し方

• CSV読み込み.xlsm（標準モジュールMod_ImportCsvToRangeをVBAに貼り付けたマクロ有効ワークシート）
• Schema.ini
• utf8_001.csv
• sjis_001.csv

※こちらからダウンロードできます

を同じフォルダに置き、TestImportCsvToRangeプロシージャを実行すると、WorkSheet上に

こんな感じで、Schema.iniでの定義を元にしてCSVファイルが読み込まれます。

エクセルで Schema.ini を使う場合のメリット／デメリット

良い点

• CSVファイル毎に、各列の型を明示可能（頭に0の付いた数値を文字列として扱ったりできる）

イマイチな点

• CSVファイル毎に設定を記述する必要あり(ワイルドカード指定などは不可)→管理が煩雑化しそう？
• VBA（ADODB）必須（Excelで普通に開いたり、PowerQueryで読み込んだりする場合には、設定は無視される）
• 最大列数が255等の制限あり

Schema.iniを半自動で作るようなツールがあればいいのかな？（予めテンプレートを作っておいて、指定フォルダ下にあるすべてのCSVファイルを指定したSchema.iniを出力、みたいな）

ソースコード

• ImportCsvToRange()を含む標準モジュール(Mod_ImportCsvToRange)
• テスト用標準モジュール(Mod_TestImport)
• テスト用CSVファイル(同じ内容を「utf8_001.csv」(UTF-8)/「sjis_001.csv」(Shift-JIS)でそれぞれ保存
• テスト用Schema.ini (※テスト用CSVファイルと同じところにShift-JISで保存のこと）

参考

MS公式ページ

• Schema.ini ファイル (テキストファイルドライバー) - Open Database Connectivity (ODBC) | Microsoft Docs
• Schema.ini File (Text File Driver) - Open Database Connectivity (ODBC) | Microsoft Docs

相変わらずわかりにくいですね……。

Schema.ini関連記事

• Access VBA CSVのインポートとエクスポートをSchema.iniとクエリで行う Import and Export Text DB file with Query - Qiita
• Accessでお仕事 テキスト データ ソース ドライバと HTML データ ソース ドライバの初期化

Microsoft.ACE.OLEDBのProvider指定に関する注意など

• Microsoft.ACE.OLEDBについてまとめてみた - Qiita

Mod_ImportCsvToRange.vba

Option Explicit

' 以下の参照設定が必要
' - Microsoft Scripting Runtime
' - Microsoft ActiveX Data Objects 6.1 Library

Sub ImportCsvToRange(CsvFilePath, Optional TargetRange As Range)
    If TargetRange Is Nothing Then Set TargetRange = ActiveSheet.Range("A1")
    
    Dim Fso As FileSystemObject: Set Fso = New FileSystemObject
    Dim CsvFolderPath: CsvFolderPath = Fso.GetParentFolderName(CsvFilePath)
    Dim CsvFileName: CsvFileName = Fso.GetFileName(CsvFilePath)
    Dim DBConnection As ADODB.Connection: Set DBConnection = New ADODB.Connection
    Dim DBRecordset As ADODB.Recordset: Set DBRecordset = New ADODB.Recordset

    With DBConnection
        '【覚書】
        '  [Microsoft.ACE.OLEDBについてまとめてみた - Qiita](https://qiita.com/yaju/items/7b0aa9e9f30005f60388)
        '  によると、環境によって使用可能なProviderが異なってくるらしい
        Select Case CLng(Application.Version)
            Case Is < 15 ' Office 2010以前
#If Win64 Then
                ' 64bit版Office(※64bit版では"Microsoft.Jet.OLEDB.4.0"が未提供)
                .Provider = "Microsoft.ACE.OLEDB.12.0"
#Else
                ' 32bit版Office(※自分の環境ではExcel 2010(32bit)で"Microsoft.ACE.OLEDB.12.0"を使うと、"Data Source"指定時にエラーも出さずExcelが落ちてしまう)
                .Provider = "Microsoft.Jet.OLEDB.4.0"
#End If
            Case Is > 15 ' Office 2016以降
                .Provider = "Microsoft.ACE.OLEDB.16.0"
            Case Else ' Office 2013
                .Provider = "Microsoft.ACE.OLEDB.15.0" ' 動作未確認(※自分の環境ではMS365(32bit)Office2019(64bit)だと実行時エラー3706「プロバイダーが見つかりません」)
        End Select
        .Properties("Data Source") = CsvFolderPath
'        .Properties("Extended Properties") = "Text; FMT=Delimited; HDR=Yes"
        .Properties("Extended Properties") = "Text" ' Schema.iniで定義すること前提なら FMTやHDRは不要
        .Open
        With DBRecordset
            .Open "SELECT * FROM " & CsvFileName, _
                ActiveConnection:=DBConnection, _
                CursorType:=adOpenStatic, _
                LockType:=adLockReadOnly, _
                Options:=adCmdText
            
            Dim FieldDict As Dictionary: Set FieldDict = New Dictionary
            Dim WorkField As ADODB.Field
            For Each WorkField In .Fields
                FieldDict(FieldDict.Count) = WorkField.Name
            Next
            TargetRange.Resize(1, FieldDict.Count) = FieldDict.Items
            TargetRange.Resize(1, 1).Offset(1, 0).CopyFromRecordset Data:=DBRecordset
            .Close
        End With
        .Close
    End With
End Sub

Mod_TestImport.vba

Option Explicit

Sub TestImportCsvToRange()
    Dim CsvNames: CsvNames = VBA.Array("sjis_001", "utf8_001")
    Dim CsvName
    Dim TargetSheet As Worksheet
    
    For Each CsvName In CsvNames
        Set TargetSheet = GetSheet(CsvName)
        Call TargetSheet.Cells.Clear
        Call ImportCsvToRange(ThisWorkbook.Path & "\" & CsvName & ".csv", TargetSheet.Cells(1, 1))
        Call TargetSheet.UsedRange.EntireColumn.AutoFit
    Next
End Sub

Private Function GetSheet(TargetSheetName) As Worksheet
    Dim TargetSheet As Worksheet
    On Error Resume Next
    With ThisWorkbook
        Set TargetSheet = .Worksheets(TargetSheetName)
        If Err.Number <> 0 Then
            Set TargetSheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            TargetSheet.Name = TargetSheetName
        End If
    End With
    Set GetSheet = TargetSheet
End Function

Schema.ini

; - Schema.ini 自身はANSI(Shift-JIS)で保存すること
; - Schema.ini は対象ファイルと同じフォルダに置くこと
; - Microsoft公式
;   - [英語版](https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver15)
;   - [日本語版](https://docs.microsoft.com/ja-jp/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver15)
; - 有用そうな記事(公式はわかりにくい＆誤りも多そう？)
;   - [Access VBA CSVのインポートとエクスポートをSchema.iniとクエリで行う Import and Export Text DB file with Query - Qiita](https://qiita.com/Q11Q/items/9c93552a0efd49d06fc4#%E9%8D%B5%E3%82%92%E6%8F%A1%E3%82%8B%E3%81%AE%E3%81%AFschemaini)
;   - [Accessでお仕事 テキスト データ ソース ドライバと HTML データ ソース ドライバの初期化](http://makoto-watanabe.main.jp/access/dahowchangingtext.htm#start)

[sjis_001.csv]
CharacterSet=932
Format=CSVDelimited
ColNameHeader=True
Col1=ID Text
Col2=日付 Date
Col3=数値 Long
Col4=文字列 Text

[utf8_001.csv]
CharacterSet=65001
Format=CSVDelimited
ColNameHeader=True
Col1=ID(4桁) Char Width 4
Col2=日付 Date
Col3=数値 Long
Col4=文字列 Text

utf8_001.csv