Sharon,
Not sure why it's flagging the 2nd field, unless the lines you
displayed in your email did not show other lines in the ".csv" file?
Is the ".csv" file truly just the five lines you've shown?
But one problem I do see is the definition of the REG and OT columns as
NUMBER(3,2) yet some of the data for those columns are "39.02" which is
NUMBER (4,2), for example. The other problem is your date format --
the data is MM/DD/YY, but your specification is saying "MM-DD-YYYY".
By the way just FYI, the syntax you are using is also known as
"relative positioning", because you've specified "field separated by
comma", meaning that each field follows the other (relative).
"Absolute" positioning would mean you specified the starting
byte-offset and length of each field, instead of just saying "fields
separated by comma".
Hope this helps?
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal => P.O. Box 630791, Highlands Ranch CO 80163-0791
website => http://www.EvDBT.com/
email => Tim@xxxxxxxxx
mobile => +1-303-885-4526
fax => +1-303-484-3608
twitter => http://www.twitter.com/timothyjgorman
Lost Data? => http://www.ora600.be/
Sharon.Kovac@xxxxxxxxxxxxxxx wrote:
Hi all,
I need help with creating an
external
table. I'm working with a simple csv file, example data below:
1,450,01/03/10,01/09/10,39.02,0.00
1,311,01/03/10,01/09/10,0.00,0.00
2,120,01/03/10,01/09/10,0.00,0.00
2,160,01/03/10,01/09/10,0.00,0.00
2,655,01/03/10,01/09/10,39.92,0.00
When I query the external table, I
get
the "reject limit reached" error. The log contains the
following for each row:
KUP-04021: field formatting error
for
field HOME_WG_3
KUP-04025: field extends beyond end
of record
KUP-04101: record 1 rejected in file
/home/aeexport/HOURSEXP.CSV
I looked up the error message and
all
I get is "This can happen when specifying either absolute or
relative positioning for a field." I'm not specifying an absolute
or relative position for a field.
Here's my create table statement:
CREATE
TABLE
ROT_EMP_HOURS_CSV
(
HOME_WG_1
NUMBER(1),
HOME_WG_3 NUMBER(3),
START_DATE DATE,
END_DATE DATE,
REG NUMBER(3,2),
OT NUMBER(3,2))
ORGANIZATION
EXTERNAL
(
TYPE
ORACLE_LOADER
DEFAULT
DIRECTORY
AEEXPORT
ACCESS
PARAMETERS
(
records delimited by
newline
fields terminated
by
','
(
HOME_WG_1 ,
HOME_WG_3 ,
START_DATE DATE
'MM-DD-YYYY',
END_DATE DATE
'MM-DD-YYYY',
REG
,
OT
)
)
LOCATION
('HOURSEXP.CSV')
)
Oracle 10.2.0.2 on Solaris.
Thanks for your help!
Sharon
Sharon Kovac |
RotoMetrics
Phone: +1 636 587 3600 |
Fax: +1 636 587 3701
800 Howerton Lane |
Eureka, MO 63025 |
USA
http://www.rotometrics.com
|
http://www.myroto.com
---------------------------------------------------------------------
This e-mail message is intended only
for
the personal use of the recipient(s) named above. This message is
confidential.
If you are not an intended recipient, you may not review, copy or
distribute
this message. If you have received this communication in error, please
notify the sender immediately by e-mail and delete the original message.
---------------------------------------------------------------------
.
|