Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 870
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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