SQL Loader: How to skip fields?

[Christoph Meyer]

Hi!

I'm trying to load a data file with each record containing 3 fields (col_A,
col_B, col_C) using SQL*Loader.
The table in the database, however, contains 2 fields only (field1, field2)
What's the syntax for the loader script to load:
col_A into field1,
col_C in field2 and
skip col_B?

Any suggestions? Thanks in advance.
Christoph Meyer

---
Please remove [nospam] before replying.

[Doug Dahlke]

If the extra fields are at the end, don't include them in the control file. It
will load fine. If the field is in the middle, my dba loads that into a temp
table, then copies the good fields to the correct table and then dumps the
table. I couldn't find a 'skip field' notation in sqlldr. It will ignore
extra data on the end past your last defined field.

Doug

[klin...@epix.net]

Hi,
If you define the actual positions of the two colums you want to include
you don't have to specify the third column and it won't be loaded.

ex
APPEND
INTO TABLE Account
(bank_nbr POSITION(01:03) INTEGER EXTERNAL
,product_type_cd POSITION(04:05) CHAR
,account_nbr POSITION(06:15) INTEGER EXTERNAL
)

APPEND
INTO TABLE Account
(bank_nbr POSITION(01:03) INTEGER EXTERNAL
,account_nbr POSITION(06:15) INTEGER EXTERNAL
)
This shuld work fine without using temporary tables as long as you can
make the files fixed length.

Bill

[Jeff Price]

What if the data is delimited, not fixed?

<klin...@epix.net> wrote in message news:38867FB2...@epix.net...

[Steve Haynes]

In article <86smk1$q8s$1...@charm.magnus.acs.ohio-state.edu>, Jeff Price
<jpr...@osu.edu> writes

>What if the data is delimited, not fixed?
>

then no.
maybe load to a temp table and move the cols you want?
Steve

[Tim Cope]

You can specify the column as a FILLER and sqlldr will skip that column.
For example:

INTO TABLE MYTEST
(
col1 CHAR(20),
col2 FILLER CHAR(30),
col3 CHAR(20)
)

col1 and col3 must exist in the table but col2 is ignored.

[Stuart Turton]

If the files are on unix use cut, awk, perl to remove the unwanted fields. If
they are on NT use perl, excel to remove them.

Jeff Price wrote:

> What if the data is delimited, not fixed?
>

[Steve Haynes]

In article <8781ar$n2...@usilws15.ca-nethaven.com>, Tim Cope
<cop...@cai.com> writes

>You can specify the column as a FILLER and sqlldr will skip that column.
>For example:
>
>INTO TABLE MYTEST
>(
>col1 CHAR(20),
>col2 FILLER CHAR(30),
>col3 CHAR(20)
>)
>

New one on me, what version did this arrive in?,
I'm sure it didn't used to be possible.
Steve

[Connor McDonald]

Available in 8i.

Typically people use "cut" or equivalent in Unix as a pre-process to do
the same in lower versions.

HTH
--
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"
connor_...@yahoo.com

"Some days you're the pigeon, and some days you're the statue."

[Narendra kumar]

Hi,
Can we skip the field names in CTL file?