表结构
create table PE_DEPT_RESULT_ITEMS
(
PE_ID VARCHAR2(10) not null,
PE_VISIT_ID NUMBER(4) not null,
PE_DEPT_CODE VARCHAR2(8) not null,
CONTENT_CLASS VARCHAR2(1) not null,
CONTENT VARCHAR2(2000),
DESCRIPTION VARCHAR2(32),
IN_DEPT_REPORT VARCHAR2(1),
IN_CHIEF_REPORT VARCHAR2(1)
)
在C盘根目录下存了csv文件,csv文件是由xls另存为而来的,其中第一行是字段名,删除之后还是不行
PE_ID PE_VISIT_ID PE_DEPT_CODE CONTENT_CLASS CONTENT DESCRIPTION IN_DEPT_REPORT IN_CHIEF_REPORT
1 14441 1 801 0 "花飘万家雪" 1 0然后在用记事本新建ctl文件存到C盘根目录下如下
load data --1、控制文件标识
infile 'C:\pe_dept_result_items.csv' --2、要输入的数据文件名为lcolddata.xls
replace into table pe_dept_result_items --3、向表test中插入记录
fields terminated by X'09' --4、字段终止于X'09',是一个制表符(TAB)
( PE_ID,PE_VISIT_ID,PE_DEPT_CODE,CONTENT_CLASS,CONTENT,DESCRIPTION,IN_DEPT_REPORT,IN_CHIEF_REPORT) -----定义列对应顺序 之后在CMD中执行sqlldr userid=system/asdfasdf control=input.ctl
返回
SQL*Loader: Release 10.1.0.2.0 - Production on 星期二 12月 9 05:28:12 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.
好像是成功了,但在日志文件中好多错误Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: ConventionalTable PE_DEPT_RESULT_ITEMS, loaded from every logical record.
Insert option in effect for this table: REPLACE Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PE_ID FIRST * WHT CHARACTER
PE_VISIT_ID NEXT * WHT CHARACTER
PE_DEPT_CODE NEXT * WHT CHARACTER
CONTENT_CLASS NEXT * WHT CHARACTER
CONTENT NEXT * WHT CHARACTER
DESCRIPTION NEXT * WHT CHARACTER
IN_DEPT_REPORT NEXT * WHT CHARACTER
IN_CHIEF_REPORT NEXT * WHT CHARACTER Record 1: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_ID.
Field in data file exceeds maximum length
Record 3: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 5: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 6: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_ID.
Field in data file exceeds maximum length
Record 7: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 8: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 9: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)Table PE_DEPT_RESULT_ITEMS:
0 Rows successfully loaded.
9 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: 132096 bytes(64 rows)
Read buffer bytes: 1048576Total logical records skipped: 0
Total logical records read: 9
Total logical records rejected: 9
Total logical records discarded: 0Run began on 星期二 12月 09 05:28:12 2008
Run ended on 星期二 12月 09 05:28:13 2008Elapsed time was: 00:00:00.87
CPU time was: 00:00:00.05
字段到底有什么问题呀?是不是什么工作没有做好呀?刚开始用,请指教?
create table PE_DEPT_RESULT_ITEMS
(
PE_ID VARCHAR2(10) not null,
PE_VISIT_ID NUMBER(4) not null,
PE_DEPT_CODE VARCHAR2(8) not null,
CONTENT_CLASS VARCHAR2(1) not null,
CONTENT VARCHAR2(2000),
DESCRIPTION VARCHAR2(32),
IN_DEPT_REPORT VARCHAR2(1),
IN_CHIEF_REPORT VARCHAR2(1)
)
在C盘根目录下存了csv文件,csv文件是由xls另存为而来的,其中第一行是字段名,删除之后还是不行
PE_ID PE_VISIT_ID PE_DEPT_CODE CONTENT_CLASS CONTENT DESCRIPTION IN_DEPT_REPORT IN_CHIEF_REPORT
1 14441 1 801 0 "花飘万家雪" 1 0然后在用记事本新建ctl文件存到C盘根目录下如下
load data --1、控制文件标识
infile 'C:\pe_dept_result_items.csv' --2、要输入的数据文件名为lcolddata.xls
replace into table pe_dept_result_items --3、向表test中插入记录
fields terminated by X'09' --4、字段终止于X'09',是一个制表符(TAB)
( PE_ID,PE_VISIT_ID,PE_DEPT_CODE,CONTENT_CLASS,CONTENT,DESCRIPTION,IN_DEPT_REPORT,IN_CHIEF_REPORT) -----定义列对应顺序 之后在CMD中执行sqlldr userid=system/asdfasdf control=input.ctl
返回
SQL*Loader: Release 10.1.0.2.0 - Production on 星期二 12月 9 05:28:12 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.
好像是成功了,但在日志文件中好多错误Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: ConventionalTable PE_DEPT_RESULT_ITEMS, loaded from every logical record.
Insert option in effect for this table: REPLACE Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PE_ID FIRST * WHT CHARACTER
PE_VISIT_ID NEXT * WHT CHARACTER
PE_DEPT_CODE NEXT * WHT CHARACTER
CONTENT_CLASS NEXT * WHT CHARACTER
CONTENT NEXT * WHT CHARACTER
DESCRIPTION NEXT * WHT CHARACTER
IN_DEPT_REPORT NEXT * WHT CHARACTER
IN_CHIEF_REPORT NEXT * WHT CHARACTER Record 1: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_ID.
Field in data file exceeds maximum length
Record 3: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 5: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 6: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_ID.
Field in data file exceeds maximum length
Record 7: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 8: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 9: Rejected - Error on table PE_DEPT_RESULT_ITEMS, column PE_VISIT_ID.
Column not found before end of logical record (use TRAILING NULLCOLS)Table PE_DEPT_RESULT_ITEMS:
0 Rows successfully loaded.
9 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: 132096 bytes(64 rows)
Read buffer bytes: 1048576Total logical records skipped: 0
Total logical records read: 9
Total logical records rejected: 9
Total logical records discarded: 0Run began on 星期二 12月 09 05:28:12 2008
Run ended on 星期二 12月 09 05:28:13 2008Elapsed time was: 00:00:00.87
CPU time was: 00:00:00.05
字段到底有什么问题呀?是不是什么工作没有做好呀?刚开始用,请指教?
一般而言,.CSV是以逗号隔开的。把
fields terminated by X'09' --4、字段终止于X'09',是一个制表符(TAB)
改成
fields terminated by X'2C' --4、字段终止于X'09',是一个制表符(TAB)
看下。X'2C'是逗号。
load data --1、控制文件标识
infile 'C:\pe_dept_result_items.csv' --2、要输入的数据文件名为lcolddata.xls
replace into table pe_dept_result_items --3、向表test中插入记录
fields terminated by ',' --4、字段间隔用','分开
( PE_ID,PE_VISIT_ID,PE_DEPT_CODE,CONTENT_CLASS,CONTENT,DESCRIPTION,IN_DEPT_REPORT,IN_CHIEF_REPORT)