Solved

SQL Loader question to import from a comma separated values file

Posted on 2003-12-08
16
3,627 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:MetroNY
  • 8
  • 7
16 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9896690
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
 

Author Comment

by:MetroNY
ID: 9896888
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
 

Author Comment

by:MetroNY
ID: 9896896
also, I did change the csv file and took out the trailing commas.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9897046
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
 

Author Comment

by:MetroNY
ID: 9897086
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
 
LVL 23

Accepted Solution

by:
seazodiac earned 100 total points
ID: 9897132
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
 

Author Comment

by:MetroNY
ID: 9897170
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
 
LVL 10

Expert Comment

by:SDutta
ID: 9897190
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:seazodiac
ID: 9897269
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
 

Author Comment

by:MetroNY
ID: 9897298
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 9897320
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
 

Author Comment

by:MetroNY
ID: 9897549
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 9897563
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
 

Author Comment

by:MetroNY
ID: 9897614
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 9897632
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
 

Author Comment

by:MetroNY
ID: 9897686
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now