Home » RDBMS Server » Server Utilities » Skip column while loading data using SQL Loader
Skip column while loading data using SQL Loader [message #157734] Sun, 05 February 2006 00:12 Go to next message
AlinaC
Messages: 45
Registered: November 2005
Location: India
Member

Data

Sl# Emp_no Name Address
001 01 Tom 1/B-XYZ street
002 02 Jon 1/C-XYZ Street


Employee Datafile
001, 01, Tom, 1/B-XYZ street
002,02,Jon, 1/C-XYZ Street

Above is a sample data file. Now I would like to import the data into an Oracle table called employee using Oracle 9i SQL Loader utility. But the table has only 3 fields (Emp_no,Name & Address), so I would like to skip Sl# while loading data. I do not want to manually modify data file. How should I write .ctl file.

Sample .ctl file.

load data
INFILE 'data\Employee'
BADFILE 'Employee.bad'
DISCARDFILE 'Employee.dis'
into table Employee
fields terminated by ','
TRAILING NULLCOLS
(Emp_no NULLIF Emp_no = BLANKS,
Name NULLIF Name = BLANKS,
Address NULLIF Address = BLANKS
)

Regards,
Alina
Re: Skip column while loading data using SQL Loader [message #157755 is a reply to message #157734] Sun, 05 February 2006 08:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you try FILLER?
Search board for examples. One such here
http://www.orafaq.com/forum/t/26690/0/
Re: Skip column while loading data using SQL Loader [message #158159 is a reply to message #157734] Wed, 08 February 2006 13:23 Go to previous messageGo to next message
AlinaC
Messages: 45
Registered: November 2005
Location: India
Member

Thanks, It worked with a FILLER. Now I need to do oposite. now my datafile has 5 fields but my oracle table has more than 5 filelds. I have to load data from that data file, for those 5 fields data will be inserted from datafile, for rest column I would like to put NULL. Is it possible? if so how?



Regards,
Alina
Re: Skip column while loading data using SQL Loader [message #554862 is a reply to message #158159] Fri, 18 May 2012 01:27 Go to previous messageGo to next message
victoryhendry
Messages: 96
Registered: January 2007
Location: Bangalore
Member

Hi Alina,

its simple, you no need to insert manually NULL values for rest of the columns, Oracle by default it will insert null values. let's try to insert ( write a script- control file) only for available field from data files.


Thanks and Regards,

Vetrivel Karuppan (Oracle-Dharmapuri)
Re: Skip column while loading data using SQL Loader [message #554865 is a reply to message #554862] Fri, 18 May 2012 01:59 Go to previous message
Michel Cadot
Messages: 66773
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why is the purpose to answer to a solved case more than 6 years later?

Regards
Michel
Previous Topic: sql loader to load in multiple table with IN predicate
Next Topic: call sequence in sql loader
Goto Forum:
  


Current Time: Fri Jan 17 18:40:00 CST 2020