Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 882
  • Last Modified:

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
0
hakula
Asked:
hakula
  • 3
  • 3
1 Solution
 
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
 
hakulaAuthor Commented:
Hi Sam,

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

Thanks.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
WadhwaCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now