oracle导出excel 方法总结

oracle导出excel

一、oracle导出excel

 

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

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

 

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

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

我做了一点修改，如下所示：

 

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

示例如下:
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 dev和excel中的列可以相互复制。

 

方法二：最专业的方法，用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，执行就可以了！！

 

先说一个简单的方法：在sqlplus中查询出数据，然后选择行项右键，有个复制到Excel，ok！

1、操作系统：windows 2000 Server，机器内存128M
　　2、数据库：　Oracle 8i R2 (8.1.6) for NT 企业版
　　3、安装路径：C:\ORACLE
　　
　　实现步骤：
　　
　　1、连入SQL*Plus
　　以system/manager用户登录，
　　SQL> conn system/manager
　　
　　创建新的用户：如user1/pass1，赋予connect,resource权限。
　　SQL> grant connect,resource to user1 identified by pass1;
　　
　　Oracle数据库的实例名，本例：oradb
　　Oracle用户名，本例：user1
　　
　　SQL> conn user1/pass1
　　SQL> create table test(a number,b char(10));
　　SQL> insert into test values(1,'一');
　　SQL> insert into test values(2,'二');
　　SQL> insert into test values(3,'三');
　　SQL> commit;
　　SQL> select * from test;
　　
　　　　　　A B
　　---------- ----------
　　　　　　1 一
　　　　　　2 二
　　　　　　3 三
　　
　　确认Oracle实例、TNSListener服务都已启动成功
　　
　　2、配置ODBC：开始→设置→控制面板→治理工具→数据源(ODBC)→
　　　
　　
　　添加一个系统DSN：
　　数据源名称（自己设，好记就行如：aaa）
　　服务名称（Oracle主机字符串：oradb）
　　用户名称（Oracle用户名：如user1）
　　
　　tnsnames.ora文件中
　　ORADB =
　　 (DESCRIPTION =
　　　 (ADDRESS_LIST =
　　　　 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.1)(PORT = 1521))
　　　 )
　　　 (CONNECT_DATA =
　　　　 (SERVICE_NAME = oradb)
　　　 )
　　 )
　　
　　
　　3、打开MicroSoft Excel 2000
　　
　　4、数据(D)→获取外部数据(D)→新建数据库查询(N)→
　　　
　　5、选择数据源：
　　　
　　
　　　
　　6、输入user1用户的密码(pass1)→OK→
　　　
　　7、选择要导入的表、列→下一步→