代码改变世界

快速将excel数据保存到Oracle数据库中

2010-05-18 22:44  白玉京  阅读(1606)  评论(0)  编辑  收藏  举报

 

     我们在工作中,也许会碰到以下情况,客户或者同事发来需要调查的数据,并不是dmp文件,而是excel文件,此时通常是一张表,少量几条记录.最近我恰好碰到了这种情况,所以做了些调查,不敢藏私,拿出来跟大家分享下. 一般的做法,只能对着excel拼写sql语句.如果列数少还好说,表的列数一多的话,这方法既费时又费力.有什么办法可以快捷地导入数据呢?以下的方案可以供大家参考:

Sql*Loader

操作步骤如下:

  1. 录入excel测试表格，test.xls。
  2. 另存为.csv格式
  3. 创建sql*loader控制文件test.ctl，内容如下：
    Load data
    Infile 'c:\test.csv'
    insert Into table test Fields terminated by ','(column1,column2,column3,column4,column5)    
    需要将数据文件拷贝到对应位置
  4. 到数据库中建立对应的测试表test
    create table test (
    column1 Varchar2(10),
    column2 Varchar2(10),
    column3 Varchar2(10),
    column4 Varchar2(10),
    column5 Varchar2(10)
    )
  5. 执行导入命令
    Sqlldr userid = system/manager control='C:\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）

ctl文件内容说明：
Load data
Infile 'e:\test.csv'  --数据源文件名称
Append|insert|replace --append在表后追加insert插入空表replace替代原有内容
Into table test      --要导入的数据库表名称
[when id = id_memo]    --过滤条件
Fields terminated by',' --字段分隔符
(id,name,telphone)    --字段名称列表

说明:

     这种方法的好处是sql*loader是oracle自带软件,无需安装额外的软件.缺点是操作比较麻烦点,而且如果表列数很多的话,输入字段名称列表会比较麻烦,适合导入大量数据.

PL Sql Developer  方法一

  1. 打开excel给需要拷贝的数据增加一个空的起始列 , 选中所要更新的数据，ctr+c  
  2. 打开PL SQl Developer
    1. 输入select * from tab_name for update,按F8运行
    2. 打开锁,此时记录集处于可以编辑的状态,
    3. 点击选中整行, ctrl+v,commit,大功告成

 

说明:这种方法其实是利用PL/SQL developer 对于记录集可以进行整行和多行复制粘贴的特性,toad似乎没有这样快速的办法;

  • 在excel表格的列新追加空列的原因是在PL/SQL Developer选中整行进行粘贴时,前面会有个列数的列,所以要新增加一列,否则会造成列的错位.
  • 这种方法处理少量数据的时候最合适,快捷方便,但是: excel的列必须与表中的列完全匹配

PL Sql Developer  方法二

  使用PL SQL Developerd的text importer,这种方法简单方便,推荐此种方法

 

方法很简单:

  1. 点击tools->text importer
  2. 在Data from txtfile 页面选择要导入的文件,在Data to Oracle 界面选择要导入的库和表即可
  3. 此时可以看到导入结果预览,还可以查看生成的sql语句,并且对于列可以智能匹配,妙用多多.当然点击Import 就OK了

 

说明:

    这种方法简单可靠,适合导入大量的数据,我猜测这个工具其实是后台根据excel的列生成了相应的sql语句,取代人工拼写sql语句的过程,呵呵,就算生成的sql语句因为一些原因,不是很正确,也可以手工修改.这样既自动又可以手动加以控制,我比较推荐这种方法. 

  前提是要安装PL/Sql Developer,呵呵

综述:

    我这篇文章主要介绍了两种工具用于将excel导入oracle,如果在客户的服务器上等环境受限的情况,没有PL/Sql Developer的话,使用Sql*Loader是个不错的选择;PL/SQL Developer的两种使用方法可以灵活使用,前者快速方便,后者可控性更好,而且无需字段匹配.