ODBC

作成日：2005.05.03
　ここでは ODBCドライバをインストールし、Excelから MySQL, PostgreSQL にアクセスする方法を解説する。テストでは、実用性はほとんどないかもしれないが(最近の市町村合併の状況を知るには有益かも？)、規模だけは十分に大きい「郵便番号データ」を使ってデータを検索する。
  1. DBのインストール
    　MySQL, PostgreSQL のインストールについては以下を参考に。
    　・MySQLのインストール/動作確認手順
    　・PostgreSQLのインストール/動作確認手順

  2. 郵便番号データのDB化
    (注)郵便番号のDB化には興味のない人はこの項をスキップしてもよい。

    　以下の郵便番号ダウンロードサービスのページから「全国一括」のデータ(ファイル名: ken_all.lzh)をダウンロードする。
    http://www.post.japanpost.jp/zipcode/dl/kogaki.html

    このファイルは以下のようなコンマ区切りの CSV データであり、12万件以上あるので Excelに全部は入りきらない分量である。
    01101,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
01101,"064  ","0640941","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｱｻﾋｶﾞｵｶ","北海道","札幌市中央区","旭ケ丘",0,0,1,0,0,0
01101,"060  ","0600041","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｵｵﾄﾞｵﾘﾋｶﾞｼ","北海道","札幌市中央区","大通東",0,0,1,0,0,0
01101,"060  ","0600042","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｵｵﾄﾞｵﾘﾆｼ(1-19ﾁｮｳﾒ)","北海道","札幌市中央区","大通西（１〜１９丁目）",1,0,1,0,0,0
01101,"064  ","0640820","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｵｵﾄﾞｵﾘﾆｼ(20-28ﾁｮｳﾒ)","北海道","札幌市中央区","大通西（２０〜２８丁目）",1,0,1,0,0,0
    MySQL, PostgreSQLで読み込みが可能なように、Tab区切りの以下の形式に変換する。
    01101	060  	0600000	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ	北海道	札幌市中央区	以下に掲載がない場合	0	0	0	0	0	0
01101	064  	0640941	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｱｻﾋｶﾞｵｶ	北海道	札幌市中央区	旭ケ丘	0	0	1	0	0	0
01101	060  	0600041	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｵｵﾄﾞｵﾘﾋｶﾞｼ	北海道	札幌市中央区	大通東	0	0	1	0	0	0
01101	060  	0600042	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｵｵﾄﾞｵﾘﾆｼ(1-19ﾁｮｳﾒ)	北海道	札幌市中央区	大通西（１〜１９丁目）	1	0	1	0	0	0
01101	064  	0640820	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｵｵﾄﾞｵﾘﾆｼ(20-28ﾁｮｳﾒ)	北海道	札幌市中央区	大通西（２０〜２８丁目）	1	0	1	0	0	0
    　具体的には上のデータに対して「,」->「\t（Tab)」変換、「"」の削除を行う。筆者は EmEditorで編集したが、Perlスクリプトが書ければ容易に変換できる。自分には難しいという人のために変換したファイル(2005.4.28更新版)をここに置く。解凍して、C:\tempに置いたものとして以下説明する。

    PostgreSQLへのデータの登録は以下の手順で行う。
    ・コマンドプロンプトでカレントディレクトリの変更
C:\>cd \"Program Files"\PostgreSQL\8.0\bin
・DB(test)の作成(作成済の場合は実行不要)
C:\Program Files\PostgreSQL\8.0\bin>createdb -U postgres test
Password:postgresdbpw
・DB(test)の編集
C:\Program Files\PostgreSQL\8.0\bin>psql -U postgres -h localhost test
パスワード:postgresdbpw
・テーブルの作成
test=# create table 郵便番号 (団体コード varchar(5), 旧郵便番号 varchar(5), 郵便番号 varchar(7), 都道府県名カナ varchar(10),市区町村名カナ varchar(30), 町域名カナ varchar(80), 都道府県名 varchar(10), 市区町村名 varchar(10),町域名 varchar(40), 複番 varchar(2), 小字 varchar(2), 丁目 varchar(2), 複町 varchar(2),更新 varchar(2), 理由 varchar(2));
・テーブルにデータの追加(データは c:\temp\ken_all-pg.txtにあるものとする)
test=# \copy 郵便番号 from c:\temp\ken_all-pg.txt
・終了
test=# \q
    MySQLへのデータの登録は以下の手順で行う(MySQLは 4.0を使い、「データベース名、テーブル名、フィールド名に日本語を使うな」ということなのでここでは使用しない)。
    C:\mysql\bin>mysql -uUser -p****** test

mysql> create table zipcode (dantai varchar(5), old varchar(5), zipcode varchar(7), pref_kana varchar(10), city_kana varchar(30), town_kana varchar(80),
    -> pref varchar(10), city varchar(10), town varchar(40), multi1 char(1), aza char(1), chome char(1), multi2 char(1), henkou char(1), reason char(1));
Query OK, 0 rows affected (0.09 sec)
(一行で入りきらないので二行に分けた)

mysql> load data infile 'c:/temp/ken_all-pg.txt' into table zipcode
(ファイルは PostgreSQLで入力したものと同じであり、c:\temp\ken_all-pg.txtにあるものとする)

mysql> quit
  3. ODBCドライバのインストール

    (注)ここより下の説明はクライアント機で行うものなので間違えないように。ただし、ローカルホスト(127.0.0.1)へのアクセスではサーバ＝クライアントである。

    MySQL用 ODBCドライバの最新版(2005.2現在 MyODBC-3.51.11-1-win.exe)を以下からダウンロードする。
    http://www.softagency.co.jp/MySQL/Downloads/MyODBC3/
    実行してドライバをインストールする。

    PostgreSQL用 ODBCドライバの最新版(2005.8現在 psqlodbc-08_00_0102.zip)を以下からダウンロードする。
    http://www.postgresql.org/ftp/odbc/versions/msi/
    解凍して psqlodbc.msi のインストールを実行する。(アンインストールはコントロールパネルのプログラムの追加と削除で)

    コントロールパネル -> 管理ツール -> データソース(ODBC) -> ドライバ に以下があればインストールは正常終了している。バージョンを確認すること。
    　MySQL ODBC 3.51 Driver    3.51.11.00
　PostgreSQL                8.00.01.02
    　ただ、そのままでは、日本語が化ける上に、"applicable_roles" などの余計なテーブルも表示されてしまいます。そのため、いのっち父さんのホームページ
    http://www.geocities.jp/inocchichichi/psqlodbc/indexj.html
    にある psqlodbc.dll をダウンロード＆解凍し、C:\Windows\system32の下の psqlodbc.dll を置き換えます。これで問題なく動作するようになります。

    他に「PostgreSQL ODBC Driver 日本語版」のページ
    http://nami73.com/download/psqlodbc.htm
    からも psqlodbc.exe がダウンロード可能であり、これでも日本語は問題なく表示できますが、"applicable_roles" などの余計なテーブルが表示されるのでこちらはお勧めではありません。

  4. データソースの設定

    コントロールパネル -> 管理ツール -> データソース(ODBC) -> システムDSN -> 追加

    (注)クライアントPCのユーザが自分一人ならシステムDSNに設定するのがよい。複数ユーザが同じパソコンを使用し、アクセスするデータベースが異なるときなどは ユーザDSN、ファイルDSN を使ってもよい。違いは以下を参考に。
    　・ユーザDSN　 : 現在のユーザにのみ認識され、現在使用しているPCでのみ使用可能
    　・システムDSN : NT サービスを含むこのPC上のすべてのユーザが認識可能
    　・ファイルDSN : 同じドライバをインストールしているユーザと共有可能

    PostgreSQL
    データソース名: PostgreSQL
説明:
サーバ名:       127.0.0.1 あるいは他ホストの場合は 192.168.0.x
データベース名: test
ユーザ名:       postgres
パスワード:     postgresdbpw
    「保存」する。

    MySQL ODBC 3.51 Driver
    Data Source Name    MySQL
Description         
Server              127.0.0.1 あるいは他ホストの場合は 192.168.0.x
User                root
Password            
Database            test
「Connect Options」「Advanced」はとりあえず設定不要。
    「Test」ボタンをクリックして「Success; connection was made!」と表示されれば設定完。「OK」で保存。

  5. 接続のテスト

    　以下の手順で接続の確認が可能である。
    (1) 例えばデスクトップに新規作成で、odbc-test.udl というファイルを作成する。
    (2) 上で作成したファイルを開く。
    (3) 「プロバイダ」タブで「Microsoft OLE DB Provider for ODBC Drivers」を選び、次へ。
    (4) 「データソース名を使用する」の下から「MySQL/PostreSQL」を選び、ユーザ名/パスワードは空欄(設定しないとコントロールパネルのデータソースで設定したものが使われる)のまま「接続のテスト」ボタンを押す。
    (5) 「(接続の)テストに成功しました」と表示されればOK。エラーのときはエラーがなくなるまで設定を見直す。MySQL/PostgreSQLとも、サーバ機とクライアント機が異なるときは、まずサーバ機上で確認し、次に localhost 以外からの接続が許可される設定になっているかどうかを確認した後、クライアント機から確認すること。

    　上の(4)の「データソース名を使用する」では、コントロールパネルの「データソース」で設定した「データソース名」が表示され、選択できる。もし「データソース」で設定したもの以外を試したいのであれば、PostgreSQLであれば、「プロバイダ」で「PostgreSQL OLE DB Provider」を選び、「接続」で以下のように設定する。
    	データソース：PostgreSQLサーバのIPアドレス
	場所        ：DB名
	ユーザ名    ：postgres
	パスワード  ：postgresdbpw
    「接続のテスト」をクリックし、「(接続の)テストに成功しました」と表示されればOK。

    「プロバイダの初期化中にエラーが発生したため、接続のテストに失敗しました。エラーを特定できません」となるときは、エラーの原因を調べること。
    「プロバイダ」に MySQLを登録する方法は不明。

  6. Excelから DBへのアクセス

    　Excelを起動し、データ -> 外部データの取り込み -> 新しいデータベース クエリ

    「データソースの選択」の「データベース」に上で、PostgreSQLの「データソース名」欄と MySQLの「Data Source Name」欄に設定した内容が MySQL*，PostgreSQL* のように表示されている。

    　ここでは PostgreSQLを例にして「富士見」という文字列を含む町域名を検索してみる。MySQLでも全く同様の手順なので説明は省略する。

    1.「データベース」で PostgreSQL* を反転させ、OKする。
    2.「列の選択」で「郵便番号」の左の + をクリックし、「都道府県名カナ」＞　「市区町村名カナ」＞　 「町域名カナ」＞　「都道府県名」＞　「市区町村名」＞　「町域名」＞ を繰り返して右側の「クエリの列」欄に６個のフィールドを選択し、次へ。
    3.「データの抽出」で「町域名」を反転させ、
    　　「値を含む」「富士見」
    　と設定し、どんどん先に進めていくと以下のような結果が Excelに取り込まれる。
    都道府県名カナ  市区町村名カナ  町域名カナ  都道府県名  市区町村名     町域名
ﾎｯｶｲﾄﾞｳ         ｸｼﾛｼ            ﾌｼﾞﾐ        北海道      釧路市         富士見
ﾎｯｶｲﾄﾞｳ         ﾕｳﾊﾞﾘｼ          ｶｼﾏﾌｼﾞﾐﾁｮｳ  北海道      夕張市         鹿島富士見町
ﾎｯｶｲﾄﾞｳ         ﾜｯｶﾅｲｼ          ﾌｼﾞﾐ        北海道      稚内市         富士見
ﾎｯｶｲﾄﾞｳ         ｶﾔﾍﾞｸﾞﾝﾓﾘﾏﾁ     ﾌｼﾞﾐﾁｮｳ     北海道      茅部郡森町     富士見町
ﾎｯｶｲﾄﾞｳ         ﾔﾏｺｼｸﾞﾝﾔｸﾓﾁｮｳ   ﾌｼﾞﾐﾁｮｳ     北海道      山越郡八雲町   富士見町
(以下省略)
  7. 関連情報へのリンク
    　


作成:いと，mailto:gfh05223@nifty.com