■ データオブジェクトを使用してCSVデータを取得しよう
ここではActiveXデータオブジェクト(ADO)とMicrosoftJetOLE DB4.0プロバイダを使用した、CSVファイルからのデータ取得について解説します。
FileSystemObjectの操作 や VBAステートメントでファイル操作 にて、テキストファイルの内容を読み取る方法を解説しました。
しかし、ただテキストファイルの内容を読み取り、ワークシート上に展開したいだけなら、今回解説するADOオブジェクトを使用した、データ取得のほうがより簡単に行えます。
それでは早速、ADOを使用したCSVファイルの読み取りについて解説していきましょう。
なおADOを使用するには、VBEの参照設定（ツール＞参照設定）で「Microsoft ActiveX Data ObjectX.X Library」にチェックを入れる必要があります。
■MicrosoftJetOLE DB4.0プロバイダを使用したCSVファイルへの接続
Sub test()
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Set CN = New ADODB.Connection
CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\;" & _
"Extended Properties='Text;HDR=NO'"
Set RS = CN.Execute("SELECT * FROM sample.csv")
Do Until RS.EOF
Debug.Print RS.Fields(0); RS.Fields(1); RS.Fields(2)
RS.MoveNext
Loop
Set RS = Nothing
Set CN = Nothing
End Sub
|
この例では、Cドライブのルートフォルダにある、"sample.csv"ファイルに接続、レコードセットを取得してイミディエイトウィンドウに、フィールド1〜3を出力します。（CSVファイルのフィールド数が3以上あるものとします） |
"Extended Properties='Text;HDR=NO'" HDRをNOに指定したとき、CSVファイルの1行目をデータとして認識します。HDRをYESに指定したとき、CSVファイルの1行目はフィールド名として認識します。
"Data Source=C:\;" はCSVファイルが存在するフォルダ名を指定します。この場合、Cドライブのルートフォルダになります。"Data Source=C:\;" は"Data Source=C:;" と記述しても同様に動作します。
"SELECT * FROM sample.csv" このSQLで、"sample.csv" のすべてのレコードを選択します。"sample.csv"は"sample#csv" と記述しても同様に動作します。
このサンプルコードは、次のように書き換えても同様に動作します。
Sub test()
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Set CN = New ADODB.Connection
CN.Provider = "Microsoft.Jet.OLEDB.4.0"
CN.Properties("Extended Properties") = "Text;HDR=NO"
CN.ConnectionString = "C:\"
CN.Open
Set RS = CN.Execute("SELECT * FROM sample.csv")
Do Until RS.EOF
Debug.Print RS.Fields(0); RS.Fields(1); RS.Fields(2)
RS.MoveNext
Loop
Set RS = Nothing
Set CN = Nothing
End Sub
"Text;HDR=NO" を"Text;HDR=YES" にして、"SELECT * FROM sample.csv" を"SELECT * FROM sample.csv WHERE [フィールド名] = フィールドの値" にすると、フィールド名を使った抽出ができます。この場合、CSVファイルの1行目がフィールド名となります。
"Text;HDR=NO" の場合、"SELECT * FROM sample.csv WHERE [sample#csv].F1 = フィールドの値" このように抽出するフィールドを指定します。この例では、sample.csvの1列目をWHERE句の抽出条件として指定しています。
取得したレコードセットを、ワークシート上に展開するには、RangeオブジェクトのCopyFromRecordsetメソッドを利用します。
Set RS = CN.Execute("SELECT * FROM sample.csv")
Range("A1").CopyFromRecordset RS
この場合、アクティブシートの"A1"セルより、レコードセットオブジェクト変数「RS」に取得されているレコードセットの内容を転記・複写します。
もちろん、繰り返し構文を利用してフィールドの値を1つづつセルに複写しても構いません。
■MSDASQL（ODBC）プロバイダを使用したCSVファイルへの接続
ここまで、Jet（OLEDB）プロバイダによるCSVファイルへの接続を解説してきましたが、同様の処理は、MSDASQL（ODBC）プロバイダを利用しても行うことができます。
Sub test()
Dim CN As String
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
CN = "Provider=MSDASQL;" & _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DefaultDir=C:\;FirstRowHasNames=0;"
RS.Open "SELECT * FROM sample.csv;", CN
Do Until RS.EOF
Debug.Print RS.Fields(0); RS.Fields(1); RS.Fields(2)
RS.MoveNext
Loop
Set RS = Nothing
End Sub
|
この例では、今までと同様に、Cドライブのルートフォルダにある、"sample.csv"ファイルに接続、レコードセットを取得してイミディエイトウィンドウに、フィールド1〜3を出力します。 |
コネクションオブジェクト変数「CN」がただの文字型変数になっていることに注意してください。
HDR プロパティはMSDASQLプロバイダでは使用できません。代わりにFirstRowHasNamesを使用します。ただし、このFirstRowHasNamesプロパティはODBCドライバに渡されることはありません。これは、ODBCドライバのバグです。
[BUG] Excel ODBC ドライバ Disregards、FirstRowHasNames またはヘッダーの設定
http://support.microsoft.com/kb/288343/ja
リンク先では、Excel ODBCドライバについて記述されていますが、Text ODBCドライバでも同じ現象が発生します。
つまり、MSDASQLプロバイダを使用した接続では、必ずCSVファイルの1行目は列見出し（フィールド名）として扱われます。注意してください。
以上、ADOオブジェクトを利用したCSVファイルへの接続を解説してきました。
CSVファイルをレコードセットとして取得した場合、読み取り専用で開かれるためレコードの更新・追加・削除は行えません。
しかしCSVファイルを一気にワークシート上に展開したいとき、RangeオブジェクトのCopyFromRecordset メソッドが利用できるなど、非常に容易に行える手法で、応用範囲も広いです。覚えておいても、決して損ではないでしょう。
|
