TIL

Today I Learned. 知ったこと、学んだことを書いていく

ADOを使って、CSVファイルを読み込む - VBA

VBA

ADOを使い、CSVファイルにアクセスすることで、CSVファイルをDBのテーブルとして扱うことができるようになる。
ADOでの接続は以下の手順で行う

  1. プロパイダを指定する
  2. アクセスするファイルの格納フォルダの指定
  3. そのほかの設定をする
  4. 接続開始
  5. SQLの実行
  6. ヘッダーの表示
  7. 取得データの表示

サンプルコード

Sub OpenDataBase()
On Error GoTo PROC_ERR
    Dim cn          As New ADODB.Connection
    Dim Rs          As New ADODB.Recordset
    Dim sEXTENDED   As String
    Dim sSrcDir     As String   ' 接続先フォルダ
    Dim sSql        As String   ' SQL
    Dim oWs         As Worksheet
    Dim lCnt        As Long     ' ヘッダー表示のループ変数
    
    sSrcDir = "C:\Temp\ADO_TEST\"
    
    ' プロパイダの設定 
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"    ' Office 2007 以降

    ' 読み込むファイルの格納フォルダのパス
    cn.Properties("Data Source") = sSrcDir
    
    ' その他のプロパティの設定
    sEXTENDED = "text"
    sEXTENDED = sEXTENDED & ";FMT=Delimited"
    sEXTENDED = sEXTENDED & ";HDR=Yes"
    cn.Properties("Extended Properties").Value = sEXTENDED
    
    ' 接続開始
    cn.Open
    
    sSql = "SELECT * FROM [CSVTEST.csv]"
    
    ' SQL実行
    Rs.Open sSql, cn
    
    If Rs.EOF Then
        ' 結果が1行もない場合終わり
        GoTo PROC_EXIT
    End If
    
    Set oWs = ThisWorkbook.Sheets("Sheet1")
    
    ' ヘッダーの表示
    For lCnt = 1 To Rs.Fields.Count
        oWs.Cells(1, lCnt).Value = "'" & Rs.Fields(lCnt - 1).Name
    Next
    
    ' 結果をそのまま表示
    oWs.Cells(2, 1).CopyFromRecordset Rs
    
    Rs.Close
    
    cn.Close
    
PROC_EXIT:
    On Error Resume Next
    
    ' 後処理
    Set Rs = Nothing
    Set cn = Nothing
    
    Exit Sub
PROC_ERR:
    MsgBox "ADO接続(CSV/TEXT)エラー：" & Err.Description & "(" & Err.Number & ")" & vbCrLf & sSrcDir, vbCritical
    GoTo PROC_EXIT
End Sub

解説

プロパイダの設定

cn.Provider = "Microsoft.ACE.OLEDB.12.0"

プロパイダの設定をしている。これはExcelのバージョンによって、異なったものを設定しないといけない
今回のソースはOffice 2007以降の場合の設定となっている

Office 2007より前の場合、"Microsoft.Jet.OLEDB.4.0"を指定すること

読み込むファイルの格納フォルダの指定

cn.Properties("Data Source") = sSrcDir

"Data Source"に読み込みたいCSVファイルが格納されているフォルダのパスを指定する。
読み込むCSVはSQLに記述するため、フォルダを指定するようになっている。

その他のプロパティの設定

sEXTENDED = "text"
sEXTENDED = sEXTENDED & ";FMT=Delimited"
sEXTENDED = sEXTENDED & ";HDR=Yes"

cn.Properties("Extended Properties").Value = sEXTENDED

複数指定する場合には";"で区切る

"text"ではテキストファイルであることを示している。

"FMT=Delimited"はファイルの形式は「区切りですよー」ということを示している。CSVはDelimitedを指定することになっている。

"HDR=Yes"はCSVファイルの1行目をヘッダーとして扱うことを示している。HDR=Noとすると、1行目もデータとして扱うようになる。

"Extended Properties"に設定することによって、その接続の設定ができる。.Valueとなっているのが少し気になった...

接続開始

cn.Open

Connection.Openで接続を開始できる。

SQLの書き方

sSql = "SELECT * FROM [CSVTEST.csv]"

[CSVTEST.csv]のようにファイルをテーブルとして扱うことができる。結合とかもできるのかな！？

拡張子を省略すると.txtとして読み込もうとする

ためしに、[CSVTEST]としてみたら以下のようなエラーになった。

ADO接続(CSV/TEXT)エラー：オブジェクト 'CSVTEST.txt' が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください。'CSVTEST.txt' がローカル オブジェクトでない場合は、ネットワークの接続を確認するか、サーバー管理者に問い合わせてください。(-2147217865)

オブジェクト 'CSVTEST.txt' が見つかりませんでした。だって！
拡張子を省略すると.txtを付けてくれるんですね...はい。これだけです。

SQLの実行

Rs.Open sSql, cn

実行というより、RecordSetの作成を行っている(カーソルを開く)という意味になるらしい。

以下、引数

recordset.Open Source, ActiveConnection, CursorType, LockType, Options
  • Source: 実行するSQL
  • ActiceConnection: 接続されている接続を指定
  • (CursotType): RecordSetを開くときに使うカーソルの種類を指定。CursorTypeEnumの値を指定可能
  • (LockType): RecordSetを開くときのロックの種類を指定。LockTypeEnumの値を指定可能
  • (Options): ほかの設定をする

読み込むだけなら、SourceとActiveConnectionの指定だけでいいっぽい？

SQL結果の確認

If Rs.EOF Then

RecordSet.EOFで結果の確認を行っている。これは次の行があればFalse、なければTrueを返してくれるため、結果の確認として使える。

また、次々に読み込んでいくときにも使えるのかな。

ヘッダーの表示

For lCnt = 1 To Rs.Fields.Count
    oWs.Cells(1, lCnt).Value = "'" & Rs.Fields(lCnt - 1).Name
Next

RecordSet.Fieldsで読み込んだデータのフィールドの情報が取得できる

フィールド数の取得はRecordSet.Fields.Count

フィールド名の取得はRecordSet.Fields(index).Name

上の2つを組み合わせることで、全フィールド名を取得することができる。

実行結果を一度に表示

oWs.Cells(2, 1).CopyFromRecordset Rs

CopyFromRecordsetを使うことによって、簡単に取得データを出力することができる！！！
また、一つずつ値を取得するときにはRecordSet(index).Valueで取得できる。

実行結果を一つずつ表示

' ヘッダーの表示
For lCnt = 1 To Rs.Fields.Count
    oWs.Cells(1, lCnt).Value = "'" & Rs.Fields(lCnt - 1).Name
Next

' 取得データの表示
Dim i, j As Long
i = 2
Do Until Rs.EOF
    For j = 1 To Rs.Fields.Count
        oWs.Cells(i, j) = Rs(j - 1).Value
    Next
    Rs.MoveNext
    i = i + 1
Loop

ポイントは次の3つ

RecordSet.EOFで次の行がなくなるまでループ
RecordSet(index).Valueで値の取得
RecordSet.MoveNextで次の行へカーソルを移動


プロパイダとは？

Microfost.ACE.OLEDB.12.0やMicrosoft.Jet.OLEDB.4.0はプロパイダっていうらしい

これはVBAでDBにアクセスするための規約みたいな？よくわからん

OLE DB（Object Linking and Embedding DataBase）プロバイダーとは、AccessやSQL Serverなどのデータべ―スにアクセスする機能を提供するAPIだ。

http://www.atmarkit.co.jp/ait/articles/1511/26/news015.html

VBAでDBに接続するためのAPIの種類を指定しているのか！！

DBの接続モード

DBの説z区モードを設定することもできる。

既定では、Jet OLE DB プロバイダを使用した DAO および ADO では、共有やアップデートが可能なアクセス状態でデータベースが開かれます

https://msdn.microsoft.com/ja-jp/library/cc376618.aspx

Modeで接続モードを設定することで、読み取り専用などにできる。

参考文献