oracle导出excel 方法总结

来源:互联网 发布:数据库关系模型示例 编辑:程序博客网 时间:2024/03/29 04:28

oracle导出excel

一、oracle导出excel

 

方法一:最简单的方法---用工具plsql dev

执行File =>newReport Window 。在sql标签中写入需要的sql,点击执行或按快捷键F8,会先吃出查询结果。在右侧工具栏,可以选择按钮另存为htmlcopy as htmlexportresults,其中export results按钮中就可以导出excel文件、csv文件、tsv文件、xml文件。

 

方法二:最专业的方法---用原始工具sqlplus

原文参见:http://www.eygle.com/archives/2005/04/eoasqlplusieaae.html

我做了一点修改,如下所示:

 

1.main.sql
用以设置环境,调用具体功能脚本
2.
功能脚本-get_tables.sql
为实现具体功能之脚本
通过这样两个脚本可以避免spool中的冗余信息,参考:
如何去除SQLPLUSSPOOL的冗余信息 

示例如下:
1.main.sql
脚本:

[oracle@jumper utl_file]$ more main.sql


set linesize 200 


set term off verify off feedback off pagesize 999 


set markup html on entmap ON spool on preformat off


spool d:/tables.xls


@d:/get_tables.sql


spool off


exit



2.get_tables.sql
脚本:

[oracle@jumper utl_file]$ more get_tables.sql 


select owner,table_name,tablespace_name,blocks,last_analyzed


from all_tables order by 1,2;



3.
执行并获得输出:

[oracle@jumper utl_file]$ sqlplus "/ as sysdba" @d:/main.sql




SQL*Plus: Release 9.2.0.4.0 - Production on Mon Apr 25 10:30:11 2005




Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.






Connected to:


Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production


With the Partitioning option


JServer Release 9.2.0.4.0 - Production




Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production


With the Partitioning option


JServer Release 9.2.0.4.0 - Production


[oracle@jumper utl_file]$ ls -l tables.xls 


-rw-r--r--    1 oracle   dba         69539 Apr 25 10:30 tables.xls


[oracle@jumper utl_file]$ 



此处输出为xls文件,通过下图我们可以看到输出效果:

 

main.sql脚本中的,spooltables.xls更改为spool tables.htm,我们可以获得htm格式输出,效果如下图:

 

方法三:最悲剧的方法,之所以称为悲剧,是因为这个方法其实很简单,但是你可能没有注意。

打开plsql dev工具,执行file=>newsql window ,输入sql,点击工具栏执行按钮或按快捷键F8。会显示出结果集。

点击结果集的左上方,可全部选中结果集,然后右键=>copy,直接粘贴到excel文件中就可以了!

 

方法四:最努力的方法,这种方法稍微有点麻烦,但不用写脚本,也不用psql dev工具,只用sql plus就可以了。

sqlplus中,执行想要的sql,把结果集copy到文本文件中(或者直接用spool命令直接输入到文本文件中),把不必要的字符、空格替换成逗号",",然后另存为csv文件,最后在用excel另存为exl文件就可以了。

 

 

二、把excel文件数据导入到oracle的某个表中。

 

方法一:最省时的方法,需要借助plsql dev工具。

        a.复制整个工作簿中的数据到某个表中。

           点击excel工作区左上角,全部选中数据,ctrl+c 复制整个工作簿中的数据。在plsql dev中,编辑表格表的数据,点击数据展示区左上角,直接粘贴就可以了(注意excel的第一列应该保持为空,如果不为空可以增加一空列,不然你复制的数据就会少一列的)!如下图

所示:

 

      b.复制某列的数据。

        这个很容易,选中excel某一列的数据,复制,选中oracle某个表的某一列,直接粘贴就可以了。plsql devexcel中的列可以相互复制。

 

方法二:最专业的方法,用sql loader

 

   原文地址:http://daniel-wuz.javaeye.com/blog/198112

 

 

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 X’09’ --
字段分隔符 
(id,name,telphone)        --
字段名称列表

 

方法三:最悲剧的方法,创建oracle外部表,为了把excel中的数据导入到数据中而去建立外部表,大题小做了!

    excel文件另存为csv文件a.csv,然后创建一个外部表t,数据指向a.csv。然后根据外部表创建一个普通的表:

    create table a asselect  * from t ,这样就可以把最初的excel文件导入到oracle中的表了。

 

方法四:最古典的方法,拼接sql语句。如果你excel熟的话,这种方法也不错。

    excel中,把数据拼接成如下sql语句:

   insert into empvalues('1','2','3');

   insert into empvalues('4','5','6');

   insert into empvalues('7','8','9');

 

copy 出以上sql,执行就可以了!!

 

0 0