将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 四列如下图:可以直接粘贴。


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


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


这样的话 第一列的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文件数据导入到数据库中(代替外部表的办法) ,如需转载请自行联系原博主。

相关文章
|
22天前
|
存储 关系型数据库 MySQL
|
24天前
|
SQL XML 数据库
后端数据库开发高级之通过在xml文件中映射实现动态SQL
后端数据库开发高级之通过在xml文件中映射实现动态SQL
22 3
|
24天前
|
SQL XML Java
后端数据库开发JDBC编程Mybatis之用基于XML文件的方式映射SQL语句实操
后端数据库开发JDBC编程Mybatis之用基于XML文件的方式映射SQL语句实操
31 3
|
13天前
|
数据库
提取本地路径Jpg或者mp4文件的名字,导入mp4的名字,并导入数据库的源码
提取本地路径Jpg或者mp4文件的名字,导入mp4的名字,并导入数据库的源码
|
13天前
|
数据库
获取本地某文件中的以.jpg文件的名字,并导入数据库的测试代码
获取本地某文件中的以.jpg文件的名字,并导入数据库的测试代码
|
12天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
9天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
978 6
Mysql 数据库主从复制
|
10天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。
|
1天前
|
消息中间件 DataWorks 关系型数据库
DataWorks产品使用合集之遇到无法连接到本地 MySQL 数据库的问题,该如何解决?
摘要:DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
2天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
18 3