ADO（ActiveX Data Objects）とSQLを使ったCSVファイルの読込の基本方法について紹介します。
全体の流れ
フォルダに格納されているCSVデータをExcelファイルに読み込みます。
フォルダへの接続とファイルのデータの読込にADOを使用します。
詳細
「CSVデータ」フォルダの中に「くだもの」「やさい」「文房具」のCSVファイルがあります。
CSVファイルには簡単な表（テーブル）があります。以下は文房具.csvの内容です。
上記のようなCSVファイルのデータをSQLで読み込んで、集計して、Excelファイルの「OUTPUT」シートに出力します。
実際に操作したい方はこちら→サンプルデータ
コード全体
Dim adCon As New ADODB.Connection
Dim adrs As New ADODB.Recordset
Sub メイン()
Dim Filepath As String
Dim Fldname As String
Dim Filename As String
With Application.FileDialog(msoFileDialogFilePicker)
If .Show = True Then
Filepath = .SelectedItems(1)'ファイル(ファイルパス)を選択
Fldname = Left(Filepath, Len(Filepath) - Len(Dir(Filepath)))'ファイルパスからフォルダ名を変数に格納
Filename = Dir(Filepath)'ファイルパスからフィル名だけを変数に格納
Else
Exit Sub
End If
End With
Call ADO接続(Filename, Fldname)
End Sub
Sub ADO接続(ByVal Filename As String, ByVal Fldname As String)
Dim Excelversion As String
Dim sqlStr As String
Dim i As Integer
'フォルダへの接続設定
adCon.provider = "Microsoft.ACE.OLEDB.12.0"
adCon.Properties("Extended Properties") = "Text;HDR=Tes"
adCon.Open Fldname
'CSV用SQL文の記載
sqlStr = "SELECT 商品,単価*個数 AS 合計 FROM " & Filename
adrs.CursorLocation = adUseClient
adrs.Open sqlStr, adCon
Sheets("OUTPUT").Select
Sheets("OUTPUT").Cells.Clear
'ヘッダーをシートに表示
For i = 0 To adrs.Fields.Count - 1
Sheets("OUTPUT").Cells(1, i + 1) = adrs.Fields(i).Name
Next i
'Recordsetオブジェクトで読み込んだデータを表示
Sheets("OUTPUT").Range("A2").CopyFromRecordset adrs
MsgBox "取込完了", vbOKOnly
adCon.Close
Set adrs = Nothing
Set adCon = Nothing
End Sub
コードを解説していきます。
ADO（ActiveX Data Objects）
Dim adCon As New ADODB.Connection
Dim adrs As New ADODB.Recordset
ADOはデータベースに接続するためのライブラリです。
ADOを使うことでCSVだけではなく、データベースサーバー、Access、Excelにアクセスすることができます。
大まかには以下の２点です。
- ADODB.Connectionでデータベースに接続します。
- ADODB.Recordsetでデータベースの中のテーブル（表）に接続します。
例えば、ADODB.Connectionでデータベースサーバー自体に接続して、ADODB.Recordsetでサーバー内の特定テーブル（表）に接続するという形です。
しかし、今回はサーバーには接続しません。あくまでローカルディスク内のＣＳＶファイルです。
そのような場合には、以下のようになります。
- ADODB.ConnectionでＣＳＶファイルが入っているフォルダに接続します。
- ADODB.Recordsetでフォルダ内のＣＳＶファイルに接続します。
参照設定
ADOを使うためには、事前に参照設定が必要です。
VBEにて「ツール」→「参照設定」をチェック
「Microsoft ActiveX Data Object 2.8Library」にチェックを入れて「OK」をクリック
参照設定を行ったうえで、以下のコードでオブジェクトを生成します。
Dim adCon As New ADODB.Connection
Dim adrs As New ADODB.Recordset
ファイル選択
Dim Filepath As String
Dim Fldname As String
Dim Filename As String
With Application.FileDialog(msoFileDialogFilePicker)
If .Show = True Then
Filepath = .SelectedItems(1)
Fldname = Left(Filepath, Len(Filepath) - Len(Dir(Filepath)))
Filename = Dir(Filepath)
Else
Exit Sub
End If
End With
こちらでは、読み込むためのCSVファイルをファイルダイアログオブジェクトを使って選択します。
先述の通り、ADODB.Connectionでまずはフォルダに接続します。つまりフォルダパスが必要になります。選択したファイルパスからフォルダパスのみを変数Fldnameに格納します。そのためのコードが以下になります。
Fldname = Left(Filepath, Len(Filepath) - Len(Dir(Filepath)))
そして、ADODB.Recordsetでファイルに接続します。ADODB.Recordsetでファイルに接続する場合はファイルパスではなくファイル名だけが必要になります。よって、ファイルパスではなくファイル名だけを変数Filenameに格納します。そのためのコードが以下になります。
Filename = Dir(Filepath)
※Ｄir関数を使うことで、ファイル名を返してくれます。下図の通り
フォルダ接続（ADODB.Connection）
Call ADO接続(Filename, Fldname)
Callステートメントで、ADO接続プロシージャにファイル名（変数：Filename）とフォルダパス（変数：Fldname）を渡します。
adCon.provider = "Microsoft.ACE.OLEDB.12.0"
adCon.Properties("Extended Properties") = "Text;HDR=Yes"
adCon.Open Fldname
ADODB.Connectionのプロパティ、メソッドを使って上記３行で対象フォルダの接続を確立します。
providerプロパティ
.provider=でOfficeのバージョンに合わせて次のように指定します。
| Microsoft.ACE.OLEDB.4.0 | Office2003以前 |
| Microsoft.ACE.OLEDB.12.0 | Office2007以降 |
Propertiesコレクション
Propertiesコレクションにて.Properties(“Extended Properties”)で拡張プロパティの指定を行います。
CSVファイルの場合：Text
先頭行をヘッダーとして扱う場合：HDR=Yes
Openメソッド
Openメソッドでフォルダパスを指定して接続と実行を行います。
SQL
sqlStr = "SELECT 商品,単価*個数 AS 合計 FROM " & Filename
変数sqlStrにSQLコードを格納します。（商品名カラムと合計カラム（単価×個数）の集計表）
※SQLコードの解説は割愛
データの読み込み（ADODB.Recordset）
ここまででADODB.Connectionで対象フォルダに接続しました。
今度はADODB.Recordsetでデータベースを読み込みます。
adrs.CursorLocation = adUseClient
adrs.Open sqlStr, adCon
adrsのCursorLocationプロパティをadUseClientに設定します。
adUseClient：クライアント側カーソル
adrs.Open sqlStr, adConでSQL（データベース）を読み込みます。
Fieldsコレクション
For i = 0 To adrs.Fields.Count - 1
Sheets("OUTPUT").Cells(1, i + 1) = adrs.Fields(i).Name
Next i
Recordsetオブジェクト（変数：adrs）のFieldsコレクションでヘッダーの情報を取得できます。
ForNextステートメントと配列を使ってOUTPUTシートに表示させます。
Sheets("OUTPUT").Range("A2").CopyFromRecordset adrs
CopyFromRecordsetメソッド
CopyFromRecordsetメソッドを使って、Recordsetオブジェクトで読み込んだデータを表示します。
Closeメソッド
adCon.Close
Set adrs = Nothing
Set adCon = Nothing
Connectionオブジェクトを閉じます。
Recordsetオブジェクトも閉じられます。そして、オブジェクト変数の値を Nothing に設定して開放します。
【参考書籍】
CSVファイル読み込みで学ぶExcel VBA ADO入門 技術の泉シリーズ
筆者オススメのVBAテキストはこちら↓
まとめ
- ADODB.Connectionでフォルダに接続（フォルダパスを指定）
- ADODB.Recordsetでデータベースを読込（ファイル名を指定、SQL活用）
- ADOを使う場合は参照設定が必要
ADOとSQLを活用したデータベースの読込の基本的な方法について解説しました。
次回は応用編としてフォルダ内の複数ファイルの結合について紹介します。
コメント