need help importing to LONG field

I have an Microsoft Access table that I need to import into Oracle. (7.3) I've created routines that create a control file for SQL Loader and exports the Access data into an text file.

My problem is the table has one formatted text (memo) field that contains carriage returns/linefeeds in the field. So one record of the text file will look like this:

field1 = "11111"
field2 = "22222"
field3 = "This is field three (cr/lf) this is line two (cr/lf) etc."

The actual exported text file will look like this:

1111122222This is field three
this is line two
etc

Now to SQL Loader, this looks like 3 physical records. So I have to use CONTINUEIF in the control file to merge field 3 back to one field and the 3 lines back to one logical record in Oracle. However the fly in the ointment is SQL Loader strips the line feed/carriage returns in my 3rd field, resulting in a single long string which is not the desired result. The line feeds are part of the original field and need to be preserved.

I've tried adding a position holder marker ("~~") for the cr/lf and importing
the text file and then using a SQL procedure to do a REPLACE on the marker, but that takes forever.( i.e. 300 records/3mb text file SQL Loader import ~ 2min , REPLACE procedure 45 mins and still running before I killed it.)

Does anybody have any suggestions? I'm open to anything. Even other approaches.

• Cris

  ---

  mailto:cbrady_at_alpinenet.com software with altitude! http://www.alpinenet.com alpine enterprise, ltd. Microsoft Solution Provider Microsoft Certified Systems Engineer