轉載：利用SQL*Loader将 Excel 数据导出到数据库中
系统环境：
1、操作系统：Windows 2003 Server R2，机器内存1024MITPUB个人空间
N.]M6o~
X [ C"U)I
2、数据库： Oracle 10g (10.2.0.2) for NT 企业版
a&A a _ u`;iX }#y03、安装路径：D:\ORACLE
(ao0wGf9g0================================================
实现步骤：
1U|U ?6rpG(v0
0l}x4oQ z U0
H1Q#P7~k!T0
2@,o3nj#T c{ Z6b0
&ZK9ryI C9~0
\*H*nZnq0
c(^'_4Ot;s:N"Z6KMs0
b
t+{ M-D'nD0
#Q
IC5g"Pt"F(K9HH0
;_Xt[)P[ {G7`$o0
"i k%R,@IZ,B0I0
,v ai ^5w4Iu-_0另附sqlldr的用法（出处不详）：
sql load的一点小总结
)` hnRW)tL0
sqlldr userid=lgone/tiger control=a.ctl
?2}r/w"Yt o#_;h0LOAD DATA
1Q
FBwq6VO/~6L0INFILE 't.dat' // 要导入的文件
// INFILE 'tt.date' // 导入多个文件
!wt'qk/p0// INFILE * // 要导入的内容就在control文件里下面的BEGINDATA后面就是导入的内容
?aH b)xy+uz0
9kG P)vk _-Av0INTO TABLE table_name // 指定装入的表
;I P'Im7qm#g0BADFILE 'c:\bad.txt' // 指定坏文件地址
*h l+f6\$B&f)zx6X0
N y8W(EIk'y1KX0************* 以下是4种装入表的方式
JW'` _peP^+?0APPEND // 原先的表有数据就加在后面
@ aFG |n/q W0// INSERT // 装载空表 如果原先的表有数据 sqlloader会停止 默认值
hx#H d]\:g-wm0// REPLACE // 原先的表有数据原先的数据会全部删除
// TRUNCATE // 指定的内容和replace的相同会用truncate语句删除现存数据
************* 指定的TERMINATED可以在表的开头也可在表的内部字段部分
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
a'`e:n:v
z+S0// 装载这种数据: 10,lg,"""lg""","lg,lg"
// 在表中结果: 10 lg "lg" lg,lg
'|2L4F2l h-BQ.p4\9I0// TERMINATED BY X '09' // 以十六进制格式 '09' 表示的
// TERMINATED BY WRITESPACE // 装载这种数据: 10 lg lg
c#h"K5F e c1n_C0
TRAILING NULLCOLS ************* 表的字段没有对应的值时允许为空
;S U;k Y l'O0
CC:LD t:_0************* 下面是表的字段
(
V2p0Jkl0col_1 , col_2 ,col_filler FILLER // FILLER 关键字此列的数值不会被装载
// 如: lg,lg,not 结果 lg lg
)
gZ.@6`[4E7o0// 当没声明FIELDS TERMINATED BY ',' 时
`9x2M3J8gYG.z&m0// (
%R2O_,Z"n0B4tG0// col_1 [interger external] TERMINATED BY ',' ,
// col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
// col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'
// )
:X"Xe&{$[7X[2]2P0// 当没声明FIELDS TERMINATED BY ','用位置告诉字段装载数据
N'q9^DD)@H1YE@0// (
// col_1 position(1:2),
F1`&wfU u.U9Ikw"f0// col_2 position(3:10),
// col_3 position(*:16), // 这个字段的开始位置在前一字段的结束位置
// col_4 position(1:16),
v*r9AbK5dp0// col_5 position(3:10) char(8) // 指定字段的类型
// )
BEGINDATA // 对应开始的 INFILE * 要导入的内容就在control文件里
K.p8D%p*d4Aucz/VM010,Sql,what
20,lg,show
=====================================================================================
//////////// 注意begindata后的数值前面不能有空格
E8q%fX%c1[ qgv-X0
1 ***** 普通装载
LOAD DATA
"S a7JT `9w `0INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,
5M5Eb u!tOw b0DNAME,
I7ax Enw{)i0LOC
L?7KI5z0)
&\&yc#{:nj0BEGINDATA
m'VMZjl010,Sales,"""USA"""
20,Accounting,"Virginia,USA"
L8CT;~4F(X"\s u030,Consulting,Virginia
f,}1R`#]~ F?(B$CPd040,Finance,Virginia
)g2i
zh/jGeL s c050,"Finance","",Virginia // loc 列将为空
60,"Finance",,Virginia // loc 列将为空
2 ***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情况
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY WHITESPACE
&v!j/P'x;s s(L0-- FIELDS TERMINATED BY x'09'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia
3 ***** 指定不装载那一列
CAktX7g Ec![!h0LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
$K)F+wx.Os0FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
nV5e+u-d^ p-}k%n0( DEPTNO,
*dT9{gw0FILLER_1 FILLER, // 下面的 "Something Not To Be Loaded" 将不会被装载
DNAME,
Rv0OJM&n? Jw0LOC
7y5d$\6i^\0)
BEGINDATA
F0wm%~Hy"o+tv s3W020,Something Not To Be Loaded,Accounting,"Virginia,USA"
3SbGJ A3z8S04 ***** position的列子
e*?'U s$z0LOAD DATA
INFILE *
+|(U+~/y&S_/u,l0INTO TABLE DEPT
F;T9bn6^~}y.Q.tj0REPLACE
f5v$kT Z.X$p0GZ,y X0( DEPTNO position(1:2),
DNAME position(*:16), // 这个字段的开始位置在前一字段的结束位置
"` ?+a _#K7|7f
Z;g0LOC position(*:29),
|(Z8~6c-H%SN,v%ay w0ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting Virginia,USA
8D-T
[)ps9K:eRc0
5 ***** 使用函数 日期的一种表达 TRAILING NULLCOLS的使用
N1p T
?,pu1Ro0LOAD DATA
4K$U5@#L)Y0INFILE *
.v {DE.V u3E ` V g8g0INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS // 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应
// 的列的值的 如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
(DEPTNO,
DNAME "upper(:dname)", // 使用函数
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy', // 日期的一种表达方式还有'dd-mon-yyyy' 等
;I*d_ZS)F0ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
fn T QWq/@xZR0)
k3F"Q C!lj+c[8zj _0BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
D`4QP&q8T
s(B
t040,Finance,Virginia,15/3/2001
pAY%q_"eps0
6 ***** 使用自定义的函数 // 解决的时间问题
k ~J E:a0create or replace
function my_to_date( p_string in varchar2 ) return date
3Om$|xI X[0as
type fmtArray is table of varchar2(25);
l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy',
aJ"c-K wl0'dd/mm/yyyy',
'dd/mm/yyyy hh24:mi:ss' );
1W
S.i{0? O)z0l_return date;
#m/m jGmu9xR}0begin
for i in 1 .. l_fmts.count
loop
begin
l_return := to_date( p_string, l_fmts(i) );
exception
] h1{.id Y T F:O/g0when others then null;
AQ^ G&Ym5Kb0end;
R Yh`;W9Y0EXIT when l_return is not null;
end loop;
'GM,ZpU1r9N0
if ( l_return is null )
-u6` J&x UC%D
J7h0then
UNYvE4}+~ Y e4t0l_return :=
new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *
p_string, 'GMT', 'EST' );
6`%y3JS1agC%x3QM c0end if;
!FpX-bK"~
Z0
v M~ U-Ox0return l_return;
end;
N9k.l7`-T/mh%r0/
cS8WlAJ0LOAD DATA
INFILE *
hP*pZ7D Ny
a.z0INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )" // 使用自定义的函数
;^}
oB*Du0)
BEGINDATA
/T^4qED `010,Sales,Virginia,01-april-2001
%fyL J5sE;b020,Accounting,Virginia,13/04/2001
!O9z
} Pp4y030,Consulting,Virginia,14/04/2001 12:02:02
40,Finance,Virginia,987268297
50,Finance,Virginia,02-apr-2001
60,Finance,Virginia,Not a date
&lH.m$T D0
7m {l~8hI-U'Jx07 ***** 合并多行记录为一行记录
LOAD DATA
+\
G,DL!B0INFILE *
concatenate 3 // 通过关键字concatenate 把几行的记录看成一行记录
DOs X"tsKw0INTO TABLE DEPT
k sBdF
W e `(n/M0replace
FIELDS TERMINATED BY ','
`a/kKI}%yW`0(DEPTNO,
DNAME "upper(:dname)",
|hgW+]~0LOC "upper(:loc)",
-L Q a${3v6x)NY0LAST_UPDATED date 'dd/mm/yyyy'
)
8?L f ~ whN:}/P*y0BEGINDATA
|&`vF1Hr010,Sales, // 其实这3行看成一行 10,Sales,Virginia,1/5/2000
?s5v/|$M0Virginia,
` BO6R `U Q#gp01/5/2000
// 这列子用 continueif list="," 也可以
+sq|!\)Ro
] H0告诉sqlldr在每行的末尾找逗号找到逗号就把下一行附加到上一行
LOAD DATA
:z4L8k t/j,A!A/g7s0INFILE *
5Cs!g3S3m UW0Y0continueif this(1:1) = '-' // 找每行的开始是否有连接字符 - 有就把下一行连接为一行
KS&k*u ve(}jt0// 如 -10,Sales,Virginia,
// 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000
// 其中1:1 表示从第一行开始并在第一行结束还有continueif next 但continueif list最理想
^P(am&uT(R$m0INTO TABLE DEPT
4h:Q
@3fjf ~1};S0replace
FIELDS TERMINATED BY ','
3y)gU+lN ~0(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
,V-J j!_^'A$K&o0LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA // 但是好象不能象右面的那样使用
7m%Jm.VN{ Z0-10,Sales,Virginia, -10,Sales,Virginia,
1/5/2000 1/5/2000
-40, 40,Finance,Virginia,13/04/2001
Tr0l+tI Jxp0Finance,Virginia,13/04/2001ITPUB个人空间+^P+r-Q"EaH
8 ***** 载入每行的行号
\)D:x r;d k6[0
E#i6Jw)tN0load data
?l6V;}}[WoI0infile *
into table t
!P7tn#l1k.XB"F\ Y0replace
( seqno RECNUM //载入每行的行号
text Position(1:1024))
*J
L4[ n/}C*uw\0BEGINDATA
?(cW
@
A_$Kc)j0fsdfasj //自动分配一行号给载入 表t 的seqno字段此行为 1
fasdjfasdfl // 此行为 2 ...
#j @ ik0Hs Yh G d09 ***** 载入有换行符的数据
注意: unix 和 windows 不同\\n& /n
< 1 > 使用一个非换行符的字符
D [:|B,ad@ r(Q0LOAD DATA
t'I*Il(ff0INFILE *
INTO TABLE DEPT
"l8K9a+SI+`W|0REPLACE
FIELDS TERMINATED BY ','
D1N:[
bq X_ ~0TRAILING NULLCOLS
%s.i9nZ
OQG7Z H0(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS "replace(:comments,'\n',chr(10))" // replace 的使用帮助转换换行符
)