預計閱讀時(shí)間：11分鐘
同事問(wèn)了個(gè)問(wèn)題，
如何將excel中的數據，導入Oralce？
這種數據導入的需求，可能是我們比較常見(jiàn)的，實(shí)現的方法可能有很多。
最簡(jiǎn)單的方法，可能就是使用PLSQL Developer，直接進(jìn)行復制和粘貼操作，如下所示，首先執行select ... for update，拷貝excel數據(刪除無(wú)關(guān)列)，選中表中所有列，直接粘貼，確認無(wú)誤，提交，
但這有一個(gè)前提，就是數據量不能太大，否則工具會(huì )卡，有人說(shuō)量級大約10萬(wàn)以?xún)?，沒(méi)有親測，各位有興趣，可以試試極限。
另外，就是有些弊端，例如excel中拷貝數據的順序，必須和PLSQL Developer檢索出來(lái)的順序一致。
另一種方法，就是Oracle自帶的SQL Developer也可以執行這種操作，工具不同而已，不再測試。
其實(shí)無(wú)論是PLSQL Developer，還是SQL Developer，之所以能導入excel，其實(shí)背后封裝的，就是sqlldr(SQL*Loader)這個(gè)工具，話(huà)句話(huà)說(shuō)，直接使用sqlldr命令行，也可以實(shí)現excel導入的操作。
SQL*Loader是一個(gè)Oracle工具，能夠將數據從外部數據文件裝載到數據庫中。他必須包含一個(gè)控制文件，可以說(shuō)，控制文件是SQL*Loader的中樞核心，控制文件能夠控制外部數據文件中的數據如何映射到Oracle的表和列。SQL*Loader能夠接收多種不同格式的數據文件。文件可以存儲在磁盤(pán)或磁帶上，或記錄本身可以被嵌套到控制文件中。記錄格式可以是定長(cháng)的或變長(cháng)的，定長(cháng)記錄是指這樣的記錄：每條記錄具有相同的固定長(cháng)度，并且每條記錄中的數據域也具有相同的固定長(cháng)度、數據類(lèi)型和位置。
使用SQL*Loader導入excel，必須另存為txt或者csv，這是一些限制。
SQL*Loader的優(yōu)點(diǎn)諸如，
1. 可將sqlldr導入命令寫(xiě)入bat、shell腳本中，自動(dòng)化執行批量處理。
2. 導入過(guò)程提供了各種參數，可以進(jìn)行各種粒度的控制。
3. 無(wú)需在Oracle服務(wù)器上執行，可以在任何其他服務(wù)器，只要能用tns連接。
關(guān)于SQL*Loader更詳細的說(shuō)明可參考《Utilities》，整個(gè)第二部分，都會(huì )介紹SQL*Loader，在線(xiàn)版本鏈接，
https://docs.oracle.com/cd/E11882_01/server.112/e22490/toc.htm
對于sqlldr的使用，簡(jiǎn)單來(lái)講，他需要兩個(gè)東東，一個(gè)是要導入的數據文件，一個(gè)是控制文件(.ctl)，他用來(lái)說(shuō)明數據文件中的數據和表的映射關(guān)系，然后使用sqlldr命令行，執行導入操作。
無(wú)論是控制文件，還是命令行指令，都有非常豐富的參數，提供各種粒度的控制，
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
光說(shuō)不練假把式，我們做一個(gè)excel導入的測試。首先，我們創(chuàng )建測試表，
create table excel(
id number,
name varchar2(1)
);其次，創(chuàng )建測試excel文件，說(shuō)是excel，其實(shí)要求的是csv，關(guān)于excel和csv，https://www.guru99.com/excel-vs-csv.html詳細進(jìn)行了介紹，中文版翻譯如下，
簡(jiǎn)單來(lái)講，csv文件其實(shí)是逗號分隔的文本文件，但也可以是常規的單元格，如下所示，excel有兩列，三行數據，一列對應于ID，二列對應于NAME，
控制文件excel.ctl內容，其中INFILE表示要導入的數據文件，BADFILE記錄導入失敗的數據，DISCARDFILE記錄丟棄的數據，
Load DATA
INFILE '/opt/app/excel/excel.csv'
BADFILE '/opt/app/excel/output.bad'
DISCARDFILE '/opt/app/excel/output.dsc'
into table excel
fields terminated by ','
(id,name)執行導入指令，主要的參數就是control，指定控制文件的路徑，log指定日志路徑，
sqlldr user/password@db control=/opt/app/excel/excel.ctl
rows=10000 bindsize=8192 readsize=8192 errors=999999
log=/opt/app/excel/output.log但是執行之后，報錯了，提示name字段定義長(cháng)度1，實(shí)際長(cháng)度2，這就很奇怪了，明明excel中name寫(xiě)的就是'a'、'b'、'c'，多了什么字符？
為了驗證，臨時(shí)將字段name長(cháng)度改為varchar2(10)，重新執行sqlldr，插入正常，看下表中存儲的字段值，長(cháng)度確實(shí)為2，多的字符ASCII值是13，10代表?yè)Q行符，13代表回車(chē)，
SQL> SELECT NAME, length(NAME), DUMP(NAME) FROM excel_x;
NAM LENGTH(NAME) DUMP(NAME)
--- ------------ ------------
a 2 Typ=1 Len=2: 97,13
b 2 Typ=1 Len=2: 98,13
c 2 Typ=1 Len=2: 99,13Load DATA
INFILE '/opt/app/excel/excel.csv'
BADFILE '/opt/app/excel/output.bad'
DISCARDFILE '/opt/app/excel/output.dsc'
into table excel
fields terminated by ','
(id,name 'trim(replace(:name,chr(13),chr(32)))')此時(shí)再次執行，正常插入，log日志記錄如下，
SQL*Loader: Release 9.2.0.7.0 - Production on Wed Aug 8 03:15:05 2018
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Control File: /opt/app/excel/excel.ctl
Data File: /opt/app/excel/excel.csv
Bad File: /opt/app/excel/output.bad
Discard File: /opt/app/excel/output.dsc
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 999999
Bind array: 10000 rows, maximum of 8192 bytes
Continuation: none specified
Path used: Conventional
Table EXCEL, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
--------------- ---------- ----- ---- ---- ---------------------
ID FIRST * , CHARACTER
NAME NEXT * , CHARACTER
SQL string for column : 'trim(replace(:name,chr(13),chr(32)))'
Table EXCEL:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 7740 bytes(15 rows)
Read buffer bytes: 8192
Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed Aug 08 03:15:05 2018
Run ended on Wed Aug 08 03:15:05 2018
Elapsed time was: 00:00:00.07
CPU time was: 00:00:00.02有些細節問(wèn)題，例如控制文件，開(kāi)始用了LOAD，如果導入表存在數據，執行sqlldr，會(huì )報錯，提示需要表空，
SQL*Loader-601: For INSERT option, table must be empty.
Error on table EXCEL可以使用TRUNCATE、APPEND等關(guān)鍵字，避免此問(wèn)題，類(lèi)似的參數控制，還有不少，有需求可以嘗試。
總體來(lái)講，將excel導入表，方法有幾種，
1. PLSQL Developer導入。
2. SQL Developer導入。
3. sqlldr導入。
還有其他方法，例如外部表、寫(xiě)程序讀excel導入，但是還是要根據自己的需求，來(lái)選擇合適的，例如導入數據量很小，就可以選擇PLSQL Developer，如果數據量大，則可以使用SQL Developer導入，又或者需要一些控制，例如輸出日志、定義傳輸buffer，就可以使用sqlldr工具，“沒(méi)有最好的方案，只有最合適的”，這句話(huà)用在這，沒(méi)毛病。
聯(lián)系客服
