Re: sqlldr - ignore columns?

"Russell England" <russ_at_css2.com> wrote in message news:JJOp5.2857$NR3.435877_at_news1.cableinet.net...
> I have several csv files that I want to load, but the csv files have some
> columns that I want to ignore:
>
> LOAD DATA
> INFILE *
> INTO TABLE mytable
> FIELDS TERINATED BY "," OPTIONALLY ENCLOSED BY '"'
> (Field1,Field2,,Field3,Field4)
> BEGINDATA
> "keep","keep","lose","keep","keep"
> "keep","keep","lose","keep","keep"
> ...
>
> How can I ignore the middle column?

Have a look at this:

LOAD DATA
INFILE '$HOME/LABS/ulcase2.dat'
INTO TABLE system.EMP

( EMPNO    POSITION(01:04) INTEGER EXTERNAL,
  ENAME    POSITION(06:15) CHAR,
  JOB      POSITION(17:25) CHAR,
  MGR      POSITION(27:30) INTEGER EXTERNAL,
  SAL      POSITION(32:39) DECIMAL EXTERNAL,
  COMM     POSITION(41:48) DECIMAL EXTERNAL,
  DEPTNO   POSITION(50:51) INTEGER EXTERNAL)

... in other words, you have an external text file containing the data, and your control file is being used to interpret that file. Now it should be fairly obvious that if you wanted to skip the data at positions 17 to 25, for example, you simply omit those positions in the loading template:

LOAD DATA
INFILE '$HOME/LABS/ulcase2.dat'
INTO TABLE system.EMP

( EMPNO    POSITION(01:04) INTEGER EXTERNAL,
  ENAME    POSITION(06:15) CHAR,
  MGR      POSITION(27:30) INTEGER EXTERNAL,
  SAL      POSITION(32:39) DECIMAL EXTERNAL,
  COMM     POSITION(41:48) DECIMAL EXTERNAL,
  DEPTNO   POSITION(50:51) INTEGER EXTERNAL)

On the other hand, this advice only applies for fixed-width data (where you can guarantee that positions 17 to 25 always contain the data you want to skip) -and since you have csv's, this advice is not going to be of much direct help. Hopefully it might give you some ideas, though. How much data are we talking about?

Regards
HJR
>
> Thanks in advance
>
> Russ
>
>
Received on Sat Aug 26 2000 - 17:12:23 CDT