| SQLLDR Carriage Return [message #416086] |
Thu, 30 July 2009 07:20  |
rdeekonda
Messages: 1
Registered: July 2009
|
Junior Member |
|
|
Hi All,
I have a requirment to load the data in a temp table. But in the data file, for perticular column data having carriage return. Can you please help me to resolve this issue. Below is the table structure, ctl and Data file.
CREATE TABLE REMOVE_CR_TEMP
(
ROW_ID VARCHAR2(15),
COMMENTS VARCHAR2(2000),
COMMENTS1 VARCHAR2(100)
)
--------------------------------------
LOAD DATA
APPEND
INTO TABLE remove_cr_temp
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
ROW_ID,
COMMENTS "replace(replace(:COMMENTS,chr(13),' '),chr(10),' ')",
COMMENTS1
)
-----------------------------------------------------
1|testrecord1|TEST
2|testrecord2|TES2
3|testrecord3|TEST3
4|testrecord with
issue 1|TEST4
5|test record with
issue2|TEST5
6|test record with
issue3|TEST6
When I run this ctl file data is not loading properly.
Please treat this as urgent and help me to resolve this issue.
Thanks,
Ravi
[Updated on: Thu, 30 July 2009 07:24] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQLLDR Carriage Return [message #416328 is a reply to message #416086] |
Fri, 31 July 2009 15:06  |
 |
Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You have to provide something that can be used to identify when a record starts or ends. Then you can use CONTINUEIF to tell it whether the next row is a continuation of the current record or begins a new record. For example, if there is always a | in the second column of each new record and not in any continuing record, then you can load it as demonstrated below. It will even eliminate the carriage returns for you, but unfortunately will not replace them with a space, at least in some versions. I suspect that by simplifying your data, you may have eliminated anything that might actually be used to identify record beginnings or endings.
-- test.dat:
1|testrecord1|TEST
2|testrecord2|TES2
3|testrecord3|TEST3
4|testrecord with
issue 1|TEST4
5|test record with
issue2|TEST5
6|test record with
issue3|TEST6
-- test.ctl:
LOAD DATA
INFILE test.dat
APPEND
CONTINUEIF NEXT PRESERVE (2:2) <> "|"
INTO TABLE remove_cr_temp
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
ROW_ID,
COMMENTS,
COMMENTS1
)
SCOTT@orcl_11g> CREATE TABLE REMOVE_CR_TEMP
2 (
3 ROW_ID VARCHAR2(15),
4 COMMENTS VARCHAR2(2000),
5 COMMENTS1 VARCHAR2(100)
6 )
7 /
Table created.
SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11g> COLUMN comments FORMAT A25
SCOTT@orcl_11g> COLUMN comments1 FORMAT A10
SCOTT@orcl_11g> SELECT * FROM remove_cr_temp
2 /
ROW_ID COMMENTS COMMENTS1
--------------- ------------------------- ----------
1 testrecord1 TEST
2 testrecord2 TES2
3 testrecord3 TEST3
4 testrecord withissue 1 TEST4
5 test record withissue2 TEST5
6 test record withissue3 TEST6
6 rows selected.
SCOTT@orcl_11g>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
