sqlldr load excel导入oracle#ocp试验#

喜旎果

　　源数据animal_feeding.csv
　　100,1-jan-2000,23.5,"Flipper seemed unusually hungry today."
105,1-jan-2000,99.45,"Spread over three meals."
112,1-jan-2000,10,"No comment."
151,1-jan-2000,55
166,1-jan-2000,17.5,"Shorty ate Squacky."
145,1-jan-2000,0,"Squacky is no more."
175,1-jan-2000,35.5,"Paintuin skipped his first meal, but ate the other five."
199,1-jan-2000,0.5,"Nosey wasn't very hungry today."
202,1-jan-2000,22.0
240,1-jan-2000,28,"Snoops appeared lethargic, and was running a fever."
100,2-jan-2000,19.5,"Flipper's appetite has returned to normal."
105,2-jan-2000,89.0
112,2-jan-2000,12
151,2-jan-2000,50
166,2-jan-2000,16.0,"We are keeping Shorty isolated from the other animals."
175,2-jan-2000,30
199,2-jan-2000,9.5,"Nosey's appetite has returned."
202,2-jan-2000,19.3
240,2-jan-2000,22,"Snoops still lethargic, no fever."
100,3-jan-2000,16,"Flipper's appetite is on the decrease."
105,3-jan-2000,101
112,3-jan-2000,8,"Bopper was very aggressive during feeding."
151,3-jan-2000,43
166,3-jan-2000,15,"We are back to normal w/Shorty."
175,3-jan-2000,33
199,3-jan-2000,8
202,3-jan-2000,18
240,3-jan-2000,30,"Snoops is back to his normal self."
　　现在要把这些数据写入scott下
　　1，在scott下建表



SET ECHO ON
CREATE TABLE animal_feeding (
animal_id               NUMBER,
feeding_dateDATE,
pounds_eatenNUMBER (5,2),
noteVARCHAR2(80)
);

　　2，写load的control控制文件load_exam.txt
　　



load
infile 'D:\animal_feeding.csv' --源数据
append                        --覆盖写入？
into table scott.animal_feeding
trailing nullcols             --源数据没有对应，写入null
( animal_id   integer external terminated by ',', --“,”结束标记，也可以指定长度position (1:3 4:14)
feeding_date  date "dd-mon-yyyy" terminated by ',',
pounds_eaten  decima external terminated by ',',
note          char terminated by ','
optionally enclosed by '"'  --note源文件有双引号，这里去掉
)

　　3，windows下的命令行导入
　　这里可以指定log的文件目录
　　特别注意，因为scott是oracle的默认建立，好多的实例都有scott，并且密码都是tiger，因此做之前最好set oracle_sid=sen指定
　　一步到处登陆：sqlplus scott/tiger@sen



C:\Documents and Settings\dell>sqlldr scott/tiger control=d:\load_exam.txt log=d:\load_exam_log.txt

　　回车报错
　　SQL*Loader-350: 语法错误位于第 9 行。
预期值是 有效的列说明, "," 或 ")", 而实际值是 "decima"。
    pounds_eaten  decima external terminated by ',',

　　修改
　　原来是decimal少了最后的“l”
　　执行，之后报错，看log

　　修改系统的日期显示为american

　　
　　4，从scott查询这个表，得到结果，成功

　　下面是日志
　　
SQL*Loader: Release 11.2.0.1.0 - Production on Mon Dec 31 18:43:11 2012
　　Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
　　Control File:   d:\load_exam.txt
Data File:      D:\animal_feeding.csv
  Bad File:     d:\animal_feeding.bad
  Discard File:  none specified
(Allow all discards)
　　Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional
　　Table SCOTT.ANIMAL_FEEDING, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
　　Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ANIMAL_ID                           FIRST     *   ,       CHARACTER            
FEEDING_DATE                         NEXT     *   ,       DATE dd-mon-yyyy     
POUNDS_EATEN                         NEXT     *   ,       CHARACTER            
NOTE                                 NEXT     *   ,  O(") CHARACTER            
　　
Table SCOTT.ANIMAL_FEEDING:
  28 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:                  66048 bytes(64 rows)
Read   buffer bytes: 1048576
　　Total logical records skipped:          0
Total logical records read:            28
Total logical records rejected:         0
Total logical records discarded:        0
　　Run began on Mon Dec 31 18:43:11 2012
Run ended on Mon Dec 31 18:43:11 2012
　　Elapsed time was:     00:00:00.18
CPU time was:         00:00:00.00