いきさつ
突然必要となった大量データ、大量テーブルのcsv取り込み、なかなか面倒です。 ネットに転がっているvbsも動かず、SQLDeveloperで１テーブルずつせこせこ作成するのもつらい・・・。 というわけで、Oracle使いならやっぱCUIでしょ！
準備するもの
まず、以下のスクリプトをコピーして、"csvimport.sql"というファイルを作成します。 このとき、以下の部分をメンテしてください。
| メンテ箇所 | |
| user/password@servicename | ユーザー名/パスワード@Oracle接続子(ネット・サービス名) |
| in ('TABLE1','TABLE2'..) | 取り込み対象テーブル |
| TRUNCATE | 実行すると、テーブルがリセットされますので、追加にしたい場合はAPPEND等に変更して下さい。 |
| SKIP=1 | 1行目を読み飛ばします。タイトルが無い場合(1行目からデータの場合)は、省略して下さい。 |
| ^`r | CSVの項目値としての改行コードを指定します(\r\n等)。CSVレコードの終端コードではありません。 別ページにあるCSVデータ出力バッチ作成を実行すると、 改行コードが"^`r"や"^`n"に変換されて出力されるのでそれを改行コードに戻してインポートする場合、左記の様に指定します。 |
| ^`n | |
ちなみに、Windowsが対象です。UNIX系(Solaris,Linux等)の場合はそのまま使えません。いい感じにカスタマイズして下さい。
------------------------------------------------------------------
-- Copyright(c) 2015-2016 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2015.08.22 作成
-- 2015.08.27 date,timestamp型を明確化,バッチ実行順を変更
-- 2015.09.01 画面出力メッセージを修正
-- 2015.09.08 処理手順を簡潔化、コメントに対応
-- 2015.09.14 先頭行をスキップ化
-- 2015.09.16 フォルダ構成をCSV_DATA,SQLLDR_CTL,SQLLDR_LOGに
-- 2015.09.17 ^`r^`nをCRLFに置換して取り込み
-- 2016.02.26 画面表示テーブル名を固定長化、TRAILING NULLCOLSを追加
--
-- [実行方法]
-- 1)このテキストを、"csvimport.sql"として作成します。
-- 2)作成したフォルダ上で、コマンドプロンプトを起動し、以下のコマンドを実行します。
-- @echo off
-- sqlplus -s user/password@servicename @csvimport.sql
-- csvimport.bat
-- 3)ログファイルを調査し、エラーが発生していないか確認します。
--
-- [注意事項]
-- LOB系、LONG型には対応していません。
-- "^`r"⇒CR、"^`n"⇒LFに置換して取り込みます。置換を使用せず、改行コードをそのまま値としてダイレクトに取り込みたい場合は、レコード終端(EOR)判定制御を組み込んで下さい。
------------------------------------------------------------------
set escape ?
set linesize 300
set pagesize 0
set term off
set head off
set feed off
set trim on
set trimspool on
set verify off
$mkdir CSV_DATA >> __csvimport1.log 2>> __csvimport2.log
$mkdir SQLLDR_CTL >> __csvimport1.log 2>> __csvimport2.log
$mkdir SQLLDR_LOG >> __csvimport1.log 2>> __csvimport2.log
------------------------------------------------------------------
-- __csvimport1.sql
------------------------------------------------------------------
spool __csvimport1.sql
prompt spool SQLLDR_CTL/?&?&1..ctl
prompt prompt OPTIONS(DIRECT=Y,MULTITHREADING=Y)
prompt prompt LOAD DATA INFILE 'CSV_DATA/?&?&1..csv'
prompt prompt TRUNCATE INTO TABLE ?&?&1
prompt prompt FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS(
prompt select case when col.column_id = 1 then ' ' else ' , ' end
prompt || rpad(col.column_name,31)
prompt || rpad(case when col.data_type = 'DATE' then rpad(col.data_type,15) || ' "YYYY/MM/DD HH24:MI:SS"'
prompt when col.data_type like 'TIMESTAMP(%)' then rpad(col.data_type,15) || ' "YYYY/MM/DD HH24:MI:SS.FF' || col.data_scale || '"'
prompt when col.data_type = 'CHAR' then '"REPLACE(REPLACE(:' || rpad(col.column_name,30) || ',''^`r'', CHR(13)),''^`n'', CHR(10))"'
prompt when col.data_type = 'VARCHAR2' then '"REPLACE(REPLACE(:' || rpad(col.column_name,30) || ',''^`r'', CHR(13)),''^`n'', CHR(10))"'
prompt else ' ' end,100)
prompt || nvl2(cmn.comments,'-- ' || cmn.comments,' ')
prompt from user_tab_columns col
prompt , user_col_comments cmn
prompt where col.table_name = '?&?&1'
prompt and col.table_name = cmn.table_name(+)
prompt and col.column_name = cmn.column_name(+)
prompt order by col.column_id asc
prompt /
prompt prompt )
prompt spool off
spool off
------------------------------------------------------------------
-- __csvimport2.sql
------------------------------------------------------------------
spool __csvimport2.sql
select '@__csvimport1.sql ' || tbl.table_name as cmdline
from user_tables tbl
where tbl.table_name in ('TABLE1','TABLE2'..)
order by table_name asc
/
spool off
@__csvimport2.sql
------------------------------------------------------------------
-- csvimport.bat
------------------------------------------------------------------
spool csvimport.bat
prompt @echo off
select 'SET /p X=' || rpad( tbl.table_name, 30) || ' <nul
SQLLDR user/password@servicename '
|| ' SKIP=1'
|| ' CONTROL=''SQLLDR_CTL/' || rpad( tbl.table_name || '.ctl''', 40)
|| ' LOG=''SQLLDR_LOG/' || to_char(sysdate,'YYYYMMDDHH24MISS') || '_' || tbl.table_name || '.log'''
|| ' | findstr -i ロードは完了しました。' as cmdline
from user_tables tbl
where tbl.table_name in ('TABLE1','TABLE2'..)
order by table_name asc
/
prompt ECHO ロード処理がすべて終了しました。詳細はログファイルを参照して下さい。
spool off
$del __csvimport*.*
$echo スクリプトを生成しました。
exit
CSVデータの配置
スクリプトの存在するフォルダ上で以下のサブフォルダを作成、CSVデータを配置します。
| 事前に用意するサブフォルダ | |
| CSV_DATA | データベースに取り込むためのCSVデータ |
実行
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
@echo off
sqlplus -s user/password@servicename @csvimport.sql
csvimport.bat
| 実行時に生成されるサブフォルダ | |
| SQLLDR_CTL | SQLローダー用の制御ファイルを生成 |
| SQLLDR_LOG | SQLローダーの実行結果ログ |
