数据导入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

)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30018455/viewspace-1665589/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30018455/viewspace-1665589/