预计阅读时间:11分钟
同事问了个问题,
如何将excel中的数据,导入Oralce?
这种数据导入的需求,可能是我们比较常见的,实现的方法可能有很多。
最简单的方法,可能就是使用PLSQL Developer,直接进行复制和粘贴操作,如下所示,首先执行select ... for update,拷贝excel数据(删除无关列),选中表中所有列,直接粘贴,确认无误,提交,
但这有一个前提,就是数据量不能太大,否则工具会卡,有人说量级大约10万以内,没有亲测,各位有兴趣,可以试试极限。
另外,就是有些弊端,例如excel中拷贝数据的顺序,必须和PLSQL Developer检索出来的顺序一致。
另一种方法,就是Oracle自带的SQL Developer也可以执行这种操作,工具不同而已,不再测试。
其实无论是PLSQL Developer,还是SQL Developer,之所以能导入excel,其实背后封装的,就是sqlldr(SQL*Loader)这个工具,话句话说,直接使用sqlldr命令行,也可以实现excel导入的操作。
SQL*Loader是一个Oracle工具,能够将数据从外部数据文件装载到数据库中。他必须包含一个控制文件,可以说,控制文件是SQL*Loader的中枢核心,控制文件能够控制外部数据文件中的数据如何映射到Oracle的表和列。SQL*Loader能够接收多种不同格式的数据文件。文件可以存储在磁盘或磁带上,或记录本身可以被嵌套到控制文件中。记录格式可以是定长的或变长的,定长记录是指这样的记录:每条记录具有相同的固定长度,并且每条记录中的数据域也具有相同的固定长度、数据类型和位置。
使用SQL*Loader导入excel,必须另存为txt或者csv,这是一些限制。
SQL*Loader的优点诸如,
1. 可将sqlldr导入命令写入bat、shell脚本中,自动化执行批量处理。
2. 导入过程提供了各种参数,可以进行各种粒度的控制。
3. 无需在Oracle服务器上执行,可以在任何其他服务器,只要能用tns连接。
关于SQL*Loader更详细的说明可参考《Utilities》,整个第二部分,都会介绍SQL*Loader,在线版本链接,
https://docs.oracle.com/cd/E11882_01/server.112/e22490/toc.htm
对于sqlldr的使用,简单来讲,他需要两个东东,一个是要导入的数据文件,一个是控制文件(.ctl),他用来说明数据文件中的数据和表的映射关系,然后使用sqlldr命令行,执行导入操作。
无论是控制文件,还是命令行指令,都有非常丰富的参数,提供各种粒度的控制,
Command-Line Parameters
https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_params.htm#SUTIL1018
Control File Reference
https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_control_file.htm#SUTIL005
光说不练假把式,我们做一个excel导入的测试。首先,我们创建测试表,
create table excel(
id number,
name varchar2(1)
);
其次,创建测试excel文件,说是excel,其实要求的是csv,关于excel和csv,https://www.guru99.com/excel-vs-csv.html详细进行了介绍,中文版翻译如下,
简单来讲,csv文件其实是逗号分隔的文本文件,但也可以是常规的单元格,如下所示,excel有两列,三行数据,一列对应于ID,二列对应于NAME,
控制文件excel.ctl内容,其中INFILE表示要导入的数据文件,BADFILE记录导入失败的数据,DISCARDFILE记录丢弃的数据,
Load DATA
INFILE '/opt/app/excel/excel.csv'
BADFILE '/opt/app/excel/output.bad'
DISCARDFILE '/opt/app/excel/output.dsc'
into table excel
fields terminated by ','
(id,name)
执行导入指令,主要的参数就是control,指定控制文件的路径,log指定日志路径,
sqlldr user/password@db control=/opt/app/excel/excel.ctl
rows=10000 bindsize=8192 readsize=8192 errors=999999
log=/opt/app/excel/output.log
但是执行之后,报错了,提示name字段定义长度1,实际长度2,这就很奇怪了,明明excel中name写的就是'a'、'b'、'c',多了什么字符?
为了验证,临时将字段name长度改为varchar2(10),重新执行sqlldr,插入正常,看下表中存储的字段值,长度确实为2,多的字符ASCII值是13,10代表换行符,13代表回车,
SQL> SELECT NAME, length(NAME), DUMP(NAME) FROM excel_x;
NAM LENGTH(NAME) DUMP(NAME)
--- ------------ ------------
a 2 Typ=1 Len=2: 97,13
b 2 Typ=1 Len=2: 98,13
c 2 Typ=1 Len=2: 99,13
Load DATA
INFILE '/opt/app/excel/excel.csv'
BADFILE '/opt/app/excel/output.bad'
DISCARDFILE '/opt/app/excel/output.dsc'
into table excel
fields terminated by ','
(id,name 'trim(replace(:name,chr(13),chr(32)))')
此时再次执行,正常插入,log日志记录如下,
SQL*Loader: Release 9.2.0.7.0 - Production on Wed Aug 8 03:15:05 2018
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Control File: /opt/app/excel/excel.ctl
Data File: /opt/app/excel/excel.csv
Bad File: /opt/app/excel/output.bad
Discard File: /opt/app/excel/output.dsc
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 999999
Bind array: 10000 rows, maximum of 8192 bytes
Continuation: none specified
Path used: Conventional
Table EXCEL, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
--------------- ---------- ----- ---- ---- ---------------------
ID FIRST * , CHARACTER
NAME NEXT * , CHARACTER
SQL string for column : 'trim(replace(:name,chr(13),chr(32)))'
Table EXCEL:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 7740 bytes(15 rows)
Read buffer bytes: 8192
Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed Aug 08 03:15:05 2018
Run ended on Wed Aug 08 03:15:05 2018
Elapsed time was: 00:00:00.07
CPU time was: 00:00:00.02
有些细节问题,例如控制文件,开始用了LOAD,如果导入表存在数据,执行sqlldr,会报错,提示需要表空,
SQL*Loader-601: For INSERT option, table must be empty.
Error on table EXCEL
可以使用TRUNCATE、APPEND等关键字,避免此问题,类似的参数控制,还有不少,有需求可以尝试。
总体来讲,将excel导入表,方法有几种,
1. PLSQL Developer导入。
2. SQL Developer导入。
3. sqlldr导入。
还有其他方法,例如外部表、写程序读excel导入,但是还是要根据自己的需求,来选择合适的,例如导入数据量很小,就可以选择PLSQL Developer,如果数据量大,则可以使用SQL Developer导入,又或者需要一些控制,例如输出日志、定义传输buffer,就可以使用sqlldr工具,“没有最好的方案,只有最合适的”,这句话用在这,没毛病。
联系客服