Excel

ADOを使って先頭行がヘッダー(フィールド名)になっていないCSVファイルを開く際のExcel、AccessのVBAサンプルプログラムを覚書。

ヘッダー(フィールド名)がない場合は、接続文字列に指定できる"Extended Properties"の値を設定することで対処できる。


ADOからCSVファイルに直接アクセスしてデータを操作するプログラムは、今までいくつか作ってきたので今更覚書するのもと思っていたが、今回久しぶりに日本郵便が配布している郵便番号データファイル(CSV形式)を使ったプログラムを作ったときに、郵便番号データファイルの1行名がフィールド名になってなくて、いきなり1行目からデータが始まっていて少し困ってしまった。

解決するまで少し時間がかかったのでしっかり覚書しておく。

今回のサンプルプログラムは、ExcelのVBAを使ったサンプルプログラムだけど、オブジェクト宣言とかを変えれば、Accessにも流用できる。

AccessのVBAの場合のサンプルプログラムも載せておく。

以下ExcelとAccessのVBAのサンプルプログラムとその説明。
スポンサードリンク


CSVファイルとの接続文字列について

まずADOを使ってCSVファイルをデータベースのテーブルのように扱う際の基本的な接続文字列の書式は以下のとおり。

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=テキストファイルのパス(ファイル名は含まない);Extended Properties="Text;HDR=Yes;FMT=Delimited

気をつけてほしいのは、"Source"に指定するパスは、目的のCSVファイルが保存されているフォルダまでのパスで、CSVファイル名は含まないということ。

じゃあ、開きたいCSVファイルはどこで指定するのかというと、実際にレコードセットを開く際のSQL文(SELECT文)の中のFROM部分に指定する。

つまり"テーブル名 = CSVファイル名"という扱いになるということ。

そしてもうひとつのポイントである、"Extended Properties"指定について説明しておく。

"Extended Properties"に指定できるプロパティ値には、以下の3つがあって、";"(セミコロン)で区切って指定する。

1番目のプロパティ値は、データベースの種類で、テキストファイルの場合には、"Text"を指定する。

2番目のプロパティ値は、目的のCSVファイルの最初の行をヘッダー(フィールド名)として扱うかどうかの指定で、最初の行がフィールド名の場合には、"HDR=YES"を指定して、1行目からデータが始まる場合は、"HDR=NO"を指定する。

"HDR=NO"を指定した場合、フィールド名は、F1、F2....というように「"F"+列番号」がフィールド名として自動的に付けられる。

自動的に付けられたフィールド名をSQL文の"Where"に指定して、条件を絞ってレコードセットを取得することも可能。

3番目のプロパティ値は、ファイルのフォマート指定を表している。

CSVファイルの場合は、"FMT=Delimited"を指定する。

"Extended Properties"は、CSVファイルだけでなく、Excelファイルなどを開く際にも使用することができる。

Excel VBAのサンプルプログラム1

"C:\ZIP"フォルダにある"KEN_ALL.CSV"ファイルを開いて先頭行をイミディエイトウィンドウに表示するExcelのVBAで作成したサンプルプログラム。
(イミディエイト ウィンドウは、[表示] - [イミディエイト ウィンドウ]で表示できる)

ちなみにプログラム中で使用しているサンプルファイル"KEN_ALL.CSV"は、日本郵便のHPからダウンロード(無料)できる全国の郵便番号データのCSVファイル。

このCSVファイルは、フィールド名が定義されておらず、1行目からデータが始まっている。
Sub Sample01forExcel()
Dim con As Object, rec As Object

    Set con = CreateObject("ADODB.Connection")
        With con
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\zip;" _
                                & "Extended Properties='text;HDR=No;FMT=Delimited'"
            .Open
        End With
    
    Set rec = CreateObject("ADODB.Recordset")
        rec.Open "select * from KEN_ALL.CSV", con
        Debug.Print rec(0) '最初のレコードの1列目の値を表示

End Sub

Access VBAのサンプルプログラム1

以下は、AccessのVBAで上記サンプルプログラムを記述した例。
Sub Sample01forAccess()
Dim con As New ADODB.Connection, rec As New ADODB.Recordset

        With con
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\zip;" _
                                & "Extended Properties='text;HDR=No;FMT=Delimited'"
            .Open
        End With
    
        rec.Open "select * from KEN_ALL.CSV", con
        Debug.Print rec(0) '最初のレコードの1列目の値を表示

End Sub
"Extended Properties"は、接続文字列プロパティにふくめずに、Connectionオブジェクトの"Properties"で別途指定することもできる。

例えばこんな感じ(AccessのVBA)。
Sub Sample02forAccess()
Dim con As New ADODB.Connection, rec As New ADODB.Recordset

        With con
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\zip;"
            .Properties("Extended Properties").Value = "text;HDR=No;FMT=Delimited"
            .Open
        End With
    
        rec.Open "select * from KEN_ALL.CSV", con
        Debug.Print rec(0) '最初のレコードの1列目の値を表示

End Sub

Excel VBAのサンプルプログラム2

以下は、都道府県が"東京都"のデータだけ取得する"Where条件"を指定した場合のサンプルプログラム(ExcelのVBA)
Sub Sample02forExcel()
Dim con As Object, rec As Object

    Set con = CreateObject("ADODB.Connection")
        With con
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\zip;"
            .Properties("Extended Properties").Value = "text;HDR=No;FMT=Delimited"
            .Open
        End With
    
    Set rec = CreateObject("ADODB.Recordset")
        rec.Open "select * from KEN_ALL.CSV Where F7 = '東京都'", con
        Debug.Print rec(6) '最初のレコードの7列目(都道府県)の値を表示

End Sub

Excel VBAのサンプルプログラム応用編

サンプルプログラムを応用して、インプットボックスに入力された郵便番号から該当する住所をメッセージボックスで表示するサンプルプログラム(ExcelのVBA)

以下のサンプルプログラムでは、郵便番号データのCSVファイルをプログラムを実行するExcelのファイルと同じフォルダに保存されていると仮定して、接続文字列で指定する対象フォルダ"Source"に"ThisWorkbook.Path"を指定している。
Sub Sample03forExcel()
Dim con As Object, rec As Object, txtZipCode As String

    Set con = CreateObject("ADODB.Connection")
        With con
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & ";"
            .Properties("Extended Properties").Value = "text;HDR=No;FMT=Delimited"
            .Open
        End With
            
    txtZipCode = InputBox("郵便番号を入力してください。", "住所検索")
        '何も入力されなかったか、[キャンセル]がクリックされたときの処理
        If txtZipCode = "" Then
            MsgBox "処理を中止しました。", vbExclamation
            Exit Sub
        End If
        
    txtZipCode = Replace(txtZipCode, "-", "") '"-"が入力されいた場合に削除する処理
    
    Set rec = CreateObject("ADODB.Recordset")
        rec.Open "select * from KEN_ALL.CSV Where F3='" & txtZipCode & "'", con
            '入力された郵便番号に該当する住所が見つからなかった場合の処理
            If rec.EOF Then
                MsgBox "該当する住所が見つかりませんでした。", vbExclamation
                Exit Sub
            End If
        
        '住所をメッセージボックスで表示
        MsgBox "入力された郵便番号に該当する住所は" & vbCrLf _
                & rec(6) & rec(7) & rec(8) & " です。", vbInformation
                
End Sub

以上、終わり。

Excelのアドインで郵便番号から住所に変換するアドインが出てるけど、いまいち使いづらいのでもうちょっと使い勝手のいいもの(自分にとって)を自作してみようかなーなんて思ってる。

スポンサードリンク

  

関連記事