VBA共通:ADOを使ってヘッダー(フィールド名)のないCSVファイルを開く際の接続文字列とサンプルプログラム(Excel、Access)

Excel

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

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

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

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

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

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

以下ExcelとAccessのVBAのサンプルプログラムとその説明。

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

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

気をつけてほしいのは、”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行目からデータが始まっている。

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

以下は、AccessのVBAで上記サンプルプログラムを記述した例。

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

例えばこんな感じ(AccessのVBA)。

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

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

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

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

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

以上、終わり。

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