- 相關(guān)推薦
ORACLE與excel的數(shù)據(jù)互傳方法
眾所周知，MicrosoftExcel能很直觀而方便地進(jìn)行數(shù)據(jù)輸入，統(tǒng)計(jì)，生成圖表，但它的數(shù)據(jù)管理能力有限，對大量的數(shù)據(jù)查詢能力不足，如果利用它數(shù)據(jù)計(jì)算方面的優(yōu)點(diǎn)和大型數(shù)據(jù)庫ORACLE的數(shù)據(jù)查詢優(yōu)點(diǎn)，可以設(shè)計(jì)出功能強(qiáng)大的數(shù)據(jù)處理系統(tǒng)。
假設(shè)與Excel數(shù)據(jù)文件相對應(yīng)的職工情況表EMP已在ORAC LE系統(tǒng)中創(chuàng)建，此表中各字段按照順序分別是職工代號( Emp—No)，職工姓名(Ename)，工資(Salary)，雇用時(shí)間(Hiredate)。
1、將Excel中的數(shù)據(jù)裝入ORACLE數(shù)據(jù)庫
通過ORACLE數(shù)據(jù)庫系統(tǒng)的高級應(yīng)用工具SQL*Loader 可以將原來的Excel中存儲的數(shù)據(jù)直接裝入ORACLE數(shù)據(jù)庫的表中。
首先，在MicrosoftExcel中完成表格數(shù)據(jù)輸入后，選擇“保存文件”命令，在出現(xiàn)對話框的“文件類型”欄中選取“格式化文本文件(空格分隔)”以形成一個(gè)標(biāo)準(zhǔn)格式化文本文件(*.PRN)或是選取“CSV(逗號分隔)” 形成一個(gè)逗號定界文件(*.CSV)。這兩者的區(qū)別是：標(biāo)準(zhǔn)格式的文本文件中每個(gè)記錄等長，數(shù)據(jù)間按原庫文件字段長度緊湊排列，字符型字段數(shù)據(jù)左對齊，數(shù)據(jù)型字段數(shù)據(jù)右對齊，不足部分用空格補(bǔ)足。逗號定界文件特點(diǎn)是各記錄可不等長，字段按其定義順序依次排列，字段間用逗號分隔，字符型字段和數(shù)據(jù)型字段的左右端空格被消去。
因?yàn)檫@兩種格式文件有不同的數(shù)據(jù)裝入方法，假如我們將職工情況EMP表存為EMP.CSV，數(shù)據(jù)變成如下形式：
120，JOE，1192.64，93/04/11
121，KERT，309.60，87/01/14
23，PETER，1500.25，84/03/12
124，SMITH，678.00，91/01/07
15，YOUNG，2390.12，80/05/03
接著，通過記事本建立以下控制文件，它包含了數(shù)據(jù)文件的名稱及其格式，文件字段如何定界，數(shù)據(jù)類型是怎樣等內(nèi)容，將該裝載控制文件保存為EMP.CTL：
LOADDATA INFILE 'C：\EMP.CSV‘BADFILE 'EMP.BAD' DISCARDFILE 'EMP.DSC' APPEND INTOTABLEEMP FILEDS TERMINATED BY "，" (Emp-No，Ename，Salary，HiredateDATA(8) "Y Y/MM/DD")
之后，雙擊SQL*Loader圖標(biāo)，進(jìn)入ORACLE7的數(shù)據(jù)裝載工具，在彈出的SQLLOADER主屏幕對話框中，分別輸入用戶登錄名，密碼，數(shù)據(jù)庫名稱和控制文件名，確認(rèn)后進(jìn)行數(shù)據(jù)裝載。這樣SQL*Loader一邊轉(zhuǎn)換、裝入數(shù)據(jù)，一邊將執(zhí)行過程中的錯(cuò)誤信息和統(tǒng)計(jì)信息存入登錄文件(LogFile)中，把ORACLE拒絕裝入的原始數(shù)據(jù)存入拒絕文件(RejectFile)。用戶可以在裝載完成后，打開登錄文件文件查看數(shù)據(jù)的裝載情況，了解因?yàn)楹畏N原因使得數(shù)據(jù)被拒絕，是因?yàn)閿?shù)據(jù)本身不符合數(shù)據(jù)庫的數(shù)表定義還是違反了完整性原則等其它原因。由此可見，數(shù)據(jù)裝載不僅快速而且安全。
特別要提到的是可以通過SQL*Loader同時(shí)將數(shù)據(jù)文件裝入多個(gè)數(shù)表。例如在Excel表格中輸入數(shù)據(jù)時(shí)，可以對應(yīng)輸入該職工參加的項(xiàng)目代號(Proj-No)，現(xiàn)在需要將這些數(shù)據(jù)分別裝入EMP表和PROJ表。數(shù)據(jù)文件按標(biāo)準(zhǔn)格式存儲如下所示，其中第5，6，7列均為該職工參加的項(xiàng)目代號：
120JOE1192.6493/04/11101112
121KERT309.6087/01/142812
23PETER1500.2584/03/124017
124SMITH678.0091/01/07102021
15YOUNG2390.1280/05/03432617
編寫的裝載數(shù)據(jù)控制文件可以是：
LOADDATA INFILE'C：\EMP.PRN' BADFILE'EMP.BAD' DISCARDFILE 'EMP.DSC' APPEND INTOTABLE EMP(Emp-NoPOSITION(01：05)INTEGER EXTERNAL，EnamePOSITION(06：15)CHAR， SalaryPOSITION(16：25)DECIMAL EXTERNAL， HiredatePOSITION(26：34)INTEGEREXTERNAL) INTOTABLEPROJ WHENProj-No!K'' (Emp-NoPOSITION(1：4)INTEGEREXTERNAL， Proj-NoPOSITION(35：38)INTEGEREXTERNAL) INTOTABLEPROJ WHENProj-No!K'' (Emp-NoPOSITION(1：4)INTEGEREXTERNAL， Proj-NoPOSITION(39：42)INTEGEREXTERNAL) INTOTABLEPROJ WHENProj-No!K'' (Emp-NoPOSITION(1：4)INTEGEREXTERNAL， Proj-NoPOSITION(43：46)INTEGEREXTERNAL)
注意，CHAR，INTEGEREXTERNAL和DECIMALEXTERNAL這些數(shù)據(jù)類型是指數(shù)據(jù)文件中的數(shù)據(jù)類型，而不是數(shù)據(jù)庫中數(shù)表的數(shù)據(jù)類型。數(shù)字字段的EXTERNAL暗示了它不是以二進(jìn)制格式存儲的數(shù)字，而是以人可以識別的ASCII碼格式存儲。
這樣運(yùn)行SQL*Loader之后，所需數(shù)據(jù)被分別裝入兩個(gè)數(shù)表，非常方便。
2、將ORACLE數(shù)據(jù)文件轉(zhuǎn)化為Excel文件
當(dāng)需要在Excel下獲取ORACLE數(shù)據(jù)時(shí)，可以通過ODBC (假設(shè)ORACLE是安裝在WindowsNT操作系統(tǒng)下)。ODBC的英文意思是OpenDatabaseConnectivity(開放式數(shù)據(jù)庫連接)，它是Microsoft提供的一組標(biāo)準(zhǔn)應(yīng)用程序編程接口(API)。ODBC建立了一組應(yīng)用程序直接操作數(shù)據(jù)庫數(shù)據(jù)的規(guī)范，允許用戶的應(yīng)用程序使用基于SQL語言的不同類型的數(shù)據(jù)庫管理系統(tǒng)。 在Excel的“數(shù)據(jù)”菜單中獲取“獲取外部數(shù)據(jù)”，M icrosoftQuery被啟動。這是一個(gè)非常有用的數(shù)據(jù)查詢程序，它能在網(wǎng)絡(luò)上以客戶機(jī)/服務(wù)器形式快速查詢數(shù)據(jù)。在其中的File菜單中選取NewQuery，“SelectDataSource ”窗口出現(xiàn)，提示用戶在DataSource列表中選取需獲取的數(shù)據(jù)所在的數(shù)據(jù)源。點(diǎn)按“Other”按鈕，“ODBCDataSour ce”窗口出現(xiàn)，列出了機(jī)器中已被定義的所有ODBC數(shù)據(jù)源，如果在其中未能找到ORACLE數(shù)據(jù)源，說明對應(yīng)于ORACLE 數(shù)據(jù)源的ODBCDriver驅(qū)動程序未有安裝。此時(shí)，只要點(diǎn)選 New按紐，在出現(xiàn)的“AddDataSource”對話框中選取ORAC LE7.1Driver，“Oracle7ODBCSetup”窗口出現(xiàn)，分別輸入數(shù)據(jù)庫名字和有效的SQL*Net主機(jī)連接字符串(格式如：P/ServerName/InstantId)，確認(rèn)后MicrosoftQrery 就與ORACLE中的數(shù)據(jù)庫相連。這之后，用戶就可以象使用本機(jī)上的數(shù)據(jù)一樣，對服務(wù)器數(shù)據(jù)進(jìn)行操作。查詢得到結(jié)果退出MicrosoftQuery時(shí)，選擇“返回Excel”，數(shù)據(jù)就被取回到Excel中，在那里進(jìn)行報(bào)表，制圖處理，輸出結(jié)果。
這樣就可以非常方便地在Excel和ORACLE之間完成數(shù)據(jù)互傳，實(shí)現(xiàn)這兩個(gè)軟件取長補(bǔ)短，使其更好地為我們服務(wù)。