csvファイルのIMPORT&EXPORTの勉強をしていたのでそのメモを。
cuiでcsvデータで出力するときはOracleDBに標準で入っているSQL*Loaderを使います。
EXPORT
IMPORTするにはDB内でspoolを発行して書き出します。
ここではテーブル名を test_table
csvファイルを export.csv とします。
SPOOL /保存したいファイルまでの絶対パス/export.csv
SELECT * FROM test_table;
SPOOL off;
絶対パスを指定せず、ファイル名だけで指定した場合は
/u01/app/oracle/ の直下に保存されます。
これで書き出しができますが、変なところで改行されたり発行したSELECT文まで入ってしまい、そのままのデータとしてうまく扱えないと思うので、整理してみましょう。
まずはSELECT文をcsvファイルに書き込まれないようにするためにサイレントモードでDBに接続します。
//rlwrapを入れているなら
rlwrap -pRed sqlplus -s /nolog
//通常の通りなら
sqlplus -s /nolog
これで左側にでていた SQL > が消えていると思います。
ここから文字数などの設定をしていきます。
set linesize 500; 横一列に表示させる最大文字数
set pagesize 0; ページ間のセパレータを非表示
set colsep ',' カラム間を,で区切る
set feedback off 確認文を表示させない
set echo off コンソールメッセージの非表示
これで一度SELECTを発行してみると１レコードずつ横一列で並んで表示されると思います。
そしてSPOOLを発行するとき、カラム名の間に ||*|| を挟んで設定してください。
SPOOL /保存したいファイルまでの絶対パス/export.csv
SELECT カラム名1||*||カラム名2||*||カラム名3 FROM test_table;
SPOOL off;
これでcsvファイルを見てみると余計な列や余白がなくなり、整頓されている状態になると思います。
IMPORT
csvファイルができたら、IMPORTするための制御ファイルを作成します。
制御ファイルの拡張子は.ctlで、ここではimport.ctlとします。
OPTIONS(LOAD=-1, SKIP=0)
LOAD DATA
INFILE '/絶対パス/export.csv'
APPEND
INTO TABLE test_table_clone
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(column1,column2,column3...)
設定項目
OPTIONS設定
LOAD=-1 何行目まで読み込むのかを設定、-1で全て読み込む
SKIP=0 読み飛ばす行を設定
ROWS=-1 どこでコミットするか設定
ERRORS=-1 許容するエラーの件数を設定
などがあります。
保存方法
INSERT テーブルが空だった場合にINPORTする。デフォルトはこのINSERTになる。
APPEND テーブルに追加でデータを保存する。
REPLACE テーブル内のデータを一度削除してから保存する。ロールバックするが、大量のデータを扱う場合は時間がかかる。
TRUNCATE テーブル内のデータを一度削除してから保存する。ロールバックしないが、大量のデータを高速で扱える。
その他の項目
INTO TABLE 'テーブル名' どのテーブルにIMPORTするか指定
FIELDS TERMINATED BY ',' どの文字で区切るか指定
OPTIONALLY ENCLOSED BY '"' データを囲む文字を指定。csvだと"で囲まれる。
**TRAILING NULLCOLS(column1, column2...)**IMPORTするカラムの指定。
以上が主な設定項目です。
これで制御ファイルと保存元ファイルができたので、実際にIMPORTしていきます。
oracleにログインし、DBに入る前の
-bash-4.2$
の状態で以下のコマンドを発行してください。
sqlldr ユーザー名/ユーザーパス /絶対パス/import.ctl;
SQL*LOADERを使ってcsvファイルを元にテーブルにデータをINSERTできると思います。ここでエラーが出た場合はctlファイルやctl,csvのパーミッションを見直してみてください。
開発環境
CeontOS7
Oracle XE 11g
csv,ctl共に設定方法で苦戦していました。
足りない部分が多々あるので各々で調べてみてください。