Re: sqlldr question: How to set control file to load variable length data?

From: <pmohanan_at_my-deja.com>
Date: 2000/07/21
Message-ID: <8la6fc$q69$1@nnrp1.deja.com>#1/1

Hi...,

You have to tell SQLLDR that there will be some missing data. The way to do it is

LOAD DATA
INFILE dept.csv
INTO TABLE dept

        FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
        TRAILING NULLCOLS
.......

.......
.....

Hope this works. (it worked for me).

Regards,

Praveen

In article <39775dfe.24816604_at_news>,
See Message body for real address wrote:
>
> The systax looks ok, but here is one from the example:
>
> LOAD DATA
> INFILE dept.csv
> INTO TABLE DEPT
> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> (DEPTNO, DNAME, LOC)
>
> be sure that you have data for each field specified by the ctl
file....
> if one row has
> Col1,col2,col3 and
> another has only
> col1,col2
>
> the error will be similar to the one you received...check your
data...AFAIK,
> there is no way to tell sqlloader to ignore this...
>
> gmei_at_my-deja.com wrote:
>
> >Hi:
> >
> >I am trying to use sqlldr to load some text data into oracle db
without
> >much success.
> >
> >The text file looks something like this:
> >
> >1234567,ABC,999,aaa
> >5678,xy,this is col3,b
> >....
> >
> >Basically it is a variable length fields with comma as separator. I
> >have no problem using sqlldr to load fixed length data. But with
> >variable length data, I tried several ways in control file such as:
> >
> >LOAD DATA
> >INFILE 't1.dat'
> >INTO TABLE T1
> >FIELDS TERMINATED BY ','
> >(C1,
> > C2,
> > C3,
> > C4)
> >
> >and
> >
> >LOAD DATA
> >INFILE 't1.dat'
> >INTO TABLE T1
> >FIELDS TERMINATED BY ','
> >(C1 char(12),
> > C2, char(3),
> > C3, char(20)
> > C4 char(5))
> >
> >and none of them worked. They always gave me Sql*loader-524 error:
> >partial record found at end of datafile(t1.ctl). I also tried in
> >file "t1.dat" to make sure the last record has extra carriage return.
> >It does not seems to help.
> >
> >I am suspecting that there might be something wrong in my control
file
> >syntax. Anyone know the correct syntax in control file to load my
data?
> >Thanks.
> >
> >Guang
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jul 21 2000 - 00:00:00 CDT