技术分享:Oracle PLSQL读取(解析)Excel文档
来源:互联网 发布:安卓抓包软件 编辑:程序博客网 时间:2024/06/05 18:58
Excel文档的数据如何读取到Oracle数据库一直是值得深究的问题。
目前相信大部分程序员都是直接用工具将Excel的数据读取到Oracle数据库的。例如Toad,PL/SQL Developer的Excel数据导入功能。
也有一部分程序开发的是:先将Excel转换为一个逗号分隔符等的文本文件(CSV等),然后写代码通过特定的符号(TAB符号或者逗号等)将数据拆分并且识别。
其实上面的读取Excel的办法都不是最好用的办法。举个例子,用Toad等工具来做,程序开发工程师来做是可以的,但是不可以将这个工作交给普通用户做(当用户想导入数据的时候)。如果用文本来做,先要做一个转换,对于用户来说无疑是多做了一个动作,操作不方便。
综上所述,其实最方便的还是:如何在Oracle数据库用PL/SQL直接读取Excel的内容,这才是最好的做法。
在PL/SQL直接解析并读取Excel,有一个蛮不错的办法,就是用Java的POI组件。确实是不错,但是要先导入POI的Java类,然后再在Oracle里面写一段Pkg的class类解析Excel。是可行的。我也研究过,发现有一些限制(可能是和POI的版本太低有关系)。
这里提出一个更加好的办法,就是用Oracle自带的utl_raw 包(二进制处理包)和dbms_xmldom包,将Excel文件对应的Blob大文本字段进行解析,最后再将解析出来的内容用管道表函数输出一个虚拟的表格。这样子就是很直观了,直接将一个Excel文档解析为一个表格!
可能大家比较关心解析的效率。所以针对效率方面,经过测试,还是非常不错的。下面有专门测试解析效率的主题。
另外一个比较关心的问题:Excel里面可以有很多公式列,那对于公式列读取出来的结果是什么?另外,一些特殊的格式栏位是否可以正确识别?经过测试,这些都可以得到正确的解析。例如公式列,解析的是公式计算的结果。
先简单测试一下实现的效果。
现在有这么一个Excel文档:XLS文件导入样本.xls,想将它导入到Oracle数据库中。
然后将它上传到服务器的某个文件夹。注意,这个文件路径(/data/uat/apps/apps_st/appl/attchment/12.0.0/BATCH_UPLOAD_TEMP/)必须是在all_directories有定义的。否则用PLSQL无法直接读取文件。值得一提的是,这个步骤并不是必须的,你也可以将Excel文件上传到Blob大字段中,然后再直接读取。常见的是FND_LOBS表的FILE_DATA字段。
最后一步,用下面的函数就可以直接读取出Excel文本的内容了(注意输入的参数):
SELECT* FROM TABLE(XYG_PUB_DATA_UPLOAD_PKG.CONVER_EXCEL_TO_TAB(XYG_ALD_FILE_PKG.CONVERT_FILE_BLOB('XYG_BATFILE','XLS文件导入样本.xls'),'',1))
必须要说明的是,栏位BATCH_CODE=工作表的名称。
如果有多个工作表,那BATCH_CODE是不同的。
附上Blob字段的Excel的读取方法:
SELECT * FROM TABLE(XYG_PUB_DATA_UPLOAD_PKG.CONVER_EXCEL_TO_TAB((SELECT FILE_DATA FROM FND_LOBS WHERE FILE_ID = 81171130),NULL,1));
注意和说明
如果是用Excel直接导入,需要注意的地方:
1 目前只支持30个栏位导入!应该是足够了!另外,管道表函数的对象的每个栏位都是Varchar2类型,内容最长4000字节。
2 由于一个Excel可能会有多个工作表,所以,在导入的时候,必须要指定导入的Excel的工作表页签的名称。
3 完全支持直接日期栏位的导入!日期栏位的格式也没限制。非常好!
4 对于数字的类型,由于系统自动转换为浮点型,为了转换的时候不出异常,所以精度不可以超过15位。
5 对于公式列,它也完美支持!导入的结果就是公式的计算结果。举个例子,一个栏位引用另外一个日期的栏位的,那导入的就是日期!
6 如果用户在打开Excel(未关闭文档的情况下),也可以用FND的标准加载功能直接加载。不过必须要注意导入之前先保存。
7 目前已经完美支持xls和xlsx文档的导入,系统会自动对导入的文档进行识别,然后分别调用不同的代码,将文档的内容解析出来。
但是,我觉得用xls是最好的,因为并不是所有的电脑都安装了2003以上的版本,用xls是最大兼容性的。
导入效率的测试
效率测试:
1 解析文档:DG订单排产顺序20150423.xls
文档大小:1.5mb,数据量:4600行。栏位数:27,其中有某些字段的内容比较多,例如物料编码描述等
解析时间:36秒。
2 解析文档:科目为差旅费.xls
文档大小:3mb,数据量:9000行。栏位数:20,其中有某些字段的内容比较多,例如2个科目的描述字段等
解析时间:65秒
3 解析文档:20150212用户职责明细导出.xls
文档大小:577kb,数据量:3000行,栏位数:11。字段内容比较少。
解析时间:14秒
4 解析文档:数据收集模板(深圳汽玻夹层第二批)匡国平2011-11-08.xls
文档大小:347kb,数据量:1572行,栏位数:17。字段的内容都不多。以数字为主。
解析时间:7秒
从上面发现一个规律,基本上解析时间和文档的大小成正比。
平均1mb的文件的解析时间要30秒左右。
应该还是可以接受的,因为导入的数据量一般不会很多!
需要注意的是,这个数据量不单纯是指记录行数,也包括记录的内容的多少。例如有某些字段的内容如果很多的话,就算行数少,数据量也很大!
完整文档:http://wenku.baidu.com/view/bff55ea35901020207409cb2
- 技术分享:Oracle PLSQL读取(解析)Excel文档
- 文档解析之Excel策划文档的读取
- (三) POI 解析Excel 之 读取Excel 文档
- Oracle PLSQL文档资料
- Oracle PLSQL文档资料
- ORACLE PLSQL解析JSON
- java 读取Excel文档
- PIO读取excel文档
- PHP读取excel文档
- 技术文档分享
- xlrd解析excel文档
- POI解析Excel文档
- Excel文件读取解析
- 读取excel,并解析
- java 解析,读取excel
- ExceL文档2.上传解析Excel文档
- jxl API 读取Excel文档
- JS读取Excel文档记录
- Leetcode: Length of Last Word
- Leetcode: Longest Common Prefix
- 基于Redis实现分布式消息队列(3)
- EXCEL VBA知识总结
- oracle instantclient(即时客户端安装) 中文乱码
- 技术分享:Oracle PLSQL读取(解析)Excel文档
- CTS测试
- PyCharm 4.0.6 注册码
- Hibernate学习笔记(1)-----------jar包引入及入门
- 11个让你吃惊的 Linux 终端命令
- source insight 查看linux源码
- Docker
- linux编程所用的一些工具
- Libgdx 高级渲染之 高斯模糊