Re: sqlldr question.

From: Adam Kurland <akurland_at_mindspring.com>
Date: Fri, 21 Dec 2001 21:32:30 -0800
Message-ID: <3C241AEE.3FCDBB3D@mindspring.com>

When loading data that has fields that may have whitespace, I typically use a tab-separated format for ALL fields.

Here is actual control file that I used to load news stories into a table with this in mind:

--news_increment.ctl
--SQL Loader control file for news_increment

load data
replace
into table tblnews_increment WHEN story_headline != 'New Story'

                    AND story_weight != BLANKS

TRAILING NULLCOLS

(story_id integer external terminated by X'09',
 story_weight integer external terminated by X'09',
 story_date DATE 'yyyy-mm-dd' terminated by X'09',
 story_headline char(1000) terminated by X'09',
 story_headhtml char(3000) terminated by X'09',
 story_lead char(100000) terminated by X'09',

 link_valid_string)

Note that the tab character has a HEX value of '09'. Makes things really easy.

-Adam

Martin Haltmayer wrote:

> Hi Jan,
>
> you are right. You can only specify one termination char or whitespace.
>
> I had a similar problem. I loaded my data into an auxiliary table, terminated by
> whitespace. I used only varchar2 columns for this auxiliary table. I offered as
> many columns as would be needed for the maximum number of fields in the input
> data. Then I used some batch sql for correcting the assignments of strings to my
> criteria.
>
> Regards,
>
> Martin
>
> Jan Eliasen wrote:
> >
> > Hi there.
> >
> > I have a datafile with lines like this;
> > 0080 A. C. Jacobsens Vej 5 34 9400 Norresundby
> >
> > 0080 is one field.
> > "A. C. Jacobsens Vej" is the second field.
> > The rest are one field a piece.
> >
> > I have the following control file;
> > LOAD DATA
> > INFILE 'RoadNames.dat'
> > REPLACE
> > INTO TABLE RoadNamesTemp
> > (Road_Code TERMINATED BY WHITESPACE,
> > Road_Name TERMINATED BY [0-9],
> > LowHouseNo TERMINATED BY WHITESPACE,
> > HighHouseNo TERMINATED BY WHITESPACE,
> > Postal_Code TERMINATED BY WHITESPACE,
> > Postal_District TERMINATED BY WHITESPACE)
> >
> > It does not work. The problem being of course the [0-9] thingy. What do I
> > do instead? I want the field to be terminated by a number - any number. I
> > can't find anything in the documentation. Can it really be so that you can
> > only terminate by a string, whitespace or a single character?
> >
> > Please help.
> >
> > --
> > Eliasen Jr.
Received on Fri Dec 21 2001 - 23:32:30 CST