ORACLE sqlldr ctl导入数据
ORACLE sqlldr ctl导入数据
oracle ctl 文本数据导出导入实现(环境 oracle 9I 9.0.2)
bat 部分(通用部分)
cd Y:\1
sqlplus mobilenew@oral/password @Y:\1\6.sql
sql 部分 (导出)
set time off echo off head off;
set heading off
set term off
set pagesize 0
set colsep "," ---对导出数据相关信息列后以 逗号分隔
set trims on
set feedback off
set linesize 1200
spool Y:\1\辽宁号码.txt
select to_char(d.phonenumber),
d.易做图,
d.age,
d.customerstatus,
d.bookstatus,
to_char(d.begintime,'yyyy-mm-dd hh24:mm:ss'),
to_char(d.endtime,'yyyy-mm-dd hh24:mm:ss'),
to_char(d.ordertime,'yyyy-mm-dd hh24:mm:ss'),
d.timelength,
d.callstatus,
d.callbusinessid
from y_customer_info d;
spool off
/
quit
/
注意 以下部分为ctl导入文件写入方法 上面写法为sql写法
导入部分
load data
infile 'E:\C号码导入\textC\辽宁号码.txt'
append into table Y_CUSTOMER_INFO_TEMP_LAOLING1
fields terminated by ',' --此处针对 逗号分隔符以处理数据.
TRAILING NULLCOLS
( PHONENUMBER,
SEX,
AGE ,
CUSTOMERSTATUS,
BOOKSTATUS ,
BEGINTIME,ENDTIME,
ORDERTIME ,TIMELENGTH,CALLSTATUS,CALLBUSINESSID)
excel 数据导入 oracle 以CVS格式导入
示例：
test.ctl 写法如下
Load data
infile 'd:/1/1.csv'
Append into table test
fields terminated by ','
(d,a)
批处理bat写法
sqlldr userid=mobilenew/password@orcl control='d:\1\test.ctl' log=d:\1\log.txt bad=d:\1\bad.txt
csv 文件导入时 ,表类型为date类型导入列子
---表结构
create TABLE FJPHONE
(
ITEMNAME VARCHAR2(50) NOT NULL,
PHONENUMBER VARCHAR2(20)NOT NULL,
CITY VARCHAR2(10) default ' ',
AREA varchar2(10) default ' ',
BRAND VARCHAR2(20) default ' ',
JOBID VARCHAR2(20) default ' ',
CALLSTATS VARCHAR2(20) default ' ' ,
TIMELENGTH VARCHAR2(20) default ' ',
ORDERTIME date default sysdate //时间列
)
ctl 文件写法如下:
load data
infile 'E:\4\副本Xl0000047.csv'
append into table fjphone
fields terminated by ','
TRAILING NULLCOLS
(ITEMNAME,PHONENUMBER,CITY,AREA,BRAND,JOBID,CALLSTATS,TIMELENGTH,ORDERTIME"to_date(:ordertime,'''yyyy-mm-dd''')")
/*在所需导入到列后加入"to_date(:ordertime,'''yyyy-mm-dd''')" */
