将execl文件数据导入到数据库中(代替外部表的办法)

环境描述:

数据库版本: 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 四列如下图:可以直接粘贴。

将execl文件数据导入到数据库中(代替外部表的办法)

这样就可以完全复制进去了。

将execl文件数据导入到数据库中(代替外部表的办法)

需要注意的是 不要这样进行粘贴,会发现少一列。

将execl文件数据导入到数据库中(代替外部表的办法)

这样的话 第一列的id 不见了,原因是: 第一列会贴在边框上。

将execl文件数据导入到数据库中(代替外部表的办法)

总结:

这个办法,确实挺实用的,但是限制数据量小的表使用。

这样还节省了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 外埠农民工

将execl文件数据导入到数据库中(代替外部表的办法)

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;
将execl文件数据导入到数据库中(代替外部表的办法)

如果 数值类型都为 vahrcahr2 的就可以

将execl文件数据导入到数据库中(代替外部表的办法)

但是这样不能用 数值类型,查询不出结果目的没有达到。。

将execl文件数据导入到数据库中(代替外部表的办法)

奇怪吧。。这个问题我今天想了一天,更换用户,sys用户 都实过结果一样的问题, 最后我也没能解决。大家有知道是什么原因的请留言给我。



本文转自ICT时空 dbasdk博客,原文链接:将execl文件数据导入到数据库中(代替外部表的办法) ,如需转载请自行联系原博主。

上一篇:memcached +php环境配置和分析


下一篇:GitHub 将开源内部负载均衡软件