日々常々

ふつうのプログラマがあたりまえにしたいこと。

テーブルのデータをCSV出力する

2020-07-10追記: Oracleの話です。あと2010年に書いたものです。

  • 力技そのいち
    • SQL*Plusの列区切り文字を「,」にする。
    • 利点は、なんといっても手軽さ。
    • 欠点
      • 可変長項目が固定長になってしまう。
      • その為NULLとスペース埋めや、末尾スペースの存在等が判別不可。
      • 複数テーブルを一気に取得できない。
  • 力技そのに
    • 列を「,」で繋げるSQLを発行する。
    • 利点は、spoolを使用しなければOracleでなくても出来る事。
    • 欠点
      • 列名を列挙するのが非常に手間。
      • 対象テーブル数、列数が増えると現実的じゃないほど面倒になる。
      • 複数テーブルを一気に取得できない。
    • SQLを作るSQLを使えば、SQL作成の手間はなくせる。
  • PL/SQL
    • 利点
      • 頑張れば出力項目を選別するとか拡張出来る。
      • 複数テーブルを一気に取得できる。
    • 欠点
      • バージョン依存度が高い。(10gR2以降)
      • その場でさらっと書いて実行するのは現実的でない。
  • SQLを作るSQLで作ったSQLを実行した結果をファイルに書き出す
    • 今回書いている方法。
    • 日本語で書くと長ったらしいが、実際の手間は殆ど無い。
    • 単純なSQLなので、やり方さえ判っていればその場で書ける。
    • おそらく一番現実的で汎用的。



以下のSQLを任意のファイル名(OutputCsv.sql等)で保存し、SQL*Plusで対象テーブルと出力ファイル名を指定して実行すると、対象テーブルのデータがCSV形式で出力されます。

SQLファイル内容

set feedback off
set trimspool on
set linesize 32767
set pagesize 0
set verify off
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; 

spool _OutputCsv.sql
prompt spool &2
--列名表示
SELECT
  DECODE(COLUMN_ID, '1', 'SELECT ', ' || '','' || '),
  '''' || COLUMN_NAME || '''',
  DECODE(COLUMN_ID,
    MAX(COLUMN_ID) OVER(PARTITION BY TABLE_NAME), ' FROM DUAL;')
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&1')
ORDER BY COLUMN_ID;
--データ取得、値はダブルクォテーションで囲む
SELECT
  DECODE(COLUMN_ID, '1', 'SELECT ', ' || '','' || '),
  '''"'' || ' || COLUMN_NAME || ' || ''"''',
  DECODE(COLUMN_ID,
    MAX(COLUMN_ID) OVER(PARTITION BY TABLE_NAME), ' FROM ' || TABLE_NAME || ';')
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&1')
ORDER BY COLUMN_ID;
prompt spool off
spool off
@_OutputCsv.sql
exit

実行コマンド(例)

sqlplus USER/PASS @OutputCsv.sql TEST_TABLE output_TEST_TABLE.csv
  • ユーザ名:USER
  • パスワード:PASS
  • SQLファイル名:OutputCsv.sql
  • 対象テーブル名:TEST_TABLE
  • 出力ファイル名:output_TEST_TABLE.csv

複数テーブルを対象とする場合は、単純にテーブル数分コマンドを実行してください。出力ファイルは上書きされますので、複数テーブル対象時は特に、被らないように気をつけてください。

処理内容

  1. USER_TAB_COLUMNSを<対象テーブル>で検索する。
  2. 列を「,」で繋げるSQLを作成する。
  3. 作成したSQLを _OutputCsv.sql に出力する。
  4. _OutputCsv.sql を実行する。
  5. 作成されたSQLの取得結果を<出力ファイル>に出力する。

注意点等

データが0件の場合は、列名のみ1行だけ出力されます。ログインしたスキーマに対象テーブルが存在しない場合や、単純なタイプミス等の場合は、出力ファイルには何も出力されません。

出力ファイル内容(例)

COL1,COL2,COL3,COL4
"1","2010-01-08 22:51:30","","a1"
"2","1900-01-01 00:00:00","A      ","a123"
"3","2010-01-08 23:55:41","ABCDEFG","a12   "
"4","2010-01-09 00:46:26","       ",""

上記内容が出力されるテーブルの定義

SQL> desc TEST_TABLE
 名前               NULL?    型
 ------------------ -------- ---------------------
 COL1                        NUMBER(5)
 COL2                        DATE
 COL3                        CHAR(7)
 COL4                        VARCHAR2(20)

おまけ

全テーブルを出力するコマンドを作るSQL

SELECT 'sqlplus user/pass @OutputCsv.sql '
       || TABLE_NAME
       || ' output_' || TABLE_NAME || '.csv'
  FROM USER_TABLES;