重庆思庄Oracle、Redhat认证学习论坛

标题: [原创] 用 sqlldr加载Excel等其他文档内容到oracle [打印本页]

作者: meconsent 时间: 2015-3-30 17:20
标题: [原创] 用 sqlldr加载Excel等其他文档内容到oracle

1.首先将excel文档保存为csv格式我命名为case2.dat//Windows下另存为点保存类型即可找到CSV格式

2.将此文件转存到oracle存在的操作系统某一路径（我的为redhat linux6.4）

3.创建控制文件：
vi case2.ctl
输入以下：
LOAD DATA
INFILE case2.dat //csv文件名
TRUNCATE INTO TABLE lt //lt为目标表名 **此处truncate为插入属性另外还有其他参数实际操作前务必查看相应参数，避免数据丢失此外还有insert append replace truncate
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' //OPTIONALLY ENCLOSED BY '"' 制定参数指明界定符 csv格式一般为"
(id,test) //字段名，必须语表中相同，顺序可不同

4.执行命令 sqlldr scott/scott control=case2.ctl

5.完成

若无分隔符，使用的定长字符串可将控制文件修改：

LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(7:15),
SAL position(17:20)
)

各种其他形式：

列多

LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(7:15),
SAL position(17:20),
COMM "0"
)

截取

LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(7:15),
SAL position(17:20),
COMM "substr(:SAL,1,1)"
)

过滤

LOAD DATA
INFILE ldr_case6.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:6),
TCOL FILLER position(8:11),
JOB position(13:21),
SAL position(23:26)
)

导入不同表

LOAD DATA
INFILE ldr_case9.dat
DISCARDFILE ldr_case9.dsc
TRUNCATE
INTO TABLE BONUS
WHEN TAB='BON'
(TAB FILLER POSITION(1:3),
   ENAME POSITION(5:9) ,
   JOB POSITION(*+1:18),
   SAL POSITION(*+1)
   )
INTO TABLE MANAGER
WHEN TAB = 'MGR'
(TAB FILLER POSITION(1:3),
   MGRNO POSITION(4:5) ,
   MNAME POSITION(7:13),
   JOB POSITION(*+1))

略过前三行数据

sqlldr scott/scott control=case10.ctl skip=3

导入从第三行到第八行

sqlldr scott/scott control=case10.ctl skip=3 load=6

更改换行符

LOAD DATA
INFILE ldr_case11_1.dat
TRUNCATE INTO TABLE MANAGER
FIELDS TERMINATED BY ","
(MGRNO,
MNAME,
JOB,
REMARK "replace(:remark,'\\n',chr(10))"
)

定长处理换行

LOAD DATA
INFILE ldr_case11_2.dat "fix 68"
TRUNCATE INTO TABLE MANAGER
(
MGRNO position(1:2),
MNAME position(*+1:10),
JOB position(*+1:24),
REMARK position(*+1:65)
)

行尾部标示换行

LOAD DATA
INFILE ldr_case11_4.dat "str '|\r\n'"
TRUNCATE INTO TABLE MANAGER
FIELDS TERMINATED BY ','
(MGRNO, MNAME, JOB, REMARK)

数据独立保存的情况

LOAD DATA
INFILE ldr_case12_2.dat
TRUNCATE INTO TABLE LOBTBL
(CREATE_DATE position(1:17) date 'yyyy-mm-dd hh24:mi',
FILESIZE position(*+1:25) "to_number(:FILESIZE,'99,999,999')",
FILEOWNER position(*+1:34),
FILENAME position(*+1) char(200) "substr(:FILENAME,instr(:FILENAME,'\\',-1)+1)",
FILEDATA LOBFILE(FILENAME) TERMINATED BY EOF)

将没有值得列置为NULL
LOAD DATA
INFILE ldr_case13.dat
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY "," TRAILING NULLCOLS
(ENAME,JOB,SAL)

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)