Re: generating flat files with no blank caracters

From: Mark D Powell <mark.powell_at_eds.com>
Date: 26 Oct 2001 07:08:04 -0700
Message-ID: <178d2795.0110260608.7a3103fa@posting.google.com>

Raphael Duenas <raf_at_srd.fr> wrote in message news:<3BD6D315.C03BE205_at_srd.fr>...
> Mark D Powell a écrit :
>
> > Raphael Duenas <raf_at_srd.fr> wrote in message news:<3BD6641B.5C73A57B_at_srd.fr>...
> > > Ed Prochak a écrit :
> > >
> > > > Please give more details. SQL Loader can ignore blanks easily. so what
> > > > really is the problem??
> > >
> > > An example of a line to load :
> > > " 9| 10| 2| 4|"
> > >
> > > it's the look of my flat file, so when i want to load this, sqloader tell me
> > > that the numbers colums are not valid ORA-1722 not a valid number.
> > > I want only unload a part or the totality of a table for load it into an other
> > > database.
> > >
> > > >
> > > >
> > > > Raphael Duenas wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > I want to generate a flat file so i can use it for partial loads on over
> > > > > databases, but when i do it with sqlplus, number's columns are completed
> > > > > with blank caracters and the file can't be use with sqloader. So how i
> > > > > should do to get a no blank car file.
> > > > >
> > > > > thanks
> > > > >
> > > > > Raphaël
> > > > > Dijon/FRANCE
> >
> > Question: did you define the file to sqlldr as fixed format or delimited by '|'?
> > It would have probably helped if you had posted the sqlldr control statements.
>
> Sure !
>
> LOAD DATA
> INFILE noy_menu.unl
> INTO TABLE noy_menu REPLACE
> FIELDS TERMINATED BY '|'
> TRAILING NULLCOLS
> (
> C_PLAT
> , C_DESTINATION
> , C_REPAS
> , C_REGIME
> , D_MENU DATE "DD/MM/YYYY"
> , T_PLAT
> , N_POSITION
> , B_EDITION
> , C_PLATEAU
> )
>
> noy_menu :
>
> 19| 1| 3| 31|24/10/01|V| 99|
> 19| 1| 3| 14|24/10/01|V| 99|
> 19| 1| 3| 15|24/10/01|V| 99|
>
> >
> >
> > -- Mark D Powell --

Question: if this is a delimited file why are there unnecessary blanks in it. Normally the file would look like 19|1|3|31|24/10/01/V|99.

Also the control card says there are 9 columns in the data but I only see 7 in the sample. Was the data cut off or are you missing 2 columns and need trailing || which worked for me:

LOAD DATA
infile *
INTO TABLE mpowel01.marktest2 replace
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
C_PLAT

 , C_DESTINATION
 , C_REPAS
 , C_REGIME
 , D_MENU DATE "DD/MM/YYYY"
 , T_PLAT
 , N_POSITION
 , B_EDITION
 , C_PLATEAU

)
begindata

19| 1| 3| 31|24/10/01|V| 99|||

SQL*Loader: Release 8.1.7.2.0 - Production on Fri Oct 26 10:04:07 2001

Commit point reached - logical record count 1

UT1> select * from marktest2;

C_PLAT C_DESTINATION C_REPAS C_REGIME D_MENU T N_POSITION B_EDITION
---------- ------------- ---------- ---------- --------- - ----------

C_PLATEAU

19 1 3 31 24-OCT-01 V 99

Mark D Powell --

Received on Fri Oct 26 2001 - 09:08:04 CDT