SEO

#1 (**permalink**) 02-24-2008, 01:30 PM

Hi

I am planning to use SQL Loader to import data present in csv files to
Oracle 9i db. I have a few questions regarding SQL Loader.
1. Can i load selective columns from the csv file.
i.e. csv format is something like this

4/27/1998,506,23,M508 RCW,TESCO,,,,,295/80R22.5,295/80R22.5

out of which i just want to load 2nd and 4th column to a particular
table. Is it possible? Or should the input file contain only the
required columns to be imported?

Also for such cases of selective import would External Tables be a
better choice?

Thanks in advance.
Punit.

#2 (**permalink**) 02-24-2008, 01:30 PM

Użytkownik punitparikh@gmail.com napisał:

> Hi
>
> I am planning to use SQL Loader to import data present in csv files to
> Oracle 9i db. I have a few questions regarding SQL Loader.

If you're planning to use, then you should first learn about SQL Loader,
control file, and ask us where help to Loader is located.
And most of answers, you get is FUT help at thaiti.com i bet.

> 1. Can i load selective columns from the csv file.
> i.e. csv format is something like this
>
> 4/27/1998,506,23,M508 RCW,TESCO,,,,,295/80R22.5,295/80R22.5
>
> out of which i just want to load 2nd and 4th column to a particular
> table. Is it possible? Or should the input file contain only the
> required columns to be imported?
>
> Also for such cases of selective import would External Tables be a
> better choice?

Yes, it would be better, import all columns, most of them you find
useful in the future.

--
Noel

#3 (**permalink**) 02-24-2008, 01:30 PM

Hi Noel,

Thanks for your reply. I have gone through various ways and syntax of
the control file. Regarding selective loading I could figure out using
Position(...) when the input is a data stream but for a delimited input
file (csv in my case) I could not figure out how I can load only
selective columns?

Could you please provide some pointers as to how this can be done?

Thanks again,
Punit.

#4 (**permalink**) 02-24-2008, 01:30 PM

Użytkownik punitparikh@gmail.com napisał:

> Hi Noel,
>
> Thanks for your reply. I have gone through various ways and syntax of
> the control file. Regarding selective loading I could figure out using
> Position(...) when the input is a data stream but for a delimited input
> file (csv in my case) I could not figure out how I can load only
> selective columns?
>
> Could you please provide some pointers as to how this can be done?

Ok. I tell you how to do it. (Since Oracle8i)

lets see on your input line.

'4/27/1998,506,23,M508 RCW,TESCO,,,,,295/80R22.5,295/80R22.5'

Your line constains 10 columns, 8 has to be skipped.
You said you need to load column 2 and 4.

CREATE TABLE PARTLOAD
(
col1 VARCHAR(10),
col2 VARCHAR2(50)
);

Now, how you should to build your control file:

[...]
INTO TABLE PARTLOAD
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
a1 FILLER,
col1 char,
a2 FILLER,
col2 char,
a3 FILLER,
a4 FILLER,
a5 FILLER,
a6 FILLER,
a7 FILLER,
a8 FILLER,
)

--
Noel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Sql Loader: Selective Import query.