Home » RDBMS Server » Server Utilities » SQLLDR Carriage Return
SQLLDR Carriage Return [message #416086] Thu, 30 July 2009 07:20 Go to next message
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 #416136 is a reply to message #416086] Thu, 30 July 2009 15:12 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
rdeekonda wrote on Thu, 30 July 2009 08:20

Please treat this as urgent.



Yes Sir!
Re: SQLLDR Carriage Return [message #416137 is a reply to message #416136] Thu, 30 July 2009 15:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Sir! Right away Sir!

*Runs around and knocks over stuff*
Re: SQLLDR Carriage Return [message #416328 is a reply to message #416086] Fri, 31 July 2009 15:06 Go to previous message
Barbara Boehmer
Messages: 8635
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> 




Previous Topic: Directory in Separate Server, External_Table
Next Topic: Change File Locations while Transport Tablespace
Goto Forum:
  


Current Time: Wed Dec 07 14:11:29 CST 2016

Total time taken to generate the page: 0.16198 seconds