使用 Oracle 的 sqlldr 将 excel 中的数据批量导入到数据库中
原创
©著作权归作者所有:来自51CTO博客作者程序员秃头之路的原创作品,请联系作者获取转载授权,否则将追究法律责任
前言
最近有一个将 excel 中的数据批量导入到 oracl 数据库中的需求,首先想到用 poi 写 java 代码来实现,后来想查查看有没有更简单的方法,然后就搜索到可以使用 sqlldr 工具来将数据导入到 oracle 中,下面就是导入数据的相关知识和过程。
一、sqlldr 是什么?
SQLLOADER是ORACLE的数据加载工具,通常用来将操作系统文件(数据)迁移到ORACLE数据库中。SQLLOADER是大型数据仓库选择使用的加载方法,因为它提供了最快速的途径(DIRECT,PARALLEL)。
二、使用案例
1.导入的数据
excel数据如下:
对应的表:
create table AJ_PM.PM_CONSTRUCTION_TEAM
(
construction_team_id NUMBER not null,
construction_team_code VARCHAR2(50),
construction_team_name VARCHAR2(250),
construction_team_ful_name VARCHAR2(250),
contact_tel VARCHAR2(11),
create_by NUMBER,
create_date DATE
)
2.将 excel 另存为 txt 格式
选择制表符分隔那个选项:
导出的 txt 是这样的:
3.创建sql*loader控制文件 import.ctl
可以先传建一个 txt 的文档,然后写好后将后缀改成 .ctl 就可以了
load data
infile '新增的分包企业.txt'
append into table pm_construction_team
fields terminated by X'09'
trailing nullcols(
CONSTRUCTION_TEAM_ID "seq_pm_construction_team.nextval",
CONSTRUCTION_TEAM_CODE,
CONSTRUCTION_TEAM_NAME,
CONSTRUCTION_TEAM_FULL_NAME,
CONTACT_TEL
)
ctl文件内容说明:
Load data
Infile '新增的分包企业.txt' --数据源文件名称
Append|insert|replace --append在表后追加insert插入空表replace替代原有内容
Into table pm_construction_team --要导入的数据库表名称
[when id = id_memo] --过滤条件
Fields terminated by X'09' --字段分隔符, 是一个制表符
(id,name,telphone) --字段名称列表
4. 执行导入命令
我们将 txt 文件和 ctl 文件都放入 c 盘根目录下,然后 Dos 命令进入 C 盘执行下面的命令:
Sqlldr userid = system/manager control='C:\import.ctl'
如果是远程数据库服务器可以这样执行:
sqlldr system/manager@22.11.97.96:1521/orclcontrol='C:\import.ctl'
其中 system/manager 是你的数据库用户名/密码,成功后出现下图:
你就会发现数据已经导进去了,如果导入不成功,就去 ctl 文件同级目录下找 log文件,看看报错信息。
5.碰到的问题
1.因为我需要主键自增所以我的 trailing nullcols 是有 5 个字段的,而数据只有4列,这样会造成一个问题,就是数据匹配的字段不对,解决方法就是在excel 最前面一列加上一列空的数据或者无效数据,让这列和主键自增的匹配,然后数据就能够正确匹配上了。
2.执行sqlldr命令的时候可能出现 协议适配器错误,最简单的解决办法就是执行 oracle_sid=XXXX,XXXX就是你的database SID.
总结
网上也还有其他的导入方法,大家也自行搜索就好,这个导入可能也会遇到其他的问题,比如怎么跳过某列什么的,这个就不贴了。