excel檔案內容匯入資料庫的問題及解決

今天需要導一些資料，從excel匯入到資料庫中。
沒有裝現成的plsqldev，只能用sql*loader來弄了。
首先我把excel檔案的內容轉換成csv檔案，以逗號分隔，在另存外excel檔案的時候有那個選項。
然後我在目標庫中建立瞭如下的表。
create table sql_summary(sql_time varchar2(100),sql_id varchar2(100),cpu_time varchar2(100),disk_time varchar2(100),exec_time varchar2(100),elapsed_s number);

資料類似下面的格式：
140320_165505,gk9u1b5j5702c,1.18E+10,111636718,691,25.14
140320_205539,gk9u1b5j5702c,1.18E+10,110500905,682,25.95
140321_005607,gk9u1b5j5702c,3049428380,29109514,181,22.07
140321_045625,gk9u1b5j5702c,1.21E+10,114246906,705,22.96
140321_085641,gk9u1b5j5702c,1.16E+10,111346877,687,22.9
140319_045158,gpjv97kkg4fv9,50516321,4705112,1,163.46
140313_123928,gqfnh6bf8h2rc,26741932,349150,4,20.4
140317_004719,gs5bhxa1gamww,500924,143350,1,9.09
140316_084548,gt03f296r4cys,11761212,309219,1,33.29
140314_204223,gun4phkc6tkza,23715395,0,1,23.72
140318_125028,gun4phkc6tkza,22455586,0,1,22.47
140318_165049,gun4phkc6tkza,23880370,1,1,23.89
140319_125232,gun4phkc6tkza,23246466,0,1,23.25
140314_124138,gusarx703b7um,50467329,356132,2,56.08
140314_204223,gv6gaza6da96k,69001509,2860,1,73.75
140316_124606,gv6gaza6da96k,69980362,2834,1,74.82
140320_205539,gwpudzhp5zazc,2907558,629750,1,71.79


建立控制檔案如下
LOAD DATA
INFILE a.txt
 INTO TABLE sql_summary
FIELDS TERMINATED BY "," 
TRAILING NULLCOLS
(sql_time,sql_id,cpu_time,disk_time,exec_time,elapsed_s)

然後呼叫sql*loader來匯入了。粗放一點，其他的日誌檔案就不指定了。 

sqlldr control=a.ctl

 
也沒有報錯，就提示了一行資訊。但是也不算錯誤，檢視錶裡，沒有資料

DUM1102 /oravl01/oracle> sqlldr control='/oravl01/oracle/a.ctl'

SQL*Loader: Release 11.2.0.2.0 - Production on Fri Mar 21 17:16:58 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64

 
對於上面的資訊，查了下，可以在控制檔案中加入這個選項來可控制commit的頻度。最大目前是165.
#OPTIONS (ROWS=100)

設定了以後還是不行。

最後百思不得其解，看看檔案的內容吧，vi 一看，原來是dos格式的問題

140314_124138,007yxsxdz7p0h,13721913,165027,1,30.93^M
140314_164158,007yxsxdz7p0h,20656859,330086,2,19.7^M
140314_204223,007yxsxdz7p0h,20669858,330198,2,21.43^M
140315_124337,007yxsxdz7p0h,11427263,165103,1,20.41^M
140315_164358,007yxsxdz7p0h,20966812,330229,2,22.38^M
140315_204420,007yxsxdz7p0h,10631384,165132,1,20.66^M

用dosux或者dos2unix格式化一把。

重新試一次。資料算是匯入了。：）
指令碼如下。

LOAD DATA
 INTO TABLE sql_summary
FIELDS TERMINATED BY "," 
TRAILING NULLCOLS
(sql_time,sql_id,cpu_time,disk_time,exec_time,elapsed_s)

sqlldr control=a.ctl data=a.data

DUM1102 /oravl01/oracle> sqlldr control=a.ctl data=a.data

SQL*Loader: Release 11.2.0.2.0 - Production on Fri Mar 21 17:36:44 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576
Commit point reached - logical record count 640
Commit point reached - logical record count 704
Commit point reached - logical record count 768
Commit point reached - logical record count 832
Commit point reached - logical record count 896
Commit point reached - logical record count 922
DUM1102 /oravl01/oracle> sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 21 17:36:51 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*)from sql_summary;

  COUNT(*)
----------
       922