Oracle Sqlldr optionally enclosed by(GCH)
站在用户的角度思考问题,与客户深入沟通,找到崇义网站设计与崇义网站推广的解决方案,凭借多年的经验,让设计与互联网技术结合,创造个性化、用户体验好的作品,建站类型包括:网站设计、成都做网站、企业官网、英文网站、手机端网站、网站推广、域名注册、网络空间、企业邮箱。业务覆盖崇义地区。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
另外有需要云服务器可以了解下创新互联cdcxhl.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。