ORACLE技术论坛–请到ask.orasos.com提问

Please consider registering
guest

Log In

Lost password?
Advanced Search:

— Forum Scope —



— Match —



— Forum Options —




Wildcard usage:
*  matches any number of characters    %  matches exactly one character

Minimum search word length is 4 characters - maximum search word length is 84 characters

Topic RSS
sqlldr 如何处理数据文件字段中的换行符?
2014 年 02 月 08 日
17:42
Guest

如入下面的数据

CARD_CODE,BUILDDEPT,BIRTHDAY,PROVICE,CITY,HOMEADDRESS,BABYBIRTHDAY,SEX
0517090724327,8004,20110817,,,江苏淮安开发,20110817,男
0250190213559,8002,19000101,,,"江苏省南京市秦淮区教师公寓10号门面

门面
门面",19000101,男
0551100325238,8006,20120715,安徽省,合肥市,hf,20120920,未知
0517001175472,8004,20090825,,,江苏淮安开发毛渡村,20090825,女

HOMEADDRESS列有换行符,我的控制文件如下:

$ cat a.ctl
load data
CHARACTERSET UTF8
infile *

into table "MEMBER_INFO"
append
fields terminated by ','
optionally enclosed by """
trailing nullcols
(
CARD_CODE,
BUILDDEPT,
BIRTHDAY,
PROVICE,
CITY,
HOMEADDRESS ,
BABYBIRTHDAY,
SEX
)
$

怎么处理?谢谢!

2014 年 02 月 08 日
22:33
惜分飞
Admin
Forum Posts: 341
Member Since:
2010 年 07 月 31 日
Online

对于有换行符的,尝试使用函数处理

 

COMMENTS "REPLACE(:COMMENTS,'\\n',CHR(10))"

 

因为换行是\n,你在sqlldr加载时需要替换为\\n

Q Q:107644445  

Tel:13429648788

个人Blog(惜分飞)

提供专业ORACLE技术支持(数据恢复,安装实施,升级迁移,备份容灾,故障诊断,系统优化等)

    

       
   

2014 年 02 月 09 日
16:50
Guest

$ ls
a.ctl xi.csv
[oracle@host011odb044 new]$ cat xi.csv
CARD_CODE,BUILDDEPT,BIRTHDAY,PROVICE,CITY,HOMEADDRESS,BABYBIRTHDAY,SEX
0517090724327,8004,20110817,,,江苏淮安开发,20110817,男
0250190213559,8002,19000101,,,"江苏省南京市秦淮区教师公寓10号门面
门面
门面",19000101,男
0551100325238,8006,20120715,安徽省,合肥市,hf,20120920,未知
0517001175472,8004,20090825,,,江苏淮安开发毛渡村,20090825,女

—-这里看到有4行数据,其中第二行字段中有换行符
[oracle@host011odb044 new]$ cat a.ctl
load data
CHARACTERSET UTF8
infile *

into table "MEMBER_INFO_TEST"
append
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(
CARD_CODE,
BUILDDEPT,
BIRTHDAY,
PROVICE,
CITY,
HOMEADDRESS "REPLACE(:HOMEADDRESS,'\\n',CHR(10))",
BABYBIRTHDAY,
SEX
)
[oracle@host011odb044 new]$ sqlldr gold/gold control=a.ctl data=xi.csv log=xi.log skip=1

SQL*Loader: Release 11.2.0.1.0 – Production on Sun Feb 9 16:39:44 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Commit point reached – logical record count 6
[oracle@host011odb044 new]$ ls
a.ctl xi.bad xi.csv xi.log
[oracle@host011odb044 new]$ cat xi.bad
0250190213559,8002,19000101,,,"江苏省南京市秦淮区教师公寓10号门面
[oracle@host011odb044 new]$ cat xi.log

SQL*Loader: Release 11.2.0.1.0 – Production on Sun Feb 9 16:39:44 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Control File: a.ctl
Character Set UTF8 specified for all input.

Data File: xi.csv
Bad File: xi.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table "MEMBER_INFO_TEST", loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
—————————— ———- —-- —- —- ———————
CARD_CODE FIRST * , O(") CHARACTER 
BUILDDEPT NEXT * , O(") CHARACTER 
BIRTHDAY NEXT * , O(") CHARACTER 
PROVICE NEXT * , O(") CHARACTER 
CITY NEXT * , O(") CHARACTER 
HOMEADDRESS NEXT * , O(") CHARACTER 
SQL string for column : "REPLACE(:HOMEADDRESS,'\n',CHR(10))"
BABYBIRTHDAY NEXT * , O(") CHARACTER 
SEX NEXT * , O(") CHARACTER

Record 2: Rejected – Error on table "MEMBER_INFO_TEST", column HOMEADDRESS.
second enclosure string not present

Table "MEMBER_INFO_TEST":
5 Rows successfully loaded.
1 Row 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: 1048576

Total logical records skipped: 1
Total logical records read: 6
Total logical records rejected: 1
Total logical records discarded: 0

Run began on Sun Feb 09 16:39:44 2014
Run ended on Sun Feb 09 16:39:45 2014

Elapsed time was: 00:00:00.13
CPU time was: 00:00:00.02
[oracle@host011odb044 new]$

导入数据库后如下:

SQL> select * from MEMBER_INFO_TEST;

CARD_CODE BUILDDEPT BIRTHDAY PROVICE CITY HOMEADDRESS BABYBIRTHD SEX
—————————— ———- ——-- —————————— —————————— —————————————- ———- ———-
0517090724327 8004 20110817 江苏淮安开发 20110817 男
门面 
门面" 19000101 男 
0551100325238 8006 20120715 安徽省 合肥市 hf 20120920 未知
0517001175472 8004 20090825 江苏淮安开发毛渡村 20090825 女

SQL>显然门面不在第一列

见表SQL:

CREATE TABLE aa(CARD_CODE varchar2(30), BUILDDEPT INT,BIRTHDAY VARCHAR2(8), PROVICE VARCHAR2(30), CITY VARCHAR2(30),HOMEADDRESS VARCHAR2(200), BABYBIRTHDAY int,SEX VARCHAR2(10));

 

shell匹配处理了数据文件,事情已经干完了,这纯属学习研究

评论功能已关闭。