OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » sqlloader help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlloader help [message #73002]

Tue, 13 January 2004 05:32

nanda
Messages: 11
Registered: February 2003

Junior Member

Hi

�� i have a data file which is delimited by a comma(,).

��total there are 10 columns are available in the data file

��all the columns are not in fixed size.

�� i need to upload last 3 columns alone in to the table.

�� that means i have to discard first 7 columns and

� then upload remaining 3 columns in to the database.

��is it possible??? if possible pls tell me how to do this.

Thanks in advance.

�

��

Report message to a moderator

Re: sqlloader help [message #73004 is a reply to message #73002]

Tue, 13 January 2004 07:04

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

please try this. use SKIP

if there are 10 records in the file,
and u want to skip first 7 records

sqlldr userid=user/pass control=somectl.ctl skip=7

Report message to a moderator

Re: sqlloader help [message #73007 is a reply to message #73004]

Tue, 13 January 2004 20:40

nanda
Messages: 11
Registered: February 2003

Junior Member

Mahesh.Rajendran,
Thanks for ur reply.
but i need to skip first 7 columns not rows..

for eg.

a flat file containing 4 rows

123,246,333,121,121,121,24d,zzz,777,xxx
123,246,333,121,121,121,24d,zzz,777,xxx
123,246,333,121,121,121,24d,zzz,yyy,xxx
123,246,333,121,121,121,24d,zzz,yyy,xxx

in this i have to upload last 3 columns into the table

zzz,777,xxx
zzz,777,xxx
zzz,yyy,xxx
zzz,yyy,xxx

thanks and regards
Nanda

Report message to a moderator

Re: sqlloader help [message #73010 is a reply to message #73007]

Wed, 14 January 2004 03:09

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

simplest workaround is to load the datafile as-is to a tempoary table ( with 10 columns).
then
use a simple sql to load only 3 columns u want, to the target table.
else
use some simple scripting to create a new datafile which contains only last 3 columns

Report message to a moderator

Re: sqlloader help [message #73011 is a reply to message #73007]

Wed, 14 January 2004 03:25

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

here is a simple script
sample.dat = your original file
newsample.dat= new file with last 3 columns

bash-2.03$ cat sample.dat
123,246,333,121,121,121,24d,zzz,777,xxx
123,246,333,121,121,121,24d,zzz,777,xxx
123,246,333,121,121,121,24d,zzz,yyy,xxx
123,246,333,121,121,121,24d,zzz,yyy,xxx

bash-2.03$ cut -d, -f8,9,10 sample.dat > newsample.dat
bash-2.03$ cat newsample.dat
zzz,777,xxx
zzz,777,xxx
zzz,yyy,xxx
zzz,yyy,xxx

Report message to a moderator

Re: sqlloader help [message #73016 is a reply to message #73007]

Thu, 15 January 2004 01:32

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You can use the filler keyword in your .ctl file to skip the first seven columns, assigning arbitrary column names:

(
text_col1 FILLER,
text_col2 FILLER,
text_col3 FILLER,
text_col4 FILLER,
text_col5 FILLER,
text_col6 FILLER,
text_col7 FILLER,
table_col1,
table_col2,
table_col3
)

Report message to a moderator

Re: sqlloader help [message #73331 is a reply to message #73007]

Fri, 26 March 2004 17:42

Joyee
Messages: 1
Registered: March 2004

Junior Member

Hi
I can not insert data from txt/csv file.
Pls help me.....
Joyee

Report message to a moderator

Previous Topic:	sqlldr and bad file
Next Topic:	ORA-00942 Oracle error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 20:51:01 CST 2024