If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > SQLLDR Carriage Return

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 3
SQLLDR Carriage Return

Hi Gurus,

Thanks for all your help in resolving my issues.

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
carriage return|TEST4
5|test record with
carriage return2|TEST5
6|test record with carriage
return3|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
Reply With Quote
  #2 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 4,002
CONTINUEIF is the keyword, I believe.

Here's an example; I have created a table as per your example. Input data is contained in the control file (after the BEGINDATA keyword). I have replaced your "with carriage return" text with, well <Carriage return>.

This is the control file:
Code:
LOAD DATA
INFILE * 
REPLACE

CONTINUEIF NEXT PRESERVE (1) = "|"

INTO TABLE remove_cr_temp
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(ROW_ID,
 COMMENTS,
 COMMENTS1
)

BEGINDATA
1|testrecord1|TEST1
2|testrecord2|TEST2
3|testrecord3|TEST3
4|testrecord4
|TEST4
5|testrecord5
|TEST5
6|testrecord6
|TEST6
Loading session:
Code:
SQL> $sqlldr scott/tiger@ora10 control=tctl.ctl log=tctl.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sri Srp 29 14:45:51 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 5
Commit point reached - logical record count 6
Result (I have formatted columns so that they would fit into a single record):
Code:
SQL> select * from remove_cr_temp;

ROW_ID          COMMENTS             COMMENTS1
--------------- -------------------- --------------------
1               testrecord1          TEST1
2               testrecord2          TEST2
3               testrecord3          TEST3
4               testrecord4          TEST4
5               testrecord5          TEST5
6               testrecord6          TEST6

6 rows selected.

SQL>
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 3
Hi Littlefoot,

Thanks for quick reply. Carriage return is a value in the data file, Let me change the data file.

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

Could you please give me a solution for this.

Thanks,
Ravi
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2004
Location: Dominican Republic
Posts: 721
You may want to "cure" your datafile first. For example, make it so every line in your datafile starts specificly with a |. Then you can use CONTINUEIF as Littlefoot specified.

If using unix/linux, you can use sed to add a specific char to every "record" in your datafile, or UltraEdit/gVim if on Windows (assuming they always start with a number)
Code:
sed 's/^\([0-9]\{1,\}\)/\|\1/g' yourdatafile
Your sample data, as given here, would then look like below

|1|testrecord1|TEST
|2|testrecord2|TEST2
|3|testrecord3|TEST3
|4|testrecord with
issue 1|TEST4
|5|test record with
issue2|TEST5
|6|test record with
issue3|TEST6

Then, you can use a controlfile like this to load this data.
Code:
LOAD DATA
INFILE yourdatfile
REPLACE
CONTINUEIF NEXT PRESERVE (1:1) != "|"
INTO TABLE remove_cr_temp
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
  DUMMY		FILLER,
  ROW_ID,
  COMMENTS,
  COMMENTS1
)
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 3
Thanks for your help... This solve my issue.

I have a small doubt on REPLACE. When we can remove carriage return by using REPLACE(COL_NAME, CHR(13)||CHR(10),' ') in SQL, why this is not working in SQLLDR.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On