Re: How to preserve newlines with SQL*Loader?

From: John Peterson <johnp_at_azstarnet.com>
Date: Wed, 16 May 2001 11:51:24 -0700
Message-ID: <tg5j1dfe1on95a@corp.supernews.com>

Thanks to some help from email, I think I'm getting closer with this. However, I still have a couple of questions:

• If I try and process the file in "stream record format" and specify a delimiter of X'3B0A' (semicolon/newline), then it appears to preserve *most* of the embedded newlines. However, if I have any *other* things in the file (like comments), then the file gets processed "weirdly" (misses valid lines).
• If I try and process this file "normally" (using the standard newlines as a physical record delimiter), then I have some trouble with CONTINUEIF LAST != ';' in conjunction with WHEN (1) = '$' (in the appropriate places) if there are comments in the file.

I like the first technique, but I really wish there were some way to say "only start processing when you hit a '$' character and go to the end of record delimiter (semicolon/newline).

Or, the second technique would work if I could say something like "ignore all lines that don't start with a '$' character, but, once that character is detected, process everything verbatim until the ';' character.

I would appreciate any suggestions/ideas! Thanks!

The following is an example of my data file.

• Test.DAT begin ----- # This is a comment.

${'Line1'} = 'This is a test.';
${'Line2'} = 'This is a test with some

carriage returns.

How do these get preserved?
';
${'Line3'} = 'This is the last line.';

# This is another comment.
----- Test.DAT end -----

"John Peterson" <johnp_at_azstarnet.com> wrote in message news:tg2d5i25kk8mbb_at_corp.supernews.com...

> Hmmm...maybe newlines can't be preserved, per se, since the newline is
> interpreted as an end-of-physical-record delimiter.  Is there any way to
> *reintroduce* a newline for every continued line?
>
> I would truly appreciate any help.  Thanks!  :-)
>
> John Peterson
>
> "John Peterson" <johnp_at_azstarnet.com> wrote in message
> news:tg11gmbcbehv0c_at_corp.supernews.com...
> > (Oracle 8.1.7 on Win2K)
> >
> > Hello, all!
> >
> > I've been struggling with a control file specification for SQL*Loader

 and
 am

> > *close* to defining it with the exception of one issue:  preserving
> > "embedded" newlines/carriage returns.
> >
> > Consider the following table structure:
> >
> >
> > create table Test (Field1 VARCHAR2(255)  NOT NULL,
> >                    Field2 VARCHAR2(4000) NOT NULL);
> >
> >
> > My control file looks like:
> >
> >
> > ----- Control File Start -----
> > OPTIONS (ERRORS=999999999)
> >
> > LOAD DATA
> > INFILE      *
> > BADFILE     '.\test.bad'
> > DISCARDFILE '.\test.dis'
> >
> > TRUNCATE
> > CONTINUEIF LAST != ';'
> >
> > INTO TABLE Test
> > WHEN (1) = '$'
> > TRAILING NULLCOLS
> > (
> > Field1 CHAR(255)  ENCLOSED BY "${'" AND "'} = ",
> > Field2 CHAR(4000) ENCLOSED BY "'"   AND "';"
> > )
> >
> > BEGINDATA
> > ${'Line1'} = 'This is a test.';
> > ${'Line2'} = 'This is a test with some
> >
> > carriage returns.
> >
> > How do these get preserved?
> > ';
> > ${'Line3'} = 'This is the last line.';
> > ----- Control File End -----
> >
> >
> > This will seemingly successfully run, however when I execute the

 following

> > to see which records have embedded newlines:
> >
> >
> > select *
> >   from Test
> >  where (instr(Field2, chr(10)) > 0 or instr(Field2, chr(13)) > 0);
> >
> >
> > No records are return, making me suspicious that embedded newlines were

 not

> > preserved.  From what I've read of ENCLOSED BY, it *should* embed the
> > newlines/linefeeds.
> >
> > I would greatly appreciate any help/suggestions!  Thanks!  :-)
> >
> > John Peterson
> >
> >
>
 

Received on Wed May 16 2001 - 13:51:24 CDT