sqlldr导入带有双引号的txt文件

转：http://blog.itpub.net/23009281/viewspace-756554/

 

1、源数据：data.txt

name log_times

"aa","2013-2-4 23:42:29"

"bb","2013-2-4 23:23:07"

"cc","2013-2-4 23:29:01"

"dd","2013-2-4 23:18:26"

"ee","2013-2-9 12:37:09"

"ff","2013-2-9 12:27:25"

"gg","2013-2-9 12:36:41"

"hh","2013-2-9 0:19:10"

"ii","2013-2-9 0:07:59"

"jj","2013-2-9 0:36:35"

2、创建表：test_sqlldr

create table  test_sqlldr

(

  name VARCHAR2(20),

  TDATE      DATE

)

3、control控制文件：load_data.ctl

LOAD DATA 

INFILE "D:\data.txt"   --源数据

APPEND

INTO TABLE test_sqlldr 

TRAILING NULLCOLS   --源数据没有对应，写入null

(

name CHAR TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',   --源文件有双引号，这里去掉

TDATE DATE "YYYY-MM-DD HH24:MI:SS" TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'   --源文件有双引号，这里去掉

)

4、window下的命令导入：

sqlldr user/password d:\ load_data.ctl   d:\load_data.log ROWS=50000 BINDSIZE=10485760 skip=1