Home » RDBMS Server » Server Utilities » Sql Loader (oracle 10 g)
Sql Loader [message #567151] Mon, 24 September 2012 12:06 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi,
I have flat file in the following format
1,Alex
02,Paul
3,Allen

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 #567155 is a reply to message #567151] Mon, 24 September 2012 12:24 Go to previous messageGo to next message
BlackSwan
Messages: 22722
Registered: January 2009
Senior Member
table is not normalized.
It is silly to have two different columns hold the same data in two different datatypes.
CREATE VIEW
or utilize Virtual Column
Re: Sql Loader [message #567157 is a reply to message #567151] Mon, 24 September 2012 13:23 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
bond007 wrote on Mon, 24 September 2012 13:06

1,Alex
02,Paul
3,Allen


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 #567158 is a reply to message #567155] Mon, 24 September 2012 13:24 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
if id is 2 file_id is 02 . we need to capture file_id exactly as per the data file.
Re: Sql Loader [message #567168 is a reply to message #567158] Mon, 24 September 2012 17:20 Go to previous message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
-- test.dat:
1,Alex
02,Paul
3,Allen


-- 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.

Previous Topic: Inserting targz files into table from specified folder
Next Topic: load query result in ms-excel sheet
Goto Forum:
  


Current Time: Mon Sep 01 07:08:16 CDT 2014

Total time taken to generate the page: 0.11689 seconds