ORACLE SQLLOADER

1. 数据长度固定文件

数据文件：

12,aaa,bbb
13,ccc,ddd
14,eee,fff
15,ggg,hhh

控制文件：

load data
infile 'd:\load_data.txt'
append
into table scott.dept(
deptno position(1:2) char,
dname position(4:6) char,
Loc position(8:10) char)

运行：sqlldrscott/tiger@democontrol=d:\load_control.ctl data=d:\load_data.txt

2. 长度不固定数据

数据文件：

16,aaadsasd,bbbd
17,cccsdds,ddddfdfd
18,eeedfdfdfdfd,fffdfdf
19,gggsd,hhhdfdf

控制文件：

load data
infile 'd:\load_data.txt'
append
into table scott.dept(
deptno char terminated by ",",
dname char terminated by ",",
Loc char terminated by ",")

运行：sqlldrscott/tiger@democontrol=d:\load_control.ctl data=d:\load_data.txt

For the data with date or number format

Example: (Linux)

Data File Book1.csv1,1992/01/02,55.75

2,1992/01/03,60

3,1992/01/06,58.75

4,1992/01/07,57.5

5,1992/01/08,58.5

Control file data.ctl

load data

infile "/tmp/Book1.csv"

append into table dm_data_1

(id terminated by ',',

price_date date "YYYY/MM/DD" terminated by ',',

price terminated by whitespace)

Execute: sqlldr dm/dm control=/tmp/data.ctl data=/tmp/Book1.csv

Example(Windows)

Data File

0208032501|78.16.162.82|shauna.mcweeney@hotmail.com|0

0208032658|78.16.162.82|shauna.mcweeney@hotmail.com|1

0208032737|2.25.239.31|J-d-yen11328@hotmail.com|1

Control file load_control.ctl

load data

infile 'F:\SubEU_Access_Log_Analysis\access_log.txt'

append

into table cardholder_access(

TIMESTAMP DATE  "MMDDHH24MISS" TERMINATED BY "|",

IP CHAR  TERMINATED BY "|",

LOGIN CHAR  TERMINATED BY "|",

RESPONSE CHAR        TERMINATED BY WHITESPACE,

TOMCAT_SERVER  CONSTANT "TomcatThor (61)",

WEB_HOST CONSTANT "SubEU01",

APPLICATION_CONTEXT CONSTANT "cardholder"

)