SQL Loader - missing SELECT keyword?

[CTOROCK]

Hi,

I am starting out with Oracle and using Oracle SQL PLUS. I am trying to load a .csv file into a table I created using the loader, but I get a "missing SELECT keyword" when I'm trying to load it. This is how I'm trying to do it:
LOAD DATA
INFLIE 'P:HHH.csv'
INTO TABLE ER_PHAR
REPLACE
FIELDS TERMINATED BY ','
(ID_CD, NCPDP_CD, NCPDP2_CD,
NPI_CD , PHARMACY_DE,
PHARMACY_NAME_DE,
ADDRESS_CD , CITY_DE,
STATE_DE, ZIP_CD,
PHONE_CD, FAX_CD,
EMAIL_CD, CHAIN_CODE_ID,
REGION_CD, BU_L_D_C2_CD,
L_BU_L_DC_A_CD ,
PIMS_ID, LEGACY_GL_ID, EPS_PH_ID_CD, COMMENTS_CD);

the fields at the end are those in my table. I've tried using the SELECT statment by trying this:
SELECT
LOAD DATA
INFLIE 'P:\Eric\Project\ALL_KP_OUTPATIENT_PHARMACIES_20100223.csv'
INTO TABLE ER_PHARMACY_TEST
REPLACE
FIELDS TERMINATED BY ','
(ID_CD, NCPDP_CD, NCPDP2_CD,
NPI_CD , PHARMACY_DE,
PHARMACY_NAME_DE,
ADDRESS_CD , CITY_DE,
STATE_DE, ZIP_CD,
PHONE_CD, FAX_CD,
EMAIL_CD, CHAIN_CODE_ID,
REGION_CD, BU_L_D_C2_CD,
L_BU_L_DC_A_CD ,
PIMS_ID, LEGACY_GL_ID, EPS_PH_ID_CD,
COMMENTS_CD)
FROM
DUAL;

But it says FROM keyword wasn't found where expected. Thanks to anyone for their help in advance.



The greatest risk, is not taking one.

 

[dkyrtata]

Are you sure you are running your load from SQL*LOADER and not from SQL*PLUS? These are two different programs.

I am only using SQL*LOADER in one place in my application so I am not fluent in its use.

But here are some notes I wrote for myself a while ago to remind me how to use the program from Unix. I hope it helps.

SQL Loader Syntax From Unix:

Sqlldr userid=userid/passwd@db keyword1=value keyword2=value2 keyword3=value3

SQL*Loader Example: (the "\" is a line continuation in Unix, as the entire command should really be on one line)

sqlldr userid=dbuserid/passwd@dbinstance \
direct=TRUE \
control=/path/ctrlfile.ctl \
bad=/path/sqlldr.bad \
data=/path/ssp.schl.load.fl.40.2006011207 \
log=/path/sqlldr.log

or

sqlldr userid=dbuserid/passwd@dbinstance parfile=/path/parameter_file

where parameter_file contains the parameter information from the first syntax.

The ctrlfile.ctl file contains:

UNRECOVERABLE
LOAD DATA
INTO TABLE school
FIELDS TERMINATED BY "~" TRAILING NULLCOLS
( RECORD_NUMBER INTEGER EXTERNAL
, TRANSACTION_DATE DATE "YYYYMMDDHH24MISS"
, SCHL_ID CHAR(8)
, SCHL_NAME CHAR(100)
)

Notes:

- If the data-type in the control file is not specified for a given field, SQL*Loader will try to figure it out.
- Numeric types can be specified as DECIMAL or INTEGER



You may also want to check this web site for more details

http://www.oracleutilities.com/OSUtil/sqlldr.html

 

[LKBrwnDBA]

a) Follow dkyrtata's instructions and links to learn how to use SQL*Loader utility.

b) You have two issues with your loader control file:

1) Syntax error ([red]INFLIE[/red]):

LOAD DATA
INFILE 'P:HHH.csv'
INTO TABLE ER_PHAR
-- Etc ---

2)Then you state:

the fields at the end are those in my table

The fields should NOT be those of your table, but rather the fields from the csv file in the order they are present and using the column names from your table.
Capiche?

c) You may also want to read about EXTERNAL TABLES.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Turkbear]

Hi,
Here is a link to almost everything you want to know about SqlLoader:

http://www.orafaq.com/wiki/SQL*Loader_FAQ

NOTE: Use cut and Paste for that link, not just clicking on it, since the * causes problems.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CTOROCK]

Thanks for the responses. Yes, unfortunatley I was trying to call sqlloader from SQL Plus. I understand that I can not do this through SQL Plus. I am not trying to do this with a BAT file that looks at a PAR file which refers it to my ctl file, but nothing happens.

My .BAT file reads:

SQLLDR USERID=MSU/MSU@M311
PARFILE=\\tsclient\P\Eric\Project\TEST_LOADER.PAR


My .PAR file reads:
CONTROL=\\tsclient\P\Eric\Project\TEST_LOADER.ctl,
LOG=\\tsclient\P\Eric\Project\TEST_LOADER.LOG,
DIRECT=YES,
ERRORS=9999999


My .CTL file reads:
Load Data
INFLIE '\\tsclient\P\Eric\Project\ALL_KP_OUTPATIENT_PHARMACIES_20100223.csv'
BADFILE '\\tsclient\P\Eric\Project\ALL_KP_OUTPATIENT_PHARMACIES_BAD.bad'
DISCARDFILE '\\tsclient\P\Eric\Project\ALL_KP_OUTPATIENT_PHARMACIES_dISC.DSC'

INTO TABLE ER_PHARMACY_TEST
Replace
FIELDS TERMINATED BY ','
(ID,NCPDP,NCPDP2,NPI,Pharmacy, 'Pharmacy Name',
Address, CITY,'STATE',ZIP,PHONE, FAX, EMAIL, 'CHAIN CODE',
REGION,'Bus Unit + Loc + Dpt Code2 - Updated',
'(Legal) Bus Unit + Loc + Dpt Code-Acct',
PIMS,'Legacy GL Location #','EPS Pharm ID#',Comments)
;



But nothing happens. When I run the .BAT file I get a line that reads:
"control =" as if it looking for my ctl file. I enter the path but nothing.

Thanks in advance for your help!


The greatest risk, is not taking one.

 

[CTOROCK]

OK i figured it out, I used two lines instead of one in the .bat file. And I misspelled Infile in my .ctl. Everything transfered successfully with no errors.

Thank you, everyone for your help!

The greatest risk, is not taking one.

 

[KenCunningham]

CTORock, glad you go this working, but if I might just add that it is traditional in these fora to award little purple stars to those contributors whose assistance has proved valuable to you, as a way of expressing your appreciation. Just thought you should be aware.

The internet - allowing those who don't know what they're talking about to have their say.