今天一哥们要导入客户信息,老是失败。我提供步骤如下:
1、首先建立表
2、将excel转换成csv格式
3、配置ctl配置文件如下:
load data
infile 'eosoperator.csv'
append into table eosoperator
fields terminated by "," optionally enclosed by "'"
trailing nullcols
(OPERATORID,USERID,PASSWORD,OPERATORNAME,ISLOCAL,STATUS,DELFLAG,OPERATORCODE)
------------------------------------- excel格式和以上配置对应------------------------------------------------------------
1623 | 程志红 | Q3qtuxGsI7B5u60nFMu+qA== | 程志红 | Y | 1 | 0 | 0 |
1624 | 靳慧 | Q3qtuxGsI7B5u61nFMu+qA== | 靳慧 | Y | 1 | 0 | 0 |
1625 | 云晓红 | Q3qtuxGsI7B5u62nFMu+qA== | 云晓红 | Y | 1 | 0 | 0 |
1626 | 蔡玮琳 | Q3qtuxGsI7B5u63nFMu+qA== | 蔡玮琳 | Y | 1 | 0 | 0 |
1627 | 郭保文 | Q3qtuxGsI7B5u64nFMu+qA== | 郭保文 | Y | 1 | 0 | 0 |
1628 | 刘玉璞 | Q3qtuxGsI7B5u65nFMu+qA== | 刘玉璞 | Y | 1 | 0 | 0 |
1629 | 袁红雁 | Q3qtuxGsI7B5u66nFMu+qA== | 袁红雁 | Y | 1 | 0 | 0 |
1630 | 陈慧芳 | Q3qtuxGsI7B5u67nFMu+qA== | 陈慧芳 | Y | 1 | 0 | 0 |
1631 | 赵习亮 | Q3qtuxGsI7B5u68nFMu+qA== | 赵习亮 | Y | 1 | 0 | 0 |
1632 | 李红 | Q3qtuxGsI7B5u69nFMu+qA== | 李红 | Y | 1 | 0 | 0 |
1633 | 侯康生 | Q3qtuxGsI7B5u70nFMu+qA== | 侯康生 | Y | 1 | 0 | 0 |
1634 | 连兴国 | Q3qtuxGsI7B5u71nFMu+qA== | 连兴国 | Y | 1 | 0 | 0 |
4、导入:E:/>sqlldr userid=tssa/tssa2006@sxtsioms_133.128.4.119 control=input.ctl
哥们按照如上步骤处理报错。我也纳闷中,我以前可以啊,于是通过远程看同事一步步做,原来是他把.xls文件直接改成.csv(需要另保存才可)
注:如果有时间的要如下设置
load data
infile 'ycemployee.csv'
append into table eosorg_t_employee
fields terminated by "," optionally enclosed by "'"
trailing nullcols
(OPERATORID,EMPCODE,ORGID,POSITIONID,OTEL1,OTEL2,OEMAIL,MOBILENO,GENDER,BIRTHDATE Date "YYYY-MM-DD", JOIN_WORK_TIME,FIRST_SCHOOLAGE,END_SCHOOL_AGE,GRADUATE_DATE,GRADUATE_SCHOOL,MAJOR)