rubyによるADO, ADOXを用いたAccess, Excelファイルの処理

最終更新日: 2009/11/03

　MS-Windowsに用意されている ADO(注1)およびADOX(注2)の仕組を利用すれば、データベースソフトのAccessのファイルを作成・参照・更新できます。Excelのファイルも同じ仕組によって処理できます。Access, Excelがインストールされていないパソコンでも大丈夫です。

　そうしたADOによる処理をrubyで簡単に実現するために作ったのが yado.rb です。総てrubyスクリプトで書かれています。

　Windows上で ruby.exe を実行できる環境があれば、yado.rbを用いて、AccessやExcelのファイルを操作するrubyスクリプトを簡単に作成できます。

ver 1.0→1.5のバージョンアップ内容
　　Access2007ファイル *.accdb への対応、sql命令の実行結果を保持する Resultクラスの新設、xml, adtgファイルの読み書きなどが主なバージョンアップ内容です。

ダウンロード：　ZIPアーカイブ　yado150.zip

　また、Access, ExcelのファイルをSQL命令で操作したい時に、同梱の yadosql.exe を利用できます。このコマンドは、Accessやrubyがない環境でも動作します。

　これをWindowsのコマンドプロンプトで起動すると、標準入力から入力されたSQL命令が実行され、その結果が標準出力・標準エラー出力に出力されます。

　yadosql.exeの解説ドキュメントは yadosql.exeの使い方 に掲載しています。

なお、yado.rbのver 1.5へのバージョンアップに伴い、次の２つのコマンドが追加されています。

• load input.xml table_name: xml|adtgを読み込んでテーブルを作成 [\l]
• xtee output.xml: SQLの実行結果をoutput.xmlに出力する [\x]

• 注1　ADO: ActiveX Data Objects
  ADOの仕組みを使えば、SQL(Structured Query Language: 構造化照会言語)の命令文を実行できるほか、SQL以外でのデータベース処理も行えます。
• 注2　ADOX: ActiveX Data Objects Extensions for DDL and Security
  ADOXは、ADOに不足しているデータ定義(DDL: Data Definition Language)などの機能を提供します。

参考情報：　MS-Accessの Form, Report, Query, Macro, Module をテキストファイルとして書き出すrubyスクリプトを プログラミング雑記帳 に載せておきました。テキストファイルから読み込む方法も容易に類推できます。つまり export, import とも行えるわけです。yado.rbと違ってAccessがインストールされていないと動かせませんが、それなりに便利です。

（１）yado.rb作成に際して重視したこと

　Accessを使わずに、なるべく簡単なrubyスクリプトで、そのファイルを作成・参照・更新できるようにすることを目標にしました。

　なにより「簡単なrubyスクリプトで」が第一で、例えば、下に掲げる実質４行のスクリプトで、Access付属のNorthwind.mdb(注)の標準テーブルの中身を総てExcelファイルに書き出すことができます。（Excelには慣れているけど Accessにはちょっと、というユーザーにはそれなりに使えるのでは？）

#! ruby.exe -KS
require 'yado'
db = Yado.new  'Northwind.mdb'
db.print_all_values  'OUTPUT.XLS'  # OUTPUT.CSVだとCSVファイルとして書き出す
db.close

　もちろん、queryまたはquery_resultメソッドによってSQL命令の実行も可能です。

db.query_result("SELECT * FROM 受注明細;").pav("output.xls")

(注)　Northwind.mdbは、下の愛媛大学のサイトからダウンロードできます。

「コンピュータ演習 Excel2000 & Access2000」

（２）開発環境

　Windows XP, ADO ver 2.7, MSWin版 ruby ver 1.8.7

　CPU：インテル(x86)・2800MHz, メモリー：190MB

　2002年に購入した今となっては旧いパソコンですが、ADOは最初からインストールされていました。

（３）動作環境

　ZIPアーカイブ同梱のサンプルスクリプトを試したところ、上の開発環境のほか、Windows VISTA, ADO ver 6.0 (Access2007はインストールされていない。)でも動きました。

　ruby.exeについては、Windows上で動くものであって win32ole.so が用意されていれば、その種類は問わないと思います。バージョンは、1.8.x であれば問題ないはずです。1.9.xでは動作確認していません。

（４）yado.rbが処理対象とするファイル

• Accessファイル：　拡張子が .mdb のファイル。yado.rbが主な対象としているのはこのmdbファイルです。
  Office2007がインストールされているパソコンでは、Access2007のファイル *.accdb も扱えます。Access2007はなくてもかまいません。
• Excelファイル：　拡張子が .xls のファイル。mdbファイルに比べると、使えない機能や独特の仕様があります。

　ADOの仕組を通して、Lotus、Paradox、あるいはmysqlやpostgresqlなどのデータベースファイルを扱えるようですが、yado.rbではそこまで拡張していません。

　ZIPアーカイブに含まれているドキュメントでは、ADOやSQLに関して私なりに調べた情報を交じえながら、yado.rbの解説を記しています。

　mdbファイルを基にして説明していますが、xlsファイルの扱いについても最後の方で触れています。

　ドキュメントをWeb上で閲覧したい方はyado.rbの使い方（１） からどうぞ。「part 1」と「part 2」の２つに分けて掲載しています。

　ドキュメントの目次は下のとおりです。

1. はじめに
   1. yado.rb作成に際して重視したこと
   2. 開発環境
   3. 動作環境
   4. yado.rbが処理対象とするファイル
2. 簡単なデータベースの作成、テーブルの作成、レコードの挿入と参照
   1. yado.rbの置き場所
   2. クラスオブジェクトの作成
   3. SQL命令の実行
   4. データベースを閉じる
   • ［補足１］　sample01.rb の実行の仕方
   • ［補足２］　SQLの insert によるレコードの挿入とデータ型
3. レコードの挿入(データベースへのデータの書き込み)
   1. レコード挿入用メソッド insert_records と addnew
      • ［補足］　レコード挿入時のデータ型
   2. CSVまたはタブ区切りテキストを読み込んでレコード挿入
   3. レコード挿入時のバイナリ型データの扱い方、および別ファイルの取り込み
      • ［補足１］　バイナリデータの大きさの制限
      • ［補足２］　バイナリデータの参照(読み込み)の技述情報
      • ［補足３］　生のバイナリデータとOLEオブジェクトの違い
   4. レコード挿入時のID番号等の自動的生成：オートインクリメント
   • ［補足］　データ型の yesno
4. レコードの更新および削除
   1. レコードの更新: SQL命令による方法
   2. レコードの更新: Yadoのupdate_records, update_pairメソッドによる方法
   3. レコードの削除: SQL命令による方法
   4. レコードの削除: Yadoのdelete_recordsメソッドによる方法
   5. その他のレコード更新用メソッド: update_array, update_hash
5. データベース参照用のYadoのメソッド
   1. テーブル＆フィールド情報を書き出す print_field_info
      ［補足］　mdbファイルにおけるテーブルの種類
   2. とりあえず総てのデータを出力する print_all_values
   3. 指定テーブルのバイナリ型データをファイルに書き出す print_binary_values
   4. 各レコードを参照する each
6. SQL命令によるデータベース参照の例
   1. 特定のフィールドによる整列 order by
   2. 検索条件を指定する where
   3. 仮装テーブル VIEW の利用
   4. テーブルの結合
7. テーブルの作成
   1. データ型
   2. データ型指定時のオプション
   3. SQL命令においてテーブル名やフィールド名に使えない文字
   4. ADOXによるテーブルの作成(SQL命令に依らない方法)
   • ［補足］　Propertiesメソッドのパラメータと値
8. Excelファイルを扱う時の注意点およびサンプル
   1. xlsファイルを扱う時の制限事項
   2. xlsファイルに関する独特の仕様
   3. xlsファイルを操作するサンプル
9. Yadoの各種メソッドに関する補足
   1. クラスオブジェクトの生成
   2. データベースの作成メソッド
   3. データベースへの接続と切断
   4. データベースの削除
   5. テーブルの削除
   6. エラーメッセージに関するメソッド
   7. ADOのバージョン
10. yado.rbの内部情報
    1. yado.rbに含まれているクラスとモジュール
    2. テーブル、フィールド、インデックスの内部記録
11. ADOに関する補足
    1. レコードオープン時のパラメータ
    2. インデックスを参照する形のレコード操作
    3. インデックスにおける空欄の取扱い指定
    4. 接続文字列
12. 参考Webサイト

当Webに関連することでアドバイスや問合せなど何かありましたら、以下までメールをお願いします。
t-yoshi#izumi@msb.biglobe.ne.jp
(SPAM対策のため、８文字目に # を入れて記載しています。お手数ですが、上記アドレスから # を除いて送信してください。)

トップページへ