sqlldr load excel导入oracle#ocp试验#
源数据animal_feeding.csv100,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_datedate "dd-mon-yyyy" terminated by ',',
pounds_eatendecima 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_eatendecima 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 LenTerm 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
页:
[1]