連結：http://blog.itpub.net/28602568/viewspace-1797410/

註釋：

之前有寫過如何將.dbf/.xls/.csv檔案匯入oracle資料庫適用量較小的檔案；

當excel/txt/csv/dbf等檔案資料大於10w，直接匯入可能比較慢，如下簡單使用了下oracle的外部表功能使資料庫直接載入/讀取系統檔案(而非insert表)。

1、將excel另存為.txt格式，再傳到資料庫directory 路徑（目錄列表：select * from dba_directories）

2、執行建立外部表語句：

create table test

(name1 varchar(4000 )/*等其他欄位*/)

organization external

(type oracle_loader

default directory bak

access parameters

( records delimited by newline characterset UTF16

fields terminated by " "

)location ( '檔名稱.txt' ))

reject limit unlimited;

引數註釋：

( records delimited by newline characterset UTF16 /*字符集要和“檔名稱.txt”字符集相同,而非資料和系統檔案（檢視T.txt字符集命令：file T.txt）*/

fields terminated by " " /*描述欄位的終止符*/

3、查詢表即可 select * from test;

引數介紹：

a）ORGANIZATION EXTERNAL 關鍵字，必須要有。以表明定義的表為外部表。
b）外部表的型別
ORACLE_LOADER：定義外部表的預設方式，只能只讀方式實現文字資料的裝載。
ORACLE_DATAPUMP：支援對資料的裝載與解除安裝，資料檔案必須為二進位制dump檔案。可以從外部表提取資料裝載到內部表，也可以從內部表解除安裝資料作為二進位制檔案填充到外部表。
c）DEFAULT DIRECTORY：預設的目錄指明瞭外部檔案所在的路徑
d）LOCATION：定義了外部表的位置
e）ACCESS PARAMETERS：描述如何對外部表進行訪問
RECORDS關鍵字後定義如何識別資料行
DELIMITED BY 'XXX'——換行符，常用newline定義換行，並指明字符集。對於特殊的字元則需要單獨定義，如特殊符號，

可以使用OX'十六位值'，例如tab(/t)的十六位是9，則DELIMITEDBY0X'09'；
cr(/r)的十六位是d，那麼就是DELIMITEDBY0X'0D'。
SKIP X ——跳過X行資料，有些檔案中第一行是列名，需要跳過第一行，則使用SKIP 1。
FIELDS關鍵字後定義如何識別欄位，常用的如下：
FIELDS：TERMINATED BY 'x'——欄位分割符。
ENCLOSED BY 'x'——欄位引用符，包含在此符號內的資料都當成一個欄位。
例如一行資料格式如："abc","a""b,""c,"。使用引數TERMINATED BY ',' ENCLOSED BY '"'後，系統會讀到兩個欄位，第一個欄位的值是abc，第二個欄位值是a"b,"c,。
LRTRIM ——刪除首尾空白字元。
MISSING FIELD VALUES ARE NULL——某些欄位空缺值都設為NULL。
對於欄位長度和分割符不確定且準備用作外部表檔案，可以使用UltraEdit、Editplus等來進行分析測試，如果檔案較大，則需要考慮將檔案分割成小檔案並從中提取資料進行測試。

f）FIELDS TERMINATED BY "," --描述欄位的終止符

g）REJECT LIMIT UNLIMITED --描述允許的錯誤數，此處為無限制

【源於本人筆記】若有書寫錯誤，表達錯誤，請指正...

此條目發表在 Oracle 分類目錄。將固定連線加入收藏夾。

Oracle 巧用外部表將大量excel資料匯入資料庫

相關文章