Oracle Sqlldr optionally enclosed by(GCH)
创新互联专注为客户提供全方位的互联网综合服务，包含不限于成都网站设计、做网站、高平网络推广、重庆小程序开发、高平网络营销、高平企业策划、高平品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；创新互联为所有大学生创业者提供高平建站搭建服务，24小时服务热线：13518219792，官方网址：www.cdcxhl.com
In all, when you edit the control file,you would rather add theclause “optionally enclosed by”inensurance.
OPTIONS(skip=1)
LOADDATA
INFILE"/home/oracle/sqlldr/MB_PERFORMANCE_TD_CELL.csv"
APPENDINTO TABLE MB_PERFORMANCE_TD_CELL
Fieldsterminated by ","
trailingnullcols
(EVENT_DAYtimestamp "yyyy-mm-dd hh34:mi:ss",
HOUR_P,
CITY_NAME,
COUNTRY_NAME,
VENDOR,
CELL_NAME,
CGI,
RLC_THRPUT ,
UTRAN1_NUM,
UTRAN2_NUM ,
RECORD_TIME"to_timestamp(to_char(sysdate,'yyyymmdd hh34:mi:ss'),'yyyymmddhh34:mi:ss')",
VERSION_ID constant "3"
)
[oracle@daidaisqlldr]$ sqlldr daidai/love8013control=MB_PERFORMANCE_TD_CELL.CTL
SQL*Loader:Release 11.2.0.4.0 - Production on Tue Jun 14 22:35:52 2016
Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commitpoint reached - logical record count 64
Commitpoint reached - logical record count 128
Commitpoint reached - logical record count 192
Commitpoint reached - logical record count 256
Commitpoint reached - logical record count 320
Commitpoint reached - logical record count 384
Commitpoint reached - logical record count 448
Commitpoint reached - logical record count 512
Commitpoint reached - logical record count 576
Commitpoint reached - logical record count 640
Commitpoint reached - logical record count 704
Commitpoint reached - logical record count 768
Commitpoint reached - logical record count 832
Commitpoint reached - logical record count 896
Commitpoint reached - logical record count 960
Commitpoint reached - logical record count 1024
……
Record96: Rejected - Error on table MB_PERFORMANCE_TD_CELL, column UTRAN2_NUM.
ORA-01722:invalid number
Record119: Rejected - Error on table MB_PERFORMANCE_TD_CELL, column UTRAN2_NUM.
ORA-01722:invalid number
Record126: Rejected - Error on table MB_PERFORMANCE_TD_CELL, column UTRAN2_NUM.
ORA-01722:invalid number
…….
From the import log, it is the conclusionthat some were imported successfully. I select from database. Samples are asfollows:
Therefore, the columns in the control filecorrespond to th
e columns of goal table. However, there were still a fewrecords that were not imported into table.
[oracle@daidaisqlldr]$ cat MB_PERFORMANCE_TD_CELL.bad
2016-06-0700:00:00,,达州,渠县,华为,SDZ6NR5：渠县涌兴_2,460-00-61844-12098,,,
2016-06-0700:00:00,,内江,内江归属未知,华为,SNJ6NR2DX：田家十中_1,460-00-61811-34907,,,
2016-06-0700:00:00,,资阳,资阳归属未知,华为,SZY6NR3：乐至大佛二站_1,460-00-61889-40577,,,
2016-06-0700:00:00,,资阳,资阳归属未知,华为,SZY4NR3：乐至仲良中学_1,460-00-61889-35687,,,
2016-06-0700:00:00,,内江,内江归属未知,华为,SNJ5NR5WY：城北_3,460-00-61812-36239,,,
2016-06-0700:00:00,,达州,宣汉县,华为,SDZ6NR3：宣汉普光105_1,460-00-61841-24707,,,
2016-06-0700:00:00,,达州,渠县,华为,SDZ6NR5：渠县望江庭_1,460-00-61844-60867,,,
Then, I think of keyword ‘optionallyenclosed by’.
cat MB_PERFORMANCE_TD_CELL.CTL
OPTIONS(skip=1)
LOAD DATA
INFILE"/home/oracle/sqlldr/MB_PERFORMANCE_TD_CELL.csv"
APPEND INTO TABLE MB_PERFORMANCE_TD_CELL
Fields terminated by "," optionlly enclosed by " "
trailing nullcols
(EVENT_DAY timestamp "yyyy-mm-ddhh34:mi:ss",
HOUR_P,
CITY_NAME,
COUNTRY_NAME,
VENDOR,
CELL_NAME,
CGI,
RLC_THRPUT ,
UTRAN1_NUM,
UTRAN2_NUM ,
RECORD_TIME"to_timestamp(to_char(sysdate,'yyyymmdd hh34:mi:ss'),'yyyymmddhh34:mi:ss')",
VERSION_ID constant "3"
)
Import data successfully.
SQL> select count(*) fromMB_PERFORMANCE_TD_CELL
2 ;
COUNT(*)
----------
50908
