SQL LDR and FLOAT field

Hi,

I am desperatly trying to load a simple csv file into an empty table (may be
not that simple after all!).
It keeps on telling the numbers are invalid for the colun value.
When I get rid of the digits after the comma it works fine. Would there be a
way to load big numbers with sqlldr?
(It also does not work with the datatype NUMBER, same message).
Thanks for helping.


here is my ctl file

LOAD DATA
INFILE 'C:\loadCSV\testrun.csv'
INTO TABLE STOCK_DATA
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(CODE, NBTYPE, YEAR, PERIOD, IS_ESTIMATE, VALUE )

The structure of my table:

CREATE TABLE OWNER.STOCK_DATA
(
CODE VARCHAR2(10) NOT NULL,
NBTYPE VARCHAR2(10) NOT NULL,
YEAR NUMBER(10) NOT NULL,
PERIOD VARCHAR2(2) NOT NULL,
IS_ESTIMATE VARCHAR2(2) NOT NULL,
VALUE FLOAT(126) NOT NULL
)

Here is what looks like my csv file:

SIEGn.DE,DBFI058,1989,A,N,10684.102654
SIEGn.DE,DBGE001,1989,A,N,370000.000000
SIEGn.DE,DBGE002,1989,A,N,365000.000000
SIEGn.DE,DBIA001,1989,A,N,18693.802631
SIEGn.DE,DBIA002,1989,A,N,11586.385320
SIEGn.DE,DBIA003,1989,A,N,6335.300000
SIEGn.DE,DBIA004,1989,A,N,2797.500000
SIEGn.DE,DBIA005,1989,A,N,4768.400000
SIEGn.DE,DBIA008,1989,A,N,7107.417311

[Daniel Morgan]

grille11@yahoo.com wrote:
Hi, I am desperatly trying to load a simple csv file into an empty table (may be not that simple after all!). It keeps on telling the numbers are invalid for the colun value. When I get rid of the digits after the comma it works fine. Would there be a way to load big numbers with sqlldr? (It also does not work with the datatype NUMBER, same message). Thanks for helping. here is my ctl file LOAD DATA INFILE 'C:\loadCSV\testrun.csv' INTO TABLE STOCK_DATA FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (CODE, NBTYPE, YEAR, PERIOD, IS_ESTIMATE, VALUE ) The structure of my table: CREATE TABLE OWNER.STOCK_DATA ( CODE VARCHAR2(10) NOT NULL, NBTYPE VARCHAR2(10) NOT NULL, YEAR NUMBER(10) NOT NULL, PERIOD VARCHAR2(2) NOT NULL, IS_ESTIMATE VARCHAR2(2) NOT NULL, VALUE FLOAT(126) NOT NULL ) Here is what looks like my csv file: SIEGn.DE,DBFI058,1989,A,N,10684.102654 SIEGn.DE,DBGE001,1989,A,N,370000.000000 SIEGn.DE,DBGE002,1989,A,N,365000.000000 SIEGn.DE,DBIA001,1989,A,N,18693.802631 SIEGn.DE,DBIA002,1989,A,N,11586.385320 SIEGn.DE,DBIA003,1989,A,N,6335.300000 SIEGn.DE,DBIA004,1989,A,N,2797.500000 SIEGn.DE,DBIA005,1989,A,N,4768.400000 SIEGn.DE,DBIA008,1989,A,N,7107.417311

The following worked perfectly in Oracle 9.2.0.4

LOAD DATA
INFILE *
INTO TABLE STOCK_DATA
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(CODE, NBTYPE, YEAR, PERIOD, IS_ESTIMATE, VALUE )

BEGINDATA
SIEGn.DE,DBFI058,1989,A,N,10684.102654
SIEGn.DE,DBGE001,1989,A,N,370000.000000
SIEGn.DE,DBGE002,1989,A,N,365000.000000
SIEGn.DE,DBIA001,1989,A,N,18693.802631
SIEGn.DE,DBIA002,1989,A,N,11586.385320
SIEGn.DE,DBIA003,1989,A,N,6335.300000
SIEGn.DE,DBIA004,1989,A,N,2797.500000
SIEGn.DE,DBIA005,1989,A,N,4768.400000
SIEGn.DE,DBIA008,1989,A,N,7107.417311

running sqlldr as:
C:\oracle\ora92\bin> sqlldr userid=abc/abc control=c:\temp\test

SQL*Loader is easy. The question is what are you doing, or not
doing that is not in your posting?

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[Alan]

"Digits after the COMMA"?

<grille11@yahoo.com> wrote in message
news:c3c3j8$28c$1@reader1.imaginet.fr... Hi, I am desperatly trying to load a simple csv file into an empty table (may
be not that simple after all!). It keeps on telling the numbers are invalid for the colun value. When I get rid of the digits after the comma it works fine. Would there be
a way to load big numbers with sqlldr? (It also does not work with the datatype NUMBER, same message). Thanks for helping. here is my ctl file LOAD DATA INFILE 'C:\loadCSV\testrun.csv' INTO TABLE STOCK_DATA FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (CODE, NBTYPE, YEAR, PERIOD, IS_ESTIMATE, VALUE ) The structure of my table: CREATE TABLE OWNER.STOCK_DATA ( CODE VARCHAR2(10) NOT NULL, NBTYPE VARCHAR2(10) NOT NULL, YEAR NUMBER(10) NOT NULL, PERIOD VARCHAR2(2) NOT NULL, IS_ESTIMATE VARCHAR2(2) NOT NULL, VALUE FLOAT(126) NOT NULL ) Here is what looks like my csv file: SIEGn.DE,DBFI058,1989,A,N,10684.102654 SIEGn.DE,DBGE001,1989,A,N,370000.000000 SIEGn.DE,DBGE002,1989,A,N,365000.000000 SIEGn.DE,DBIA001,1989,A,N,18693.802631 SIEGn.DE,DBIA002,1989,A,N,11586.385320 SIEGn.DE,DBIA003,1989,A,N,6335.300000 SIEGn.DE,DBIA004,1989,A,N,2797.500000 SIEGn.DE,DBIA005,1989,A,N,4768.400000 SIEGn.DE,DBIA008,1989,A,N,7107.417311