Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to handle carriage return/linefeed embedded in text field as first char

751641Feb 4 2010 — edited Feb 5 2010

Hi,

I am trying to use sqlldr to import text that is comma-separated and double quote-enclosed, but contains embedded carriage return/linefeed pairs. I have a control file which works for most cases and looks like this:

LOAD DATA
INFILE './foo.new'
APPEND
CONTINUEIF LAST PRESERVE (<>'"')
INTO TABLE <tablename>
FIELDS TERMINATED BY "," ENCLOSED BY '"'
TRAILING NULLCOLS

Unfortunately, this fails when the very first character of the text field is a carriage return/linefeed pair, e.g.,:

"11","22","33","\r\nThis is the fourth field","","","","",""

In that case, what is actually the opening double-quote for the fourth field, is interpreted by the CONTINUEIF clause as the end of that line. Can anyone please point me to a solution?

thanks.
stan.