excel數據導入Oracle的需求

預計閱讀時間：11分鐘

同事問了個問題，

如何將excel中的數據，導入Oralce？

這種數據導入的需求，可能是我們比較常見的，實現的方法可能有很多。

最簡單的方法，可能就是使用PLSQL Developer，直接進行複製和粘貼操作，如下所示，首先執行select ... for update，拷貝excel數據(刪除無關列)，選中表中所有列，直接粘貼，確認無誤，提交，

但這有一個前提，就是數據量不能太大，否則工具會卡，有人說量級大約10萬以內，沒有親測，各位有興趣，可以試試極限。

另外，就是有些弊端，例如excel中拷貝數據的順序，必須和PLSQL Developer檢索出來的順序一致。

另一種方法，就是Oracle自帶的SQL Developer也可以執行這種操作，工具不同而已，不再測試。

其實無論是PLSQL Developer，還是SQL Developer，之所以能導入excel，其實背後封裝的，就是sqlldr(SQL*Loader)這個工具，話句話說，直接使用sqlldr命令行，也可以實現excel導入的操作。

SQL*Loader是一個Oracle工具，能夠將數據從外部數據文件裝載到資料庫中。他必須包含一個控制文件，可以說，控制文件是SQL*Loader的中樞核心，控制文件能夠控制外部數據文件中的數據如何映射到Oracle的表和列。SQL*Loader能夠接收多種不同格式的數據文件。文件可以存儲在磁碟或磁帶上，或記錄本身可以被嵌套到控制文件中。記錄格式可以是定長的或變長的，定長記錄是指這樣的記錄：每條記錄具有相同的固定長度，並且每條記錄中的數據域也具有相同的固定長度、數據類型和位置。

使用SQL*Loader導入excel，必須另存為txt或者csv，這是一些限制。

SQL*Loader的優點諸如，

1. 可將sqlldr導入命令寫入bat、shell腳本中，自動化執行批量處理。

2. 導入過程提供了各種參數，可以進行各種粒度的控制。

3. 無需在Oracle伺服器上執行，可以在任何其他伺服器，只要能用tns連接。

關於SQL*Loader更詳細的說明可參考《Utilities》，整個第二部分，都會介紹SQL*Loader，在線版本鏈接，

https://docs.oracle.com/cd/E11882_01/server.112/e22490/toc.htm

對於sqlldr的使用，簡單來講，他需要兩個東東，一個是要導入的數據文件，一個是控制文件(.ctl)，他用來說明數據文件中的數據和表的映射關係，然後使用sqlldr命令行，執行導入操作。

無論是控制文件，還是命令行指令，都有非常豐富的參數，提供各種粒度的控制，

Command-Line Parameters

https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_params.htm#SUTIL1018

Control File Reference

https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_control_file.htm#SUTIL005

光說不練假把式，我們做一個excel導入的測試。首先，我們創建測試表，

其次，創建測試excel文件，說是excel，其實要求的是csv，關於excel和csv，https://www.guru99.com/excel-vs-csv.html詳細進行了介紹，中文版翻譯如下，

簡單來講，csv文件其實是逗號分隔的文本文件，但也可以是常規的單元格，如下所示，excel有兩列，三行數據，一列對應於ID，二列對應於NAME，

控制文件excel.ctl內容，其中INFILE表示要導入的數據文件，BADFILE記錄導入失敗的數據，DISCARDFILE記錄丟棄的數據，

執行導入指令，主要的參數就是control，指定控制文件的路徑，log指定日誌路徑，

但是執行之後，報錯了，提示name欄位定義長度1，實際長度2，這就很奇怪了，明明excel中name寫的就是"a"、"b"、"c"，多了什麼字元？

為了驗證，臨時將欄位name長度改為varchar2(10)，重新執行sqlldr，插入正常，看下錶中存儲的欄位值，長度確實為2，多的字元ASCII值是13，10代表換行符，13代表回車，

我們不說為什麼excel會多一個回車，咱們看如何解決？修改控制文件，name欄位增加兩個函數，一個是replace，將chr(13)回車替換為chr(32)，即空格，然後使用trim，刪除空格，保證欄位長度，

此時再次執行，正常插入，log日誌記錄如下，

有些細節問題，例如控制文件，開始用了LOAD，如果導入表存在數據，執行sqlldr，會報錯，提示需要表空，

可以使用TRUNCATE、APPEND等關鍵字，避免此問題，類似的參數控制，還有不少，有需求可以嘗試。

總體來講，將excel導入表，方法有幾種，

1. PLSQL Developer導入。

2. SQL Developer導入。

3. sqlldr導入。

還有其他方法，例如外部表、寫程序讀excel導入，但是還是要根據自己的需求，來選擇合適的，例如導入數據量很小，就可以選擇PLSQL Developer，如果數據量大，則可以使用SQL Developer導入，又或者需要一些控制，例如輸出日誌、定義傳輸buffer，就可以使用sqlldr工具，「沒有最好的方案，只有最合適的」，這句話用在這，沒毛病。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！