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_date	DATE,
	pounds_eaten	NUMBER (5,2),
	note			VARCHAR2(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