關(guān)于oracle導(dǎo)出excel和excel導(dǎo)入oracle的經(jīng)驗(yàn)總結(jié)

Sky-Cool 2011-12-26

展開全文

關(guān)于oracle導(dǎo)出excel和excel導(dǎo)入oracle的經(jīng)驗(yàn)總結(jié)

一、oracle導(dǎo)出excel

方法一：最簡單的方法---用工具plsql dev

執(zhí)行File =>new Report Window 。在sql標(biāo)簽中寫入需要的sql，點(diǎn)擊執(zhí)行或按快捷鍵F8，會先吃出查詢結(jié)果。在右側(cè)工具欄，可以選擇按鈕另存為html、copy as html、export results，其中export results按鈕中就可以導(dǎo)出excel文件、csv文件、tsv文件、xml文件。

方法二：最專業(yè)的方法---用原始工具sqlplus

原文參見：http://www./archives/2005/04/eoasqlplusieaae.html

我做了一點(diǎn)修改，如下所示：

1.main.sql
用以設(shè)置環(huán)境，調(diào)用具體功能腳本
2.功能腳本-get_tables.sql
為實(shí)現(xiàn)具體功能之腳本
通過這樣兩個(gè)腳本可以避免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.執(zhí)行并獲得輸出:

[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腳本中的，spool tables.xls更改為spool tables.htm,我們可以獲得htm格式輸出，效果如下圖:

html文件

方法三：最悲劇的方法，之所以稱為悲劇，是因?yàn)檫@個(gè)方法其實(shí)很簡單，但是你可能沒有注意。

打開plsql dev工具，執(zhí)行file=>new sql window ，輸入sql，點(diǎn)擊工具欄“執(zhí)行”按鈕或按快捷鍵F8。會顯示出結(jié)果集。

點(diǎn)擊結(jié)果集的左上方，可全部選中結(jié)果集，然后右鍵=>copy，直接粘貼到excel文件中就可以了！

方法四：最努力的方法，這種方法稍微有點(diǎn)麻煩，但不用寫腳本，也不用psql dev工具,只用sql plus就可以了。

在sqlplus中，執(zhí)行想要的sql，把結(jié)果集copy到文本文件中（或者直接用spool命令直接輸入到文本文件中），把不必要的字符、空格替換成逗號","，然后另存為csv文件，最后在用excel另存為exl文件就可以了。

二、把excel文件數(shù)據(jù)導(dǎo)入到oracle的某個(gè)表中。

方法一：最省時(shí)的方法，需要借助plsql dev工具。

a.復(fù)制整個(gè)工作簿中的數(shù)據(jù)到某個(gè)表中。

點(diǎn)擊excel工作區(qū)左上角，全部選中數(shù)據(jù)，ctrl+c 復(fù)制整個(gè)工作簿中的數(shù)據(jù)。在plsql dev中，編輯表格表的數(shù)據(jù)，點(diǎn)擊數(shù)據(jù)展示區(qū)左上角，直接粘貼就可以了（注意excel的第一列應(yīng)該保持為空，如果不為空可以增加一空列，不然你復(fù)制的數(shù)據(jù)就會少一列的）！如下圖

所示：

b.復(fù)制某列的數(shù)據(jù)。

這個(gè)很容易，選中excel某一列的數(shù)據(jù)，復(fù)制，選中oracle某個(gè)表的某一列，直接粘貼就可以了。plsql dev和excel中的列可以相互復(fù)制。

方法二：最專業(yè)的方法，用sql loader。

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

1.錄入excel 測試表格，test.xls。
2.另存為.csv格式
3.創(chuàng)建sql*loader控制文件test.ctl，內(nèi)容如下：
Load data
Infile 'c:/test.csv'
insert Into table test Fields terminated by ','(column1,column2,column3,column4,column5)

需要將數(shù)據(jù)文件拷貝到對應(yīng)位置

4.到數(shù)據(jù)庫中建立對應(yīng)的測試表test

create table test (
column1 Varchar2(10),
column2 Varchar2(10),
column3 Varchar2(10),
column4 Varchar2(10),
column5 Varchar2(10)
)

5.執(zhí)行導(dǎo)入命令
Sqlldr userid = system/manager control='C:/test.ctl'

導(dǎo)入成功!

附：
Sqlldr的函數(shù)關(guān)鍵字說明：
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(默認(rèn)全部)
Skip       --導(dǎo)入時(shí)跳過的記錄行數(shù)（默認(rèn)0）
Load       --導(dǎo)入時(shí)導(dǎo)入的記錄行數(shù)（默認(rèn)全部）
Errors    --允許錯(cuò)誤的記錄行數(shù)（默認(rèn)50）

ctl文件內(nèi)容說明：
Load data
Infile ‘e:/test.csv’          --數(shù)據(jù)源文件名稱
Append|insert|replace       --append在表后追加，insert插入空表，replace替代原有內(nèi)容
Into table test            --要導(dǎo)入的數(shù)據(jù)庫表名稱
[when id = id_memo]     --過濾條件
Fields terminated by X’09’ --字段分隔符
(id,name,telphone)        --字段名稱列表

方法三：最悲劇的方法，創(chuàng)建oracle外部表，為了把excel中的數(shù)據(jù)導(dǎo)入到數(shù)據(jù)中而去建立外部表，大題小做了！

將excel文件另存為csv文件a.csv，然后創(chuàng)建一個(gè)外部表t，數(shù)據(jù)指向a.csv。然后根據(jù)外部表創(chuàng)建一個(gè)普通的表：

create table a as select * from t ，這樣就可以把最初的excel文件導(dǎo)入到oracle中的表了。

方法四：最古典的方法，拼接sql語句。如果你excel熟的話，這種方法也不錯(cuò)。

在excel中，把數(shù)據(jù)拼接成如下sql語句：

insert into emp values('1','2','3');

insert into emp values('4','5','6');

insert into emp values('7','8','9');

copy 出以上sql，執(zhí)行就可以了??！

本站是提供個(gè)人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： Sky-Cool > 《數(shù)據(jù)庫》

舉報(bào)/認(rèn)領(lǐng)