Re: SQL*Loader Question

From: Bricklen <bricklen_at_shaw.ca>
Date: Wed, 06 Mar 2002 00:21:18 GMT
Message-ID: <3C856071.2FE9FAA8@shaw.ca>

maybe try a controlfile like this:

LOAD DATA
INFILE *
APPEND INTO TABLE your_table
TRAILING NULLCOLS
WHEN (1:3) = 'USD'
FIELDS TERMINATED BY ','
(
col1,
col2,
col3 "TO_DATE(:col3,'DD-MM-YYYY')",

col4,
col5,
col6,

)
BEGINDATA
Client_id XYZCompany
base,quote,amount,date,ask,bid

USD,,1,03-01-2002,1.1565,1.1558
USD,CAD,1,03-01-2002,1.5930,1.5922
USD,JPY,1,03-01-2002,133.360,133.300
USD,MXP,1,03-01-2002,9.1136,9.0636
USD,CHF,1,03-01-2002,1.7083,1.7073
USD,GBP,1,03-01-2002,0.7051,0.7047
USD,EUR,1,03-01-2002,1.1565,1.1558
USD,AUD,1,03-01-2002,1.9253,1.9242
USD,HKD,1,03-01-2002,7.8004,7.7992
USD,SGD,1,03-01-2002,1.8329,1.8321
USD,TWD,1,03-01-2002,35.1005,35.1005
USD,INR,1,03-01-2002,48.8240,48.6460
USD,MYR,1,03-01-2002,3.8021,3.7975
USD,CNY,1,03-01-2002,8.2866,8.2666
USD,KRW,1,03-01-2002,1324.20,1313.50
USD,SEK,1,03-01-2002,10.5090,10.5030

you can also use functions and sql in there (eg. a CASE statements). That WHEN clause only works properly if you always start with USD. Change it to suit your needs.

(didn't actually test this, so I can't say for sure if it is going to work -- try it and see).

hth

Bricklen

Ian Terence Botham wrote:
>
> I just managed to write a script(in perl) that would pull the data
> displayed on the webite into a datafile. The datafile looks like this
>
> Client_id XYZCompany
> base,quote,amount,date,ask,bid
> USD,,1,03-01-2002,1.1565,1.1558
> USD,CAD,1,03-01-2002,1.5930,1.5922
> USD,JPY,1,03-01-2002,133.360,133.300
> USD,MXP,1,03-01-2002,9.1136,9.0636
> USD,CHF,1,03-01-2002,1.7083,1.7073
> USD,GBP,1,03-01-2002,0.7051,0.7047
> USD,EUR,1,03-01-2002,1.1565,1.1558
> USD,AUD,1,03-01-2002,1.9253,1.9242
> USD,HKD,1,03-01-2002,7.8004,7.7992
> USD,SGD,1,03-01-2002,1.8329,1.8321
> USD,TWD,1,03-01-2002,35.1005,35.1005
> USD,INR,1,03-01-2002,48.8240,48.6460
> USD,MYR,1,03-01-2002,3.8021,3.7975
> USD,CNY,1,03-01-2002,8.2866,8.2666
> USD,KRW,1,03-01-2002,1324.20,1313.50
> USD,SEK,1,03-01-2002,10.5090,10.5030
>
> I would like to chop off the first 2 lines in the file and load the
> rest into the database....
> Client_id XYZCompany
> base,quote,amount,date,ask,bid
>
> Is it possible thru SQL*Loader to do so?? I would like to get a
> feedback based on which I shall create a script...
>
> Thanks
> Ian....
Received on Tue Mar 05 2002 - 18:21:18 CST