SQL Loader question to import from a comma separated values file

Hello everyone,

I created this new dB to keep track of daily Exchange rates.  At the moment lets say I can obtain a two line, comma separated values (CSV) file as follows:

FX.CSV  file:

Date, USD, JPY, DKK, GBP, SEK, CHF, ISK, NOK, BGN, CYP, CZK, EEK, HUF, LTL, LVL, MTL, PLN, ROL, SIT, SKK, TRL, AUD, CAD, HKD, NZD, SGD, KRW, ZAR,
4 December 2003, 1.2074, 130.67, 7.4415, 0.70045, 8.9630, 1.5579, 89.47, 8.0905, 1.9520, 0.58360, 32.293, 15.6466, 270.30, 3.4530, 0.6571, 0.4296, 4.6484, 40338, 236.5000, 41.014, 1756611, 1.6421, 1.5785, 9.3772, 1.8684, 2.0728, 1437.71, 7.4660,

But, I only need the following information to be entered and not everything: Date, USD, JPY, GBP, AUD.  
How can I load this into my dB using SQL*Loader and skip the rest? basically I want to choose the columns to be enter into my dB. Also, I may have to keep in mind that the order of the exchange rates in the csv file may change somewhere down the line when I obtain it.
so, is there any way to explicitly tell the SQL*Loader which columns to load, i.e. somehow use a "Where" type of command and search for USD, or JPY and then load this particular data?


say my SID is FX
table name: FX_RATES table

table structure:
Date - Sysdate
EUR - Number(1) - will always be 1, representing the exchange rate to 1 Euro.
USD - Number(11,5)
JPY - Number(11,5)
GBP - Number(11,5)
AUD - Number(11,5)

I currently can input the data as follows:

 insert into FX_RATES
 values(sysdate, 1, 1.2074, 130.67, 0.70045, 1.6421 );

Please let me know how I can script this is SQL to automate the proces and load the date with SQL Loader.
Your help is greatly appreciated.  Thank you very much.


-metro
MetroNYAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

seazodiacCommented:
sure you can use sql loader to load this data at once with the right parameter "FILLER" , which will direct sql loader to skip the column in the record.
first off , you should change "Date" column in your FX_RATES table because it's oracle reserved word.

let say:

FX_RATES(current_date date, eur number, usd number, jpy number, gbp number, aud number);

second, change your CSV file to this format:

Make the first line as the HEADER column , so you add "SKIP 1" in the control file below:

Date, USD, JPY, DKK, GBP, SEK, CHF, ISK, NOK, BGN, CYP, CZK, EEK, HUF, LTL, LVL, MTL, PLN, ROL, SIT, SKK, TRL, AUD, CAD, HKD, NZD, SGD, KRW, ZAR    
4 December 2003, 1.2074, 130.67, 7.4415, 0.70045, 8.9630, 1.5579, 89.47, 8.0905, 1.9520, 0.58360, 32.293, 15.6466, 270.30, 3.4530, 0.6571, 0.4296, 4.6484, 40338, 236.5000, 41.014, 1756611, 1.6421, 1.5785, 9.3772, 1.8684, 2.0728, 1437.71, 7.4660

create this control file:

load data
append
into table FX_RATES
skip 1
fields terminated by ','
(current_date DATE "to_date(current_date, 'DD MONTH YYYY')",
  EUR       CONSTANT 1,
  USD       decimal external,
  JPY         decimal external,
  GBP        decimal external,
  SEK         FILLER,
  CHF         FILLER,
  ISK          FILLER,
  NOK        FILLER,
  BGN         FILLER,
  CYP         FILLER,
  CZK         FILLER,
  EEK          FILLER,
   HUF        FILLER,
   LTL         FILLER,
   LVL        FILLER,
 MTL        FILLER,
 PLN        FILLER,
 ROL       FILLER,
  SIT         FILLER,
 SKK          FILLER,
 TRL           FILLER,
AUD            decimal external,  
 CAD         FILLER,
HKD           FILLER,
NZD           FILLER,
SGD          FILLER,
KRW         FILLER,
 ZAR        FILLER )


save this control file as FX.CTL

the use this command:

SQLLDR  userid=<usrname/pw@SID> control=FX.CTL data=FX_RATES.CSV

this should be it. hope this helps

 
0
MetroNYAuthor Commented:
Hi seazodiac,

Thank you so much for your fast response.
I scripted the file, but when I tried running it, I get the following error at the cmd prompt.

SQL*Loader-410: Number to skip must be load-level, not table-level

here's my script (I'm soooo close :) )

LOAD DATA

INFILE 'D:\DataFeeds\FX\Staging\eurofxref.csv'
BADFILE 'D:\DataFeeds\FX\BadDis\fx.bad'
DISCARDFILE 'D:\DataFeeds\FX\BadDis\fx.dis'

APPEND

INTO TABLE FXRATES_TEMP

SKIP 1

Fields terminated by ','

( FX_DATE sysdate,
  EUR CONSTANT 1,
  USD decimal external,
  JPY decimal external,
  GBP decimal external,
  SEK FILLER,
  CHF FILLER,
  ISK FILLER,
  NOK FILLER,
  BGN FILLER,
  CYP FILLER,
  CZK FILLER,
  EEK FILLER,
  HUF FILLER,
  LTL FILLER,
  LVL FILLER,
  MTL FILLER,
  PLN FILLER,
  ROL FILLER,
  SIT FILLER,
  SKK FILLER,
  TRL FILLER,
  AUD FILLER,
  CAD decimal external,  
  HKD FILLER,
  NZD FILLER,
  SGD FILLER,
  KRW FILLER,
  ZAR FILLER )

-metro
0
MetroNYAuthor Commented:
also, I did change the csv file and took out the trailing commas.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

seazodiacCommented:
two options for you:

1. move "SKIP 1" line up direct below "LOAD DATA" LINE
it will look like this:

LOAD DATA
SKIP 1
BADFILE.....

APPEND


2. Remove the "SKIP 1" line in the control file and THE HEADER LINE in the CSV file so that CSV file contain only data

0
MetroNYAuthor Commented:
ok, I think I figured it out, I have to put the SKIP=1 in the command prompt and not in the .ctl file, but guesss what, now I get a different error message:

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12560: TNS:protocol adapter error

would you happen to know what this means?  
Thank you.

0
seazodiacCommented:
why don't follow my suggestion?

change your control file to :


LOAD DATA
APPEND
INTO TABLE FXRATES_TEMP
Fields terminated by ','
( FX_DATE DATE sysdate,
 EUR CONSTANT 1,
 USD decimal external,
 JPY decimal external,
 GBP decimal external,
 SEK FILLER,
 CHF FILLER,
 ISK FILLER,
 NOK FILLER,
 BGN FILLER,
 CYP FILLER,
 CZK FILLER,
 EEK FILLER,
 HUF FILLER,
 LTL FILLER,
 LVL FILLER,
 MTL FILLER,
 PLN FILLER,
 ROL FILLER,
 SIT FILLER,
  SKK FILLER,
 TRL FILLER,
 AUD FILLER,
 CAD decimal external,  
 HKD FILLER,
  NZD FILLER,
  SGD FILLER,
  KRW FILLER,
 ZAR FILLER )

then modify your CSV file to remove the HEADER COLUMN ..basically this first line "Date, USD, JPY, DKK, GBP, SEK, CHF, ISK, NOK, BGN, CYP, CZK, EEK, HUF, LTL, LVL, MTL, PLN, ROL, SIT, SKK, TRL, AUD, CAD, HKD, NZD, SGD, KRW, ZAR".

then use the following command:


SQLLDR  userid=<usrname/pw@SID> control=<control_Filename> data=<data_filename>

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MetroNYAuthor Commented:
I have :(
I followed your exact suggestiong and I still get the same error message

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12560: TNS:protocol adapter error

0
SDuttaCommented:
In your case SKIP should be part of OPTIONS in the ctl file. SKIP can be put after the LOAD DATA only if you are doing a CONTINUE_LOAD.

OPTIONS (SKIP = 1)
LOAD DATA
APPEND
INTO TABLE FXRATES_TEMP
Fields terminated by ','
( FX_DATE DATE sysdate,
 EUR CONSTANT 1,
....
...
0
seazodiacCommented:
hmmm, try to change your control file to this:


LOAD DATA
APPEND
INTO TABLE FXRATES_TEMP
Fields terminated by ','
( FX_DATE DATE "sysdate",
EUR CONSTANT '1',
USD decimal external,
JPY decimal external,
GBP decimal external,
SEK FILLER,
CHF FILLER,
ISK FILLER,
NOK FILLER,
BGN FILLER,
CYP FILLER,
CZK FILLER,
EEK FILLER,
HUF FILLER,
LTL FILLER,
LVL FILLER,
MTL FILLER,
PLN FILLER,
ROL FILLER,
SIT FILLER,
  SKK FILLER,
TRL FILLER,
AUD FILLER,
CAD decimal external,  
HKD FILLER,
  NZD FILLER,
  SGD FILLER,
  KRW FILLER,
ZAR FILLER )
0
MetroNYAuthor Commented:
WoOhhooo, I got it.
the problem was in my command line
I missed out the @SID

 :(

but now it says

Commit point reached - logical record count 1

how do I automatically commit the statement so I can see it in my table and my db?

Thank you sooo much.

0
seazodiacCommented:
the mesg "Commit point reached - logical record count 1"
means your record is already commited, you should see the record in your table already
0
MetroNYAuthor Commented:
seazodiac,

I think I may have a small issue, because I am not seeing the data in my dB, and I get a .BAD file.
for some reason it thinks the file is bad.  what should I do ?

0
seazodiacCommented:
take a look at the log file, this log file should be in the same directory by the same name.

it generally gives you a reason why, post it if you can not solve yourself.
0
MetroNYAuthor Commented:
nice, I found the log file.
I think it's confusing the date field for USD, but I an not sure.


SQL*Loader: Release 9.2.0.4.0 - Production on Mon Dec 8 12:03:21 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Control File:   D:\DataFeeds\FX\Scripts\FX_Control.ctl
Data File:      D:\DataFeeds\FX\Staging\eurofxref.csv
  Bad File:     D:\DataFeeds\FX\BadDis\fx.bad
  Discard File: D:\DataFeeds\FX\BadDis\fx.dis
 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table FXRATES_TEMP, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FX_DATE                                                   SYSDATE
EUR                                                       CONSTANT
    Value is '1'
USD                                 FIRST     *   ,       CHARACTER            
JPY                                  NEXT     *   ,       CHARACTER            
GBP                                  NEXT     *   ,       CHARACTER            
SEK                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
CHF                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
ISK                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
NOK                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
BGN                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
CYP                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
CZK                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
EEK                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
HUF                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
LTL                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
LVL                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
MTL                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
PLN                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
ROL                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
SIT                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
SKK                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
TRL                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
AUD                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
CAD                                  NEXT     *   ,       CHARACTER            
HKD                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
NZD                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
SGD                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
KRW                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)
ZAR                                  NEXT     *   ,       CHARACTER            
  (FILLER FIELD)

Record 1: Rejected - Error on table FXRATES_TEMP, column USD.
ORA-01722: invalid number


Table FXRATES_TEMP:
  0 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  66176 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:             1
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Mon Dec 08 12:03:21 2003
Run ended on Mon Dec 08 12:03:21 2003

Elapsed time was:     00:00:00.18
CPU time was:         00:00:00.13
0
seazodiacCommented:
ok, check your data file:
if your data is still in this format, then it's wrong...because the first field is DATE. since you load your data with SYSDATE, you should get rid of the first Date field in your data file.

4 December 2003, 1.2074, 130.67, 7.4415, 0.70045, 8.9630, 1.5579, 89.47, 8.0905, 1.9520, 0.58360, 32.293, 15.6466, 270.30, 3.4530, 0.6571, 0.4296, 4.6484, 40338, 236.5000, 41.014, 1756611, 1.6421, 1.5785, 9.3772, 1.8684, 2.0728, 1437.71, 7.4660
0
MetroNYAuthor Commented:
Thank you  Thank you Thank you seazodiac for all you help !!!
it finally worked the way I want it. all I have to do is to schedule the script and it's done.
this was my first SQL Loader script, but I think I should be able to handle the next one.

:)

-Metro
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.