oracle9i sqlldr导入csv文件示例

参考: http://airlgc.blog.51cto.com/161810/30179

 

1.数据文件D:\data\test.csv,内容如下:

 

13811399900,13814637554,2010-01-03 00:20:23,0.82,0.89,1.71
13811399900,13817776540,2010-01-03 20:07:03,0.68,0.32,1.0
13811399900,13810185614,2010-01-03 17:12:43,0.67,0.52,1.19
13811399900,13818059540,2010-01-03 09:13:28,0.06,0.93,0.99
13811399900,13810447836,2010-01-03 03:27:00,0.69,0.44,1.13
13811399900,13813858487,2010-01-03 00:40:37,0.5,0.04,0.54
13811399900,13817990048,2010-01-03 10:46:10,0.15,0.09,0.24
13811399900,13816020117,2010-01-03 11:34:03,0.25,0.74,0.99
13811399900,13816459470,2010-01-03 13:56:49,0.05,0.1,0.15
13811399900,13813181827,2010-01-03 11:41:47,0.23,0.36,0.59
13811399900,13811068450,2010-01-03 06:18:20,0.09,0.17,0.26
13811399900,13818080603,2010-01-03 04:45:46,0.03,0.76,0.79
13811399900,13818655262,2010-01-03 21:28:24,0.05,0.64,0.69

 

2.控制文件 D:\data\insert.ctl,请注意，我们指定了日期数据类型的格式：

 

 load   data
infile   'D:\data\test.csv'
append  into   table   table2
fields terminated   by   ','
(C1,
C2,
C3 Date "yyyy-mm-dd hh24:mi:ss" ,
C4,
C5,
C6)

 

3.表结构:

 

create table TABLE2
(
  C1 VARCHAR2(12),
  C2 VARCHAR2(12),
  C3 DATE,
  C4 NUMBER(10,2),
  C5 NUMBER(10,2),
  C6 NUMBER(10,2)
)

 

4. 执行：

 

sqlldr test/test2@db_192.168.1.2  D:\data\insert.ctl

 

5.在控制文件最上方增加一行 unrecoverable：

 

unrecoverable

load   data
infile   'D:\data\test.csv'
append  into   table   table2
fields terminated   by   ','
(C1,
C2,
C3 Date "yyyy-mm-dd hh24:mi:ss" ,
C4,
C5,
C6)

 

6.在sqlplus中关闭日志：

 

SQL> alter table table2 nologging;

 

7.执行:

 

sqlldr test/test2@db_192.168.1.2 D:\data\insert.ctl direct=true parallel=true