Re: embedded newlines & sqlldr

From: <tornabene_at_my-deja.com>
Date: Mon, 05 Jul 1999 00:03:57 GMT
Message-ID: <7lospa$698$1@nnrp1.deja.com>

In article <7locvc$h44$1_at_nnrp03.primenet.com>, "Rob Calfee" <trace_at_primenet.com> wrote:
> Can you supply a sample of the data (flat file) and the sql loader
script
> you are using?

Certainly -- here it is.

This is the ctl script.

LOAD DATA
INFILE 'MAIL_INFO.dat'
INTO TABLE 'MAIL_INFO'
FIELDS TERMINATED BY "|"
OPTIONALLY ENCLOSED BY "'"
(

      user_id,
      letter_id,
      letter_title NULLIF letter_title = "NULL",
      mail_date DATE 'YYYY-MM-DD HH24:MI:SS' NULLIF mail_date = "NULL",
      num_copies,
      mail_flags NULLIF mail_flags = "NULL"

)

Here is some sample data. In this example, the sqlldr perceives the newline after www.foo.com in the third record as the logical end of the third record. It then perceives the next row (which is still part of the third record) as the beginning of the next record. I've set the error limit at 40, so after trying to reconcile 40 more rows, the loader quits.

1|2|Testing number one|1999-01-31 09:03:43|1|0|
2|2|Testing 9:35am|1999-01-31 09:33:41|1|0|
3|2|This http://www.foo.com

|1999-01-31 22:32:35|5|1|
2|2|Testing 9:35am|1999-01-31 09:33:41|1|NULL|

Any suggestions are appreciated!

Thanks a lot.

Catherine

tornabenenetscapenet put the @ and . where it seems to fit. :-)

> Rob Calfee
> trace_at_primenet.com

>
> tornabene_at_my-deja.com wrote in message
<7lmkka$jd6$1_at_nnrp1.deja.com>...
> >Hi all,
> >
> >I'm importing data from a Solid database to an Oracle 8.1.5 database
> >running on Solaris.
> >
> >A lot of my varchar data has embedded returns in the data. I cannot
> >simply get rid of the embedded returns because it contains formatting
> >information that I need.
> >
> >The sqlldr utility thinks that the embedded returns signify the end
of
> >the record and dies on those records, because it then thinks the new
> >line is the beginning of a new record, which is obviously wrong.
> >
> >Is there any way to escape embedded returns in varchars so the loader
> >doesn't think it's at the end of the record? Can I force it to count
> >columns before it looks for a new record (all columns are present in
> >every row).
> >
> >Thanks a lot!
> >
> >Catherine
> >
> >tornabenenetscapenet put the @ and . where it seems to fit. :-)
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Share what you know. Learn what you don't.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sun Jul 04 1999 - 19:03:57 CDT