Escape characters for SQLLoader

9. February 2010 18:49 by Mrojas in General  //  Tags: , , , ,   //   Comments (0)

 

The LINC/EAE migration tool can automatically generate reports that can be used to extract your data from DMSII to your target database, for example Oracle.
In this scenarios the Oracle SQL Loader tool is used. However you might problems loading the data because the string values can contain the same characters you are using to enclose them.

Let’s see an example, taken from an oracle forum:

C:\ora>type buyer.ctl
LOAD DATA
INFILE 'buyer.data'
truncate into table BUYER
FIELDS TERMINATED BY ',' optionally enclosed by '"' TRAILING NULLCOLS
(
buyer_code,
BUYER_NAME
)
 

And suppose you have data like:

1,"XYZ IND"
2,"ABC"
3,"XYZ ABC"
4,"Your "offspring""
5,"ATUL"

How can you “escape” the enclosing characters. Well I found the answer in another forum:

If two delimiter characters are encountered next to each other, a single occurrence of the delimiter character is used in the data value. For example, 'DON''T' is stored as DON'T. However, if the field consists of just two delimiter characters, its value is null.

So just use something like:

 

1,"XYZ IND"
2,"ABC"
3,"XYZ ABC"
4,"Your ""offspring"""
5,"ATUL"

Categories