- 相關推薦
ORACLE與excel的數據互傳方法
眾所周知，MicrosoftExcel能很直觀而方便地進行數據輸入，統計，生成圖表，但它的數據管理能力有限，對大量的數據查詢能力不足，如果利用它數據計算方面的優點和大型數據庫ORACLE的數據查詢優點，可以設計出功能強大的數據處理系統。
假設與Excel數據文件相對應的職工情況表EMP已在ORAC LE系統中創建，此表中各字段按照順序分別是職工代號( Emp—No)，職工姓名(Ename)，工資(Salary)，雇用時間(Hiredate)。
1、將Excel中的數據裝入ORACLE數據庫
通過ORACLE數據庫系統的高級應用工具SQL*Loader 可以將原來的Excel中存儲的數據直接裝入ORACLE數據庫的表中。
首先，在MicrosoftExcel中完成表格數據輸入后，選擇“保存文件”命令，在出現對話框的“文件類型”欄中選取“格式化文本文件(空格分隔)”以形成一個標準格式化文本文件(*.PRN)或是選取“CSV(逗號分隔)” 形成一個逗號定界文件(*.CSV)。這兩者的區別是：標準格式的文本文件中每個記錄等長，數據間按原庫文件字段長度緊湊排列，字符型字段數據左對齊，數據型字段數據右對齊，不足部分用空格補足。逗號定界文件特點是各記錄可不等長，字段按其定義順序依次排列，字段間用逗號分隔，字符型字段和數據型字段的左右端空格被消去。
因為這兩種格式文件有不同的數據裝入方法，假如我們將職工情況EMP表存為EMP.CSV，數據變成如下形式：
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
接著，通過記事本建立以下控制文件，它包含了數據文件的名稱及其格式，文件字段如何定界，數據類型是怎樣等內容，將該裝載控制文件保存為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圖標，進入ORACLE7的數據裝載工具，在彈出的SQLLOADER主屏幕對話框中，分別輸入用戶登錄名，密碼，數據庫名稱和控制文件名，確認后進行數據裝載。這樣SQL*Loader一邊轉換、裝入數據，一邊將執行過程中的錯誤信息和統計信息存入登錄文件(LogFile)中，把ORACLE拒絕裝入的原始數據存入拒絕文件(RejectFile)。用戶可以在裝載完成后，打開登錄文件文件查看數據的裝載情況，了解因為何種原因使得數據被拒絕，是因為數據本身不符合數據庫的數表定義還是違反了完整性原則等其它原因。由此可見，數據裝載不僅快速而且安全。
特別要提到的是可以通過SQL*Loader同時將數據文件裝入多個數表。例如在Excel表格中輸入數據時，可以對應輸入該職工參加的項目代號(Proj-No)，現在需要將這些數據分別裝入EMP表和PROJ表。數據文件按標準格式存儲如下所示，其中第5，6，7列均為該職工參加的項目代號：
120JOE1192.6493/04/11101112
121KERT309.6087/01/142812
23PETER1500.2584/03/124017
124SMITH678.0091/01/07102021
15YOUNG2390.1280/05/03432617
編寫的裝載數據控制文件可以是：
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這些數據類型是指數據文件中的數據類型，而不是數據庫中數表的數據類型。數字字段的EXTERNAL暗示了它不是以二進制格式存儲的數字，而是以人可以識別的ASCII碼格式存儲。
這樣運行SQL*Loader之后，所需數據被分別裝入兩個數表，非常方便。
2、將ORACLE數據文件轉化為Excel文件
當需要在Excel下獲取ORACLE數據時，可以通過ODBC (假設ORACLE是安裝在WindowsNT操作系統下)。ODBC的英文意思是OpenDatabaseConnectivity(開放式數據庫連接)，它是Microsoft提供的一組標準應用程序編程接口(API)。ODBC建立了一組應用程序直接操作數據庫數據的規范，允許用戶的應用程序使用基于SQL語言的不同類型的數據庫管理系統。 在Excel的“數據”菜單中獲取“獲取外部數據”，M icrosoftQuery被啟動。這是一個非常有用的數據查詢程序，它能在網絡上以客戶機/服務器形式快速查詢數據。在其中的File菜單中選取NewQuery，“SelectDataSource ”窗口出現，提示用戶在DataSource列表中選取需獲取的數據所在的數據源。點按“Other”按鈕，“ODBCDataSour ce”窗口出現，列出了機器中已被定義的所有ODBC數據源，如果在其中未能找到ORACLE數據源，說明對應于ORACLE 數據源的ODBCDriver驅動程序未有安裝。此時，只要點選 New按紐，在出現的“AddDataSource”對話框中選取ORAC LE7.1Driver，“Oracle7ODBCSetup”窗口出現，分別輸入數據庫名字和有效的SQL*Net主機連接字符串(格式如：P/ServerName/InstantId)，確認后MicrosoftQrery 就與ORACLE中的數據庫相連。這之后，用戶就可以象使用本機上的數據一樣，對服務器數據進行操作。查詢得到結果退出MicrosoftQuery時，選擇“返回Excel”，數據就被取回到Excel中，在那里進行報表，制圖處理，輸出結果。
這樣就可以非常方便地在Excel和ORACLE之間完成數據互傳，實現這兩個軟件取長補短，使其更好地為我們服務。