How to load data with escaped characters in SQL*Loader

I want to load data dumped by Informix unload command. If the field delimiter or a newline is found in values, Informix unload will use escape '\'. How do I load it to Oracle? Does SQL*Loader recognize escaped characters? What other tools may I use?

For example, the following data should be one record in Oracle. Thanks.

1|this is a test\
 with newline in values
LVL 1
hakulaAsked:
Who is Participating?
 
WadhwaConnect With a Mentor Commented:
Hi hakula,
since The newline is in the middle of data for the second column and record delimiter is also the new line char , in this case it is not possible to load data  directly through sql*loader. You have make changes in the text  file to change the record delimiter  to some other char other then  "|" or "new line char".

Here you may find  some tool to load the data, you may try it. http://www.benthicsoftware.com/products.html
The name of the tool is GOLDLOAD


Goodluck
Sam
0
 
jkstillCommented:
I believe best choice is to clean up the data before loading it into Oracle.

A small Perl script is the perfect method for doing this.  Trying to load
this directly through SQL*Loader may not be possible.


0
 
WadhwaCommented:
Hi hakula
Is the record delimiter is also the new line char

Sam..
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
hakulaAuthor Commented:
Hi Sam,

In the example, the delimiter is '|'. The newline is in the middle of data for the second column.

Thanks.
0
 
WadhwaCommented:
Hi hakula,

I am asking not the field delimiter , i am asking about record delimiter

Waht is the delimeter between one record and the second record.

Sam..
0
 
hakulaAuthor Commented:
Sam,

yes, record delimiter is newline.
Thanks.
0
 
hakulaAuthor Commented:
Sam,

Thanks for your idea. Changing the record delimiter is the key to the problem, but your comment failed to mention how to change it. I figured out how to change the record delimiter myself.

Thanks again.
0
All Courses

From novice to tech pro — start learning today.