資料匯入SQLLDR

 

create table test_lff

(id number,

 name varchar2(10),

 memo varchar2(10)

 );

 commit;

--1.將所有資料羅列出來的匯入方法 D:\test_lff.ctl

options(skip=1)

load data

infile *

truncate

into table test_lff

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

 id,name,memo

)

begindata

1,john,

2,susie,2_susie

3,test,test

sqlldr userid=scott/tiger@ORG11G_1 control=D:\test_lff.ctl log=test_lff.log;

select * from test_lff;

--結果 options(skip=1)

1     2     susie 2_susie

2     3     test  test

skip=0時，則可將三條記錄全部匯入，而且之前已有的資料會被覆蓋

---

--2.將要匯入的資料單獨存放在檔案中/data/dump_dir/dir_lff/shixiao.csv然後進行匯入

LOAD DATA

infile '/data/dump_dir/dir_lff/shixiao.csv'

badfile 'shixiao.bad' 

append

into table test_lff

fields terminated by ','

trailing nullcols       

(

sender_name, 

sender_mobile_phone,

waybill_no,  receiver_name,

receiver_mobile_phone, 

sender_time, 

receive_time, 

sender_city, 

sender_prov,

receiver_city, 

receiver_prov                       

)

sqlldr ytrep/ytrep control=lff.ctl

--------------------------------------------

load data

infile '/home/oracle/script_zxf/data_lff_20150312.txt'

truncate

into table lff_998

FIELDS TERMINATED BY X'13'   ---行與行之間的分隔符

---optional enclosed by '""' 欄位與欄位之間的分隔符

TRAILING NULLCOLS

(

   WAYBILL_NO

)

$ cat lff_998_1.ctl

--------------------------

load data

infile '/home/oracle/script_zxf/lff_998_1.txt'

truncate

into table lff_998

FIELDS TERMINATED BY X'13'  ------X'09'製表符 X'13'換行符 \r

TRAILING NULLCOLS

(

   WAYBILL_NO

)