2017年7月12日水曜日

[VBA] Oracle テーブルのレコードを CSV 出力する

Oracle 表のデータを CSV ファイルに出力したい要望は多いが、まとめて出力するには良いツールが無い。SQL*Plus の spool で csv のカンマ区切り形式で出力するのも、1行のサイズを調整したり汎用的に使いにくい。

MSAccess から ODBC 経由で Oracle のテーブルにリンクして、このテーブルをCSVファイルに出力してやると、比較的楽に csv の様式で出力できる。

少量ならリンクテーブルでも良いが、今回はまとめて出力するために SQL の編集だけでテーブルを変更できるように、パススルークエリを使用する。
（※注意）ただしVBAの言語仕様では、パススルークエリから csv ファイルをエクスポートできないので、クエリでラップしてやる。

■ 準備するもの
  • パススルークエリ（Name:="PassThrough", SQLText:="SELECT * FROM DUAL")
    接続文字列を指定（ODBC;DSN=xxx;UID=xxx;PWD=xxx;DBQ=xxx）
    ※SQLはこの後で CSV の Export 毎に編集するので、仮でよい
  • クエリ(Name:="Wrapper", SQLText:="SELECT * FROM PassThrough")
    これはパススルークエリを SELECT するだけ
  • [CSV出力対象一覧]テーブル
    まとめて出力する対象を、表を作って登録しておく

■ VBA 実装例
' 参照設定[Microsoft Scripting Runtime]が必要です。
Dim Fso As New Scripting.FileSystemObject

Dim TabName As String
Dim QueryDef As DAO.QueryDef
Dim Recordset As DAO.Recordset
' [CSV出力対象一覧].[テーブル名] に出力対象を登録済み
Set Recordset = CurrentDb.TableDefs("CSV出力対象一覧").OpenRecordset

Do While Recordset.EOF = False
  TabName = Recordset("テーブル名").Value

  ' パススルークエリを変更（※必要ならば、ここでソート順を付ける）
  Set QueryDef = CurrentDb.QueryDefs("PassThrough")
  QueryDef.SQL = "SELECT * FROM " & TabName

  ' クエリ(Wrapper)から、CSV ファイルを出力
  DoCmd.TransferText _
    TransferType:=AcTextTransferType.acExportDelim, _
    TableName:="Wrapper", _
    FileName:=TabName & ".csv"

  Recordset.MoveNext
Loop
時刻: