大家好！今天想出了一个可以代替外部表的小办法，虽然很简单，但是确实挺实用的，这个仅限于表中数据量小一些的时候使用。
今天被外部表折腾了半天，没有结果，开发还一直再催要数据，汗。

不发牢骚了，开始说明：

环境描述：

数据库版本： 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用户都实过结果一样的问题，最后我也没能解决。大家有知道是什么原因的请留言给我。

ITPub博客

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

最新文章