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

azzotest發表於2015-09-30
OracleExcel資料庫

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

作者：ｌōττéｒｙ©版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]

註釋：
    之前有寫過 如何將.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  分類目錄。將固定連線加入收藏夾。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26224278/viewspace-1812424/，如需轉載，請註明出處，否則將追究法律責任。

相關文章