Oracle导入excel数据方法汇总

 

 

摘要：在程序编制过程和数据汇总交换过程中，经常会碰到需要将其他人员在office办公环境下编制的文件数据内容导入oracle中的情况。 目前程序开发者经常使用的方法有如下几种：1，使用oracle提供的导入工具sql*loader；2，使用plsql developer工具；3使用其他数据转移工具做中转站。下面我们采用实例的方式分别描述几种导入方式的优缺点及其操作步骤。假设‘e:\ test.xls’文件中存在三列数据分别代表客户编号，客户名称和联系电话。其内容如下：

10001	zjaxi01	13562485246
10002	zjaxi02	13562485247
10003	zjaxi03	13562485248
10004	zjaxi04	13562485249
10005	zjaxi05	13562485250
10006	zjaxi06	13562485251
10007	zjaxi07	13562485252
10008	zjaxi08	13562485253
10009	zjaxi09	13562485254
10010	zjaxi10	13562485255
10011	zjaxi11	13562485256
10012	zjaxi12	13562485257
10013	zjaxi13	13562485258
10014	zjaxi14	13562485259

使用sql *loader

SQL*LOADER是oracle的数据加载工具，在NT下sql*loader的 命令为SQLLDR，在UNIX下一般为SQLLDR/SQLLOAD。通常用来将操作系统文件迁移到oracle数据库中。它是大型数据仓库选择使用的 加载方法，因为它提供了最快速的途径（direct，parallel）。但这种方法要求存储数据的oracle表是已经存在的。

使用这种方法导入excel内容到oracle时，首先需要将excel文件另存为文本格式，文件类型选文本类型或者csv类型即将e:\test.xls另存为e:\test.csv。

如果oracle没有对应存储数据的表结构，则需要创建表test(id,name,telphone)。

用记事本创建sql*loader控制文件test.ctl(ctl后缀是企图证明这是一个控制文件，但实际上这个文件后缀不重要，用户可以自由选择，但文件格式一定要是文本格式)，内容如下：

Load data

Infile ‘e:\test.csv’          --数据源文件名称

Append|insert|replace       --append在表后追加，insert插入空表，replace替代原有内容

Into table test            --要导入的数据库表名称

[when id = id_memo]     --过滤条件

Fields terminated by X’09’ --字段分隔符

(id,name,telphone)        --字段名称列表

最后在命令窗口输入sqlldr命令行执行导入操作Sqlldr userid = system/manager control=’e:\test.ctl’

Sqlldr的函数关键字说明：

Userid   --oracle用户名 userid = username/password

Control --控制文件名称 control = ‘e:\insert.ctl’

Log     –-日志文件名称 log = ‘e:\insert.log’

Bad     --损坏文件名称

Data     --data file name

Discard    --discard file name

Discardmax --number of discards to allow(默认全部)

Skip       --导入时跳过的记录行数（默认0）

Load       --导入时导入的记录行数（默认全部）

Errors    --允许错误的记录行数（默认50）

使用plsql developer

PL/SQL Developer是一种专门用于开发、测试、调试和优化oracle PL/SQL存储程序单元，比如触发器，存储过程，函数和程序包等集成开发环境。

在单个文件数据不多（小于10w行），并且目的表结构已经存在的情况下可以使用plsql developer工具将excel内容直接通过简单的copy，paste操作导入oracle数据库。具体操作方式如下：

l         在plsql developer的sql window里输入select * from test for update，其中test是要导入的oracle数据表名称；

l         执行查询（通过点击按钮或者快捷键F8可以实现）；

l         点击查询结果上面的锁状按钮，使得查询到的内容处于编辑状态。

l         从excel 数据表中copy（ctrl ＋C）要导入oracle中的数据，如果是单列数据导入，可以先按plsql developer中查询结果上面的“添加（＋）”按钮，使得数据表处于添加数据状态，然后将鼠标在plsql developer的列名称上单击，最后使用快捷键ctrl ＋ v 完成数据导入操作，并单击plsql developer中的“提交（对号）”按钮提交所做的修改。

l         如果是同时导入所有列，首先选择copy所有数据，然后点增加（＋）并点击编辑那一行最前面的星号使得整行数据都处于被选择状态，最后paste即可将数据导入。

l         如果分列导入数据，则需要逐列完成，首先选择copy要导入的数据，然后然后点增加（＋）并点击编辑那一列的名称，使得整列数据都处于被选择状态，最后paste即可将数据导入。

使用PLSQL developer执行数据的导入和导出操作主要存在的问题是在大数据量情况下对中文数据可能出错，估计将接收数据表的类型定义为nvarchar2的类型会比较好一点。另外，就是这种方法需要人工更多的参与，不能达到自动导入的效果。

使用sql server中转

    这部操作执行时，需要先用sqlserver的导入导出工具将excel内容导入sqlserver数据库 中，然后使用同样的工具将sqlserver中转内容导入oracle数据库，这里需要两次的数据复制与IO操作，效率相对是比较低的。并且根据csdn 网友jkflyfox的描述，这种方式下需要将oracle文件的名称全部大写，否则会出错。