Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL LDR and FLOAT field
grille11_at_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_at_x.washington.edu (replace 'x' with a 'u' to reply)Received on Thu Mar 18 2004 - 11:46:10 CST