这两天工作中就大量地遇到这个问题,如何将EXCEL中大量的数据导入到oracle数据库中。工作内容就是如果excel中的数据少,就一列一列复制到PL/SQL developer中选定表执行,如果表中的数据庞大就修改excel中的内容,将EXCEL中的内容修改地和oracle中的表结构一致后使用工具软件导入。
作为一个有志青年,我不能做一个不求甚解的人。经过查找资料,我理解了将excel导入oracle中的本质:SQL*loader 。
SQL*loader作为oracle中非常常用的功能,大家一定要多多练习,多多使用,才能慢慢地掌握这个工具,通过这也能理解oracle设计中的精妙之处。
Sql*loader是个工具,oracle公司封装了它的实现,我们很好的利用这个工具能解决很多的向oracle数据库中导入数据的问题。
如图所示,控制文件是整个操作的核心。正确合理的运用控制文件是整个操作的关键。
闲话不多说,开始试验。
1, 测试在控制文件中直接添加数据。
试验过程全部在scott的schema中操作。我们新建一个表
SQL>
SQL> show user
User is "scott"
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
SQL> create table test(
2 id number,
3 name varchar2(30));
Table created
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
TEST
SQL
我们想向test中添加数据,编辑控制文件 ldr_test.ctl 内容如下
/*后为注释内容
load data
/*控制文件开头,表明开始加载
infile *
/*表明数据文件的位置,*表示数据文件在控制文件中
insert into table test
/*表明要插入数据库中的表
fields terminated by ","
/*表明文件数据文件要用什么来分隔表示不同的内容
(id,name)
/*此为引述的数据库中表的结构
Begindata
/*此标志下面都是要引用的数据了
1,tom
/*第一个元素,id为1,name为tom
2,lily
打开命令行窗口,执行sqlldr scott/tiger control=C:\ldr_test.ctl
提示成功。由于我在c盘根目录下执行,所以日志文件会自动生成在C盘根目录下。打开c盘。看到ldr_test.log文件。打开内容如下
SQL*Loader: Release 10.1.0.2.0 - Production on 星期五 12月 28 21:02:15 2012
Copyright (c) 1982, 2004, Oracle. All rights reserved.
控制文件:
C:\ldr_test.ctl
数据文件:
C:\ldr_test.ctl
错误文件:
C:\ldr_test.bad
废弃文件: 未作指定
:
(可废弃所有记录)
要加载的数: ALL
要跳过的数: 0
允许的错误: 50
绑定数组: 64 行, 最大 256000 字节
继续:
未作指定
所用路径:
常规
表 TEST,已加载从每个逻辑记录
插入选项对此表 REPLACE 生效
列名
位置
长度
中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
ID
FIRST * , CHARACTER
NAME
NEXT * , CHARACTER
表 TEST:
2 行 加载成功。
由于数据错误, 0 行 没有加载。
由于所有 WHEN 子句失败, 0 行 没有加载。
由于所有字段都为空的, 0 行 没有加载。
为绑定数组分配的空间:
33024 字节 (64 行)
读取
缓冲区字节数: 1048576
跳过的逻辑记录总数:
0
读取的逻辑记录总数:
2
拒绝的逻辑记录总数:
0
废弃的逻辑记录总数:
0
从 星期五 12月 28 21:02:15 2012 开始运行
在 星期五 12月 28 21:02:15 2012 处运行结束
经过时间为: 00: 00: 00.08
CPU 时间为: 00: 00: 00.05
控制文件的第三句话要详细说明一下,insert into table test ,insert语句表明要向表test中插入,但是要求后面的表为空。除了insert外还有replace ,append,truncate 3个不同的动作。Replace表示先delete表中全部数据,然后再insert数据。Truncate动作和replace动作类似,但是truncate动作时以truncate方式删除表中的数据。
有了数据嵌套在控制文件中的方式的基础,我们研究下如何从excel中的数据导入到oracle中,这里我们编辑一个excel文件。
控制文件已经说明 field terminated by “,” 但是excel中的文件不是以逗号组成的二元组形式。所以,我们只要把excel中的数据保存成以逗号组成的二元组就行了。Excel中提供了这样的保存方式,只要在另存为选项中选择csv(comma separate values)
保存过后我们可以用记事本打开刚才的数据文档,我们发现已经如我们愿成为想要的格式了。现在只需要修改控制文件。
load data
infile ldr_testdata.csv
/*一定要注意执行时的目录,文件名加目录会报错
append into table test
fields terminated by ","
(id,name)
Sqlldr命令执行成功,这时我们返回数据库中查看下
成功添加。
经过多次的试验,得出cmd中执行的环境非常重要。Sqlldr的低级用法就写到这里,明天继续做试验,进行sqlldr的高级内容试验。
为了梦想,加油。
转载于:https://blog.51cto.com/bestlanzi/1103529