环境描述:
数据库版本: oracle Release 11.2.0.3.0版本
execl版本 :WPS Office 9.1.0.4764 -- 详细版本号 , 其实就是金山的那个WPS
需求描述:
开发提供了5个execl表格,格式为:.xlsx文件 每个表中数据量不是很大 最多的行数为:645行,开发想通过 5个表中的数据和生产库进行做数据比对,然后修改数据。
表格的数据 列共4列:(我以为用 字母表示代替列名)
唯一号 | 姓名 | 身份证号码 | 户口性质 |
a b c d
我的处理思路:
1 使用外部表进行数据导入。
但是外部表真是问题多多啊,有遇到了奇葩问题了, 为了节约读者的观赏性:
我把内容分为两部分 1 代替外部表的办法 2 第二次遇到外部表的奇葩问题。
那部分感兴趣看那部分,不罗嗦。
1 代替外部表的办法
1. 创建要导入的表的表结构:
比如我的上面所述的表,我将表起名为 data1 列有 a b c d
create table data1
(
a number(10),
b varchar2(30),
c number(20),
d number(5)
);
2. 表创建好以后可以利用 PLSQL工具直接将execl表中的数据直接粘贴进去!
在execl表中将所有列(除了标题行)全部选中,在创建的表中执行:
select * from data1 for update; 打开锁 选中 A B C D 四列如下图:可以直接粘贴。
这样就可以完全复制进去了。
需要注意的是 不要这样进行粘贴,会发现少一列。
这样的话 第一列的id 不见了,原因是: 第一列会贴在边框上。
总结:
这个办法,确实挺实用的,但是限制数据量小的表使用。
这样还节省了execl文本格式的转换工作,外部表分割时候定义的分割符的问题。
比如,假如列中自带有, 那么数据将出现问题,就先说到这里,可以实际操作一下,评判出那个更有效率更准确。
2 第二次遇到外部表的奇葩问题。
表的列:
唯一号 | 姓名 | 身份证号码 | 户口性质 |
a b c d
遇到的问题描述:
创建外部表时候 列的类型创建为: A number B varchar2 c number d number 的表创建了查询却没有数据。
如果我将表的列 创建成为 : A varchar2 B varchar2 C varchar2 D varchar2 就可以查到数据 但是使用where条件查询过滤数据的时候不能用。
操作步骤:
1 将execl文件格式化转换为.csv格式,然后上次到服务器的目录中。
文件上传到了/home/oralce/ 数据内容格式为: 注(最后一列将字符转为数字了,开发要求的)
格式为:
1 本市城镇职工
2 外埠城镇职工
3 本市农村劳动力
4 外埠农村劳动力
5 本市农民工
6 外埠农民工
2 创建外部表:
创建逻辑目录并授权:
conn / as sysdba
CREATE DIRECTORY TESTDIR AS '/home/oracle';
GRANT READ,WRITE ON DIRECTORY TESTDIR TO xcy;
CREATE TABLE test_date
(a number(10),
b varchar2(40),
c number(20) ,
d number(20)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY TESTDIR
ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
(a char,
b char,
c char,
d char
))
LOCATION('data1.csv'))
reject limit 40000;
如果 数值类型都为 vahrcahr2 的就可以
但是这样不能用 数值类型,查询不出结果目的没有达到。。
奇怪吧。。这个问题我今天想了一天,更换用户,sys用户 都实过结果一样的问题, 最后我也没能解决。大家有知道是什么原因的请留言给我。
本文转自ICT时空 dbasdk博客,原文链接:将execl文件数据导入到数据库中(代替外部表的办法) ,如需转载请自行联系原博主。