| Sql Loader [message #567151] |
Mon, 24 September 2012 12:06  |
bond007
Messages: 64
Registered: March 2009
|
Member |
|
|
Hi,
I have flat file in the following format
Table Structure :
create table emp
( id number,
name varchar2(40),
file_id varchar2(4)
)
The data file has two column but the table has 3 . The 1st and 3rd column are same but id is number and file_id is character.
How to change in the control file. Need some input from you .
Emp
Id, name ,file_id
1 Alex 1
2 paul 02
3, Allen 3
Thanks in advance
|
|
|
|
|
|
| Re: Sql Loader [message #567157 is a reply to message #567151] |
Mon, 24 September 2012 13:23  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
bond007 wrote on Mon, 24 September 2012 13:06
Emp
Id, name ,file_id
1 Alex 1
2 paul 02
3, Allen 3
What determines that ID number 2 will use lowercase for the name and that ID 3 will have a comma appended to the Emp ID?
Aside from those 2 issues, have you tried just putting 2 entires in the control file, one using a TO_NUMBER and the other using data "as is." Of course, TO_NUMBER will fail on any data that is not really a number.
|
|
|
|
|
|
| Re: Sql Loader [message #567168 is a reply to message #567158] |
Mon, 24 September 2012 17:20 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- test.dat:
-- test.ctl:
load data
infile test.dat
into table emp
fields terminated by ','
trailing nullcols
(id, name, file_id ":id")
-- table, load, and results:
SCOTT@orcl_11gR2> create table emp
2 (id number,
3 name varchar2(40),
4 file_id varchar2(4))
5 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> select * from emp
2 /
ID NAME FILE
---------- ---------------------------------------- ----
1 Alex 1
2 Paul 02
3 Allen 3
3 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
