タイトル
TOP → Oracle → This Page

SQL*Loader制御ファイルサンプル

概要

ここではSQL*Loaderの制御ファイルをケース別にサンプルとして紹介します。
SQL*Loader自体の説明は SQL*Loader の解説ページを参照してね♪

例1 固定長ファイル、改行なし

テーブル 
CREATE TABLE TEST1
(
	TESTCD	CHAR(4) NOT NULL,
	TESTNM	CHAR(6) NOT NULL,
	CONSTRAINT PK_TEST1 PRIMARY KEY (TESTCD)
)
データ 
0001あああ0002いいい0003ううう0004えええ0005おおお
制御ファイル 
LOAD DATA
INFILE 'TEST1.DAT' "fix 10"
BADFILE 'TEST1.BAD'
DISCARDFILE 'TEST1.DSC'
APPEND INTO TABLE TEST1
(
TESTCD POSITION(1:4) CHAR,
TESTNM POSITION(5:10) CHAR
)
ロード結果 
TESTCD	TESTNM
0001	あああ
0002	いいい
0003	ううう
0004	えええ
0005	おおお

例2 固定長ファイル、改行あり

テーブル 
CREATE TABLE TEST2
(
	TESTCD	CHAR(4) NOT NULL,
	TESTNM	CHAR(6) NOT NULL,
	CONSTRAINT PK_TEST2 PRIMARY KEY (TESTCD)
)
データ 
0001あああ
0002いいい
0003ううう
0004えええ
0005おおお
制御ファイル 
LOAD DATA
INFILE 'TEST2.DAT'
BADFILE 'TEST2.BAD'
DISCARDFILE 'TEST2.DSC'
APPEND INTO TABLE TEST2
(
TESTCD POSITION(1:4) CHAR,
TESTNM POSITION(5:10) CHAR
)
ロード結果 
TESTCD	TESTNM
0001	あああ
0002	いいい
0003	ううう
0004	えええ
0005	おおお

例3 可変長ファイル、カンマ区切り、囲み記号なし

テーブル 
CREATE TABLE TEST3
(
	TESTCD	VARCHAR(4) NOT NULL,
	TESTNM	VARCHAR(6) NOT NULL,
	CONSTRAINT PK_TEST3 PRIMARY KEY (TESTCD)
)
データ 
1,あ
2,いい
30,ううう
400,え
5000,おおお
制御ファイル 
LOAD DATA
INFILE 'TEST3.DAT'
BADFILE 'TEST3.BAD'
DISCARDFILE 'TEST3.DSC'
APPEND INTO TABLE TEST3
FIELDS TERMINATED BY ','
(
TESTCD,
TESTNM
)
ロード結果 
TESTCD	TESTNM
1	あ
2	いい
30	ううう
400	え
5000	おおお

例4 可変長ファイル、カンマ区切り、囲み記号あり

テーブル 
CREATE TABLE TEST4
(
	TESTCD	VARCHAR(4) NOT NULL,
	TESTNM	VARCHAR(6) NOT NULL,
	CONSTRAINT PK_TEST4 PRIMARY KEY (TESTCD)
)
データ 
"1","あ"
"2","いい"
"30","ううう"
"400","え"
"5000","おおお"
制御ファイル 
LOAD DATA
INFILE 'TEST4.DAT'
BADFILE 'TEST4.BAD'
DISCARDFILE 'TEST4.DSC'
APPEND INTO TABLE TEST4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
TESTCD,
TESTNM
)
ロード結果 
TESTCD	TESTNM
1	あ
2	いい
30	ううう
400	え
5000	おおお

例5 可変長ファイル、カンマ区切り、シーケンス項目、日付項目

テーブル 
CREATE TABLE TEST5
(
	TESTCD	VARCHAR(4) NOT NULL,
	TESTNM	VARCHAR(6) NOT NULL,
	SEQ1	NUMBER(2) NOT NULL,
	SEQ2	NUMBER(2) NOT NULL,
	SEQ3	NUMBER(2) NOT NULL,
	TESTDATE	DATE NOT NULL,
	CONSTRAINT PK_TEST5 PRIMARY KEY (TESTCD)
)
データ 
A,あああ,20000102
B,いいい,20010203
C,ううう,20011122
D,えええ,20011231
E,おおお,20100101
制御ファイル 
LOAD DATA
INFILE 'TEST5.DAT'
BADFILE 'TEST5.BAD'
DISCARDFILE 'TEST5.DSC'
APPEND INTO TABLE TEST5
FIELDS TERMINATED BY ','
(
TESTCD,
TESTNM,
SEQ1 SEQUENCE(1, 1),
SEQ2 SEQUENCE(COUNT, 2),
SEQ3 SEQUENCE(MAX, 3),
TESTDATE CHAR "TO_DATE(:TESTDATE, 'YYYYMMDD')"
)
ロード結果 
TESTCD	TESTNM	SEQ1	SEQ2	SEQ3	TESTDATE
A	あああ	1	2	3	2000/01/02
B	いいい	2	4	6	2001/02/03
C	ううう	3	6	9	2001/11/22
D	えええ	4	8	12	2001/12/31
E	おおお	5	10	15	2010/01/01

例6 可変長ファイル、カンマ区切り、無視項目、定数、システム日時、関数

テーブル 
CREATE TABLE TEST6
(
	TESTCD	VARCHAR(4) NOT NULL,
	TESTNM	VARCHAR(6) NOT NULL,
	TESTNOTE	VARCHAR(10) NOT NULL,
	TESTDATE1	DATE NOT NULL,
	TESTDATE2	VARCHAR(14) NOT NULL,
	CONSTRAINT PK_TEST6 PRIMARY KEY (TESTCD)
)
データ 
A,あああ,dummy
B,いいい,dummy
C,ううう,dummy
D,えええ,dummy
E,おおお,dummy
制御ファイル 
LOAD DATA
INFILE 'TEST6.DAT'
BADFILE 'TEST6.BAD'
DISCARDFILE 'TEST6.DSC'
APPEND INTO TABLE TEST6
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
TESTCD,
TESTNM,
NOCOLUMN FILLER,
TESTNOTE CONSTANT 'XXXXX',
TESTDATE1 SYSDATE,
TESTDATE2 CHAR "TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')"
)
ロード結果 
TESTCD	TESTNM	TESTNOTE	TESTDATE1	TESTDATE2
A	あああ	XXXXX	2010/02/17 11:36:04	20100217113604
B	いいい	XXXXX	2010/02/17 11:36:04	20100217113604
C	ううう	XXXXX	2010/02/17 11:36:04	20100217113604
D	えええ	XXXXX	2010/02/17 11:36:04	20100217113604
E	おおお	XXXXX	2010/02/17 11:36:04	20100217113604

例7 可変長ファイル、カンマ区切り、WHEN条件

テーブル 
CREATE TABLE TEST7
(
	TESTCD	VARCHAR(4) NOT NULL,
	TESTNM	VARCHAR(6) NOT NULL,
	TESTTYPE	CHAR(1) NOT NULL,
	CONSTRAINT PK_TEST7 PRIMARY KEY (TESTCD)
)
データ 
11,あああ,A
12,いいい,B
13,ううう,C
14,えええ,A
15,おおお,B
21,かかか,C
22,ききき,A
23,くくく,B
24,けけけ,C
25,こここ,A
制御ファイル 
LOAD DATA
INFILE 'TEST7.DAT'
BADFILE 'TEST7.BAD'
DISCARDFILE 'TEST7.DSC'
APPEND INTO TABLE TEST7
WHEN (TESTTYPE = 'A')
FIELDS TERMINATED BY ','
(
TESTCD,
TESTNM,
TESTTYPE
)
ロード結果 
TESTCD	TESTNM	TESTTYPE
11	あああ	A
14	えええ	A
22	ききき	A
25	こここ	A

例8 複数ファイル指定

テーブル 
CREATE TABLE TEST8
(
	TESTCD	VARCHAR(4) NOT NULL,
	TESTNM	VARCHAR(6) NOT NULL,
	CONSTRAINT PK_TEST3 PRIMARY KEY (TESTCD)
)
データ-1 
101,あああ
102,いいい
103,ううう
104,えええ
105,おおお
データ-2 
201,かかか
202,ききき
203,くくく
204,けけけ
205,こここ
制御ファイル 
LOAD DATA
INFILE 'TEST8-1.DAT' BADFILE 'TEST8-1.BAD' DISCARDFILE 'TEST8-1.DSC'
INFILE 'TEST8-2.DAT' BADFILE 'TEST8-2.BAD' DISCARDFILE 'TEST8-2.DSC'
APPEND INTO TABLE TEST8
FIELDS TERMINATED BY ','
(
TESTCD,
TESTNM
)
ロード結果 
TESTCD	TESTNM
101	あああ
102	いいい
103	ううう
104	えええ
105	おおお
201	かかか
202	ききき
203	くくく
204	けけけ
205	こここ

例9 固定長ファイル、改行あり、小数点数値や日付(DATEやTIMESTAMP)あり

テーブル 
CREATE TABLE TEST9
(
	TESTCD	CHAR(4) NOT NULL,
	TESTNM	CHAR(6) NOT NULL,
	NUMVAL	NUMBER(10,3),
	DATEVAL1	DATE,
	DATEVAL2	DATE,
	TIMEVAL1	TIMESTAMP,
	TIMEVAL2	TIMESTAMP,
	CONSTRAINT PK_TEST9 PRIMARY KEY (TESTCD)
)
データ 
0001あああ 1234567.890201201010000012012/01/01 00:00:01201201010000011111112012-01-01 00:00:01.111111
0002いいい-1234567.890201201010000022012/01/01 00:00:02201201010000022222222012-01-01 00:00:02.222222
0003ううう       1.000201201010000032012/01/01 00:00:03201201010000033333332012-01-01 00:00:03.333333
0004えええ       0.123201201010000042012/01/01 00:00:04201201010000044444442012-01-01 00:00:04.444444
0005おおお      -0.123201201010000052012/01/01 00:00:05201201010000055555552012-01-01 00:00:05.555555
制御ファイル 
LOAD DATA
INFILE 'TEST9.DAT'
BADFILE 'TEST9.BAD'
DISCARDFILE 'TEST9.DSC'
APPEND INTO TABLE TEST9
(
TESTCD POSITION(1:4) CHAR,
TESTNM POSITION(5:10) CHAR,
NUMVAL POSITION(11:22) "TO_NUMBER(:NUMVAL)",
DATEVAL1 POSITION(23:36) DATE(14) "YYYYMMDDHH24MISS",
DATEVAL2 POSITION(37:55) DATE(19) "YYYY/MM/DD HH24:MI:SS",
TIMEVAL1 POSITION(56:75) "TO_TIMESTAMP(:TIMEVAL1,'YYYYMMDDHH24MISSFF6')",
TIMEVAL2 POSITION(76:101) "TO_TIMESTAMP(:TIMEVAL2,'YYYY/MM/DD HH24:MI:SS.FF6')"
)
ロード結果 
TEST TESTNM     NUMVAL DATEVAL1            DATEVAL2            TIMEVAL1                   TIMEVAL2
0001 あああ 1234567.89 2012-01-01 00:00:01 2012-01-01 00:00:01 2012-01-01 00:00:01.111111 2012-01-01 00:00:01.111111
0002 いいい -1234567.9 2012-01-01 00:00:02 2012-01-01 00:00:02 2012-01-01 00:00:02.222222 2012-01-01 00:00:02.222222
0003 ううう          1 2012-01-01 00:00:03 2012-01-01 00:00:03 2012-01-01 00:00:03.333333 2012-01-01 00:00:03.333333
0004 えええ       .123 2012-01-01 00:00:04 2012-01-01 00:00:04 2012-01-01 00:00:04.444444 2012-01-01 00:00:04.444444
0005 おおお      -.123 2012-01-01 00:00:05 2012-01-01 00:00:05 2012-01-01 00:00:05.555555 2012-01-01 00:00:05.555555

サンプルダウンロード

CREATE TABLE 文、データ、制御ファイル、バッチファイルが揃ったサンプルです。
(上記の例1～8のサンプルファイルです)
Windows環境なら解凍してすぐに使えます。

クリックで ダウンロード

更新履歴

2012/02/22　例9にTIMESTAMPを追加
2012/01/21　例9を追加
2010/02/26　例8を更新
2010/02/17　新規作成

TOP → Oracle → This Page