任意のSQL文の実行結果をプログラムでCSVに出力する方法

記事

Mihoko Iijima · 2021年9月24日 3m read

#SQL #ヒントとコツ #Caché #Ensemble #HealthShare #InterSystems IRIS #InterSystems IRIS for Health

これは InterSystems FAQ サイトの記事です。

システムユーティリティクラスを利用したプログラムを作成することで出力できます。

【注意1】出力データの区切り文字がカンマではなくタブで出力されます点、ご注意ください。

【注意2】エクセルでタブ区切りのファイルを開くため、エクセルの開くメニューから出力したファイルを開いてください。

処理概要は以下の通りです。

1)　%SYSTEM.SQL クラス（※）の Execute() メソッドを使用します。

第1引数：SQL文を指定します。

第2引数：内部日付が格納されている場合 yyyy-mm-dd で出力されるように表示モードを切り替えます。（1：odbcモード）

詳細は下記ドキュメントページおよびクラスリファレンスをご参照ください。

ドキュメント - $SYSTEM.SQL.Execute()【IRIS】
クラスリファレンス - $SYSTEM.SQLクラス - Execute()メソッド【IRIS】

ドキュメント - $SYSTEM.SQL.Execute()
クラスリファレンス - $SYSTEM.SQLクラス - Execute()メソッド

※ %SYSTEMパッケージ以下クラスは $system 特殊変数を利用してメソッドを実行できます。

set rset=$system.SQL.Execute("ここにSQL文",1)

2)　実行結果は %SQL.StatementResult クラスのインスタンスで返ります。

%DisplayFormatted() メソッドを使用して検索結果のファイル出力を実行します。

// 第1引数：出力形式の指定
// 　0：XMLファイル出力
// 　1：HTMLファイル出力
//  99：タブ区切りのテキストファイル出力）
// 100：CSVファイル出力《注意：タブ区切りで生成されます》　【注意3】参照
// 第2引数：出力ファイル名の指定（ファイルの拡張子を付けずにフルパスで指定）
//    【注意】Caché／Ensembleサーバ上に出力されます
// 第3引数：実行結果の取得用変数（参照渡しのため、先頭にピリオド付与）
do rset.%DisplayFormatted(99,"c:\kit\test",.pMessages)

上記 1) 2) の処理をまとめてストアドプロシージャとして利用できるサンプルをご用意しています。

https://github.com/Intersystems-jp/FAQ-SQLtoCSVUtility

検索対象テーブルが存在するネームスペースにインポートしてご利用ください。

サンプル実行例は以下の通りです（ターミナルでの実行例）。

// 実行したいSQLを変数に設定
set sql="SELECT OrderID, ItemName, Quantity, OrderDate FROM Test.FruitsOrder"

// インポートしたクラスメソッドを実行します。1が返れば成功です
write ##class(ISJSQL.Utils).ToCSV(sql,,"c:\kit")

メソッドの引数詳細は、クラスメソッドの説明文（Utils.cls 4～12行目）をご参照ください。

出力結果は、第3引数に指定したディレクトリ以下に　yyyymmddhhmmss.拡張子　の形式で出力されます（拡張子は、メソッドの第2引数で指定する形式により異なります）。

ストアドプロシージャの実行を管理ポータルから行う手順は以下の通りです。

管理ポータル > システムエクスプローラー > SQL の画面を開きサンプルをインポートしたネームスペース移動後、ISJSQL.Utils_ToCSV()を実行します。

管理ポータルから任意のSQL実行結果をCSVファイルに出力する方法