搜索
Excel與Oracle之間資料傳遞精誠資訊專業服務技術期刊作者：鄭嘉松StevenCheng文章編號：OCS08091901難易等級：易■■□□□難前言眾所周知，MicrosoftExcel能很直觀而方便地進行資料輸入，統計，生成圖表，但它的資料管理能力有限，對大量的資料查詢能力不足，如果利用它資料計算方面的優點和大型資料庫ORACLE的資料查詢優點，可以設計出功能強大的資料處理系統。本文利用Excel資料檔案與Oracle相對應的職工情況表EMP說明：此表中各字段按照順序分別是職工代號(EMPNO)，職工姓名(ENAME)，工作性質(JOB)，雇用時間(HIREDATE)，工資(SALARY)。本文將透過一些範例來介紹Excel與ORACLE資料交換的具體步驟。1、將Excel中的資料載入ORACLE資料庫透過ORACLE資料庫系統的進階應用工具SQL*Loader可以將原來的Excel中存儲的資料直接載入ORACLE資料庫的表中。首先，在MicrosoftExcel中完成表格資料輸入後，選擇“儲存檔案”命令，在出現對話框的“檔案類型”欄中選取“格式化文本檔案(空格分隔)”儲存成一個標準格式化文本檔案(*.PRN)或是選取“CSV(逗號分隔)”形成一個逗號定界檔案(*.CSV)。這兩者的區別是：標準格式的文本檔案中每個記錄等長，資料間按原資料庫檔案字段長度緊湊排列，字符型字段資料左對齊，資料型字段資料右對齊，不足部分用空格補足。逗號定界檔案特點是各記錄可不等長，字段按其定義順序依次排列，字段間用逗號分隔，字符型字段和資料型字段的左右端空格被消去。因為這兩種格式文件有不同的資料載入方法，本文介紹一般常用的CSV(逗號分隔)方式。我們將職工情況EMP_LAB表格存為EMP_LAB.CSV，資料變成如下形式：7369,SMITH,CLERK,1980/12/17,8007499,ALLEN,SALESMAN,1981/2/20,16007521,WARD,SALESMAN,1981/2/22,12507566,JONES,MANAGER,1981/4/2,29757654,MARTIN,SALESMAN,1981/9/28,12507698,BLAKE,MANAGER,1981/5/1,28507782,CLARK,MANAGER,1981/6/9,24507839,KING,PRESIDENT,1981/11/17,50007844,TURNER,SALESMAN,1981/9/8,15007900,JAMES,CLERK,1981/12/3,9507902,FORD,ANALYST,1981/12/3,30007934,MILLER,CLERK,1982/1/23,1300接著，通過記事本建立以下控制文件，它包含了資料文件的名稱及其格式，文件字段如何定界，資料類型是怎樣等內容，將該控制文件保存為EMP_LAB.CTLLOADDATAINFILE'D:\EMP_LAB.CSV'BADFILE'D:\EMP_LAB.bad'DISCARDFILE'D:\EMP_LAB.dsc'INTOTABLESTEVEN.EMP_LABAPPENDREENABLEDISABLED_CONSTRAINTSEXCEPTIONSSTEVEN.EMP_LABFIELDSTERMINATEDBY','(EMPNO,ENAME,JOB,HIREDATEDATE(10)YYYY/MM/DD,SAL)建立一個表格（STEVEN.EMP_LAB）語法如下：CREATETABLESTEVEN.EMP_LAB(EMPNONUMBER(4),ENAMEVARCHAR2(10BYTE),JOBVARCHAR2(9BYTE),HIREDATEDATE,SALNUMBER(7,2))TABLESPACEUSERS;之後，執行SQL*Loader工具，將資料載入到ORACLE資料庫（STEVEN.EMP_LAB）：C:\ORACLE_HOME\BINsqlldrsteven/systex@sora10gcontrol=d:\EMP_LAB.CTL我們可以在載入完成後，打開登錄檔案檢視資料的載入情況，了解是否載入資料成功或因為何種原因使得資料被拒絕，如果載入失敗可能是因為資料本身不符合資料庫的表格定義或是違反了完整性原則等其它原因。由此可見，SQL*Loader不僅快速而且安全。2、將ORACLE資料檔案轉化為Excel檔案當需要在Excel下讀取ORACLE資料時，可以透過ODBC。ODBC的英文意思是OpenDatabaseConnectivity(開放式資料庫連接)，它是Microsoft提供的一組標準應用程式界面(API)。ODBC建立了一組應用程式直接操作資料庫資料的規範，允許用戶的應用程式使用基於SQL語言的不同類型的資料庫管理系統。在Excel的“資料”選單中獲取“取得外部資料”，MicrosoftQuery被啟動。這是一個非常有用的資料查詢程式，它能在網路上以Client/Server形式快速查詢資料。在其中的File選單中選取NewQuery，“SelectDataSource”視窗出現，提示用戶在DataSource清單中選取需外部讀取資料所在的資料源。點按“Other”按鈕，“ODBCDataSource”視窗出現，列出了機器中已被定義的所有ODBC資料源，分別輸入資料庫名字和有效的SQL*Net主機連接字元串，確認後MicrosoftQuery就與ORACLE中的資料庫相連。這之後，用戶就可以像使用本機上的資料一樣，對伺服器資料進行操作。查詢得到結果退出MicrosoftQuery時，選擇“返回Excel”，資料就被取回到Excel中，在那裡進行報表，製圖處理，輸出結果。這樣就可以非常方便地在Excel和ORACLE之間完成資料互傳，實現這兩個軟體取長補短，使其更好地為我們服務。1.設定ODBC2.選擇連線Driver3.設定連線DB（必頇事先設好tnsnames.ora）4.開啟MicrosoftExcel（選擇從其他來源，取得外部資料）5.選擇從MicrosoftQuery6.選擇DataSource（ODBC連線名稱HR）7.輸入連線登入ID和密碼8.如果查詢的Owner非剛剛連線的SYSTEM，可以從”選項”設定其他Owner，例如：STEVEN9.選擇欲查詢的Table（EMP_LAB）和欄位10.可以設定篩選條件和排序條件11.也可以直接在Excel上點選排序欄位12.可以依據需求產生統計圖表