前言
最近有一个将 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_nameVARCHAR2(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 datainfile '新增的分包企业.txt'append into table pm_construction_teamfields 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 dataInfile '新增的分包企业.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.
总结
网上也还有其他的导入方法,大家也自行搜索就好,这个导入可能也会遇到其他的问题,比如怎么跳过某列什么的,这个就不贴了。