SQLLoading with Comma Delimiter and Quoted Strings [message #70484] |
Wed, 19 June 2002 21:26 |
Deborah
Messages: 18 Registered: June 2002
|
Junior Member |
|
|
I get my input data with comma separated values
and enclosed with double quotes in all the fields
compulsorily. Moreover, in the data file, they
appear as if it is fixed width. They come like this:
(Just an extract of two fields only.)
"2002-06-03","09:27:15"
"2002-06-03","11:25:46"
"2002-06-04","14:21:39"
"2002-05-22","15:52:07"
"<10 spaces>","<8 spaces>"
"<10 spaces>","<8 spaces>"
"2002-06-07","17:27:47"
"2002-06-04","09:20:11"
"2002-06-10","18:26:44"
"2002-05-05","02:49:36"
The first of these fields is a date field and the
other one is to be kept separate as a time field.
In oracle, I do it as date field and varchar2 (8)
field.
How should I tackle this when there are blanks in
the date field?
I have another problem, I have another field which
looks like:
"2002-05-07-10.25.54"
having both date and time part in the same field. This
appears in the same dat file.
Right now, I have created another date field for this
one. And in the sqlloader control file, mentioning the
field as:
time_stamp date "yyyy-mm-dd-hh24.mi.ss" which works
fine as long as the input is fine. But sometimes, I
get the input as:
"2002-05-07 <whitespaces padded>" (no time part).
And sometimes no date as well. All spaces (not NULL,
but full width taken by whitespaces)
So can anyone suggest how my control file should be ?
I also have the permission to the table structure. Can
also suggest how my table fields be changed.
So I have two questions above. Immediate reply pls.
TIA,
Debby
|
|
|
|
|