excel数据导入Oracle的需求

预计阅读时间：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./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

我们不说为什么excel会多一个回车，咱们看如何解决？修改控制文件，name字段增加两个函数，一个是replace，将chr(13)回车替换为chr(32)，即空格，然后使用trim，删除空格，保证字段长度，

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工具，“没有最好的方案，只有最合适的”，这句话用在这，没毛病。