Re: SqlLoader: record delimiter other than newline?

Hi,

SQLLoader allows to specify any string as record delimiter. Use the "str" option for this. For example:

EMP table was exported with #### as record delimiter. Then the .ctl file is:

OPTIONS(direct=false)
LOAD DATA
INFILE 'emp.txt' "str '####'"
INTO TABLE EMP
INSERT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (EMPNO,
 ENAME,
 JOB,
 MGR,
 HIREDATE DATE "YYYY-MM-DD HH24:MI:SS",
 SAL,
 COMM,
 DEPTNO) Enjoy SQLLoader :)

Best regards, Dmitry

--
http://www.ispirer.com/products - Database migration software for IBM DB2,
Oracle, SQL Server, Sybase, Informix and MySQL.


"Phil Currier" <gg.20.pholius_at_spamgourmet.com> wrote in message
news:41b0e711.0301161408.3bbc82d5_at_posting.google.com...


> Hi,

>

> I'm attempting to load data from flat files in which the data fields


> can occasionally contain newline (\n) characters.  Though I can change

> the field delimiter, I haven't found a way to change the record

> delimiter.  The "terminated by" and "enclosed by" clauses don't seem

> to work across newlines; I can't treat the record as multiple records

> and combine them with CONCATENATE or CONTINUEIF since there can be

> arbitrary numbers of newlines in a given field, with no clear way to

> identify them from the rest of the record.

>

> Does anyone know a good way to change sqlldr's idea of the record

> delimiter?  Is there a way to "escape" characters in a datafile?  My

> current workaround kludge is to replace the embedded newlines with a

> nonprinting character in each file, load the records, then use

> replace() to update the fields afterwards.  However, I'd rather get

> sqlldr to be a little smarter.  If it matters, I'm running on Sun

> using 8.1.7.

>

> Thanks,

> Phil