| Modify data while loading with SQL*Loader [message #69261] |
Thu, 29 November 2001 20:41  |
diaz
Messages: 58
Registered: October 2001
|
Member |
|
|
hello,
i have data with structure like this :
12345Xspundiaz 300.000
12346Diaz 10.000
i'd like to load them on the table X :
id char(5),
name char(10),
cash number
the problem is the field "cash" is going to be filled by data containing ".",
what i'd like to know is there converter to number to such field like that ?
i know there is "to_char" and "to_date"
thanks..
----------------------------------------------------------------------
|
|
|
|
|
|
| Re: Modify data while loading with SQL*Loader [message #69276 is a reply to message #69264] |
Sun, 02 December 2001 22:55  |
diaz
Messages: 58
Registered: October 2001
|
Member |
|
|
Mr.Suresh wrote :
oracle automatically handles when you define cash column in table like number(10,3).
hmm... unfortunately "." in the data is not showing decimal/floating point..
it is just string showing that the cash is in thousands..
example : 300.000 (three hundred thousands)
so, what should i do ?
----------------------------------------------------------------------
|
|
|
|
| Re: Modify data while loading with SQL*Loader [message #69282 is a reply to message #69264] |
Mon, 03 December 2001 07:50 |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
use replace function.
data:
abc,kkkk,300.000
wer,reeee,10.000
cotrol file:
LOAD DATA
INFILE 'samp.TXT'
APPEND
INTO TABLE samp_tbl
FIELDS TERMINATED BY ","
optionally enclosed by '"'
trailing nullcols
(field1,field2,field3 "to_number(replace(:field3,'.',''))")
table structure:
FIELD1 VARCHAR2(10)
FIELD2 VARCHAR2(10)
FIELD3 NUMBER
after invoking sqlloader, data in table look like
abc kkkk 300000
wer reeee 10000
is that you want?
----------------------------------------------------------------------
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
