-
SQL*LOADER Question
Hello. I have a table that I wanted to load a CSV file into
1 CREATE TABLE "TMS_DEF_ACTIONS" ("DEF_ACTION_KEY"
2 VARCHAR2(15) NOT NULL, "CREATION_TS" DATE NOT NULL,
3 "CREATED_BY" VARCHAR2(30) NOT NULL, "ACTION_TYPE"
4 VARCHAR2(15) NOT NULL, "DEFAULT_TEXT" VARCHAR2(2000),
5 "MODIFICATION_TS" DATE, "MODIFIED_BY" VARCHAR2(30),
6 CONSTRAINT "TMS_DEF_ACTIONS_PK" PRIMARY KEY("DEF_ACTION_KEY")
and I wanted to load the following csv data that I extracted
"CLARIFY ","21.04.2004 11:03:04","OPS$KOO ","C ","Please clarify this term. "," "," " "SPLIT ","21.04.2004 11:03:04","OPS$KOO ","N ","There is more than one medical concept. Please split for accurate coding. "
I put this controlfile together
LOAD DATA
INFILE 'TMS_DEF_ACTIONS.lst'
INTO TABLE TMS_DEF_ACTIONS
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
DEF_ACTION_KEY CHAR NULLIF (DEF_ACTION_KEY=BLANKS)
, CREATION_TS DATE "MM/DD/YY" NULLIF (CREATION_TS=BLANKS)
, CREATED_BY CHAR NULLIF (CREATED_BY=BLANKS)
, ACTION_TYPE CHAR NULLIF (ACTION_TYPE=BLANKS)
, DEFAULT_TEXT CHAR NULLIF (DEFAULT_TEXT=BLANKS)
, MODIFICATION_TS DATE "MM/DD/YY" NULLIF (MODIFICATION_TS=BLANKS)
, MODIFIED_BY CHAR NULLIF (MODIFIED_BY=BLANKS)
but i am gettign errors..
QL*Loader: Release 9.2.0.6.0 - Production on Tue Jun 21 15:12:22 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Control File: tms_def_actions.ctl
Data File: TMS_DEF_ACTIONS.lst
Bad File: TMS_DEF_ACTIONS.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table TMS_DEF_ACTIONS, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEF_ACTION_KEY FIRST * , CHARACTER
NULL if DEF_ACTION_KEY = BLANKS
CREATION_TS NEXT * , DATE MM/DD/YY
NULL if CREATION_TS = BLANKS
CREATED_BY NEXT * , CHARACTER
NULL if CREATED_BY = BLANKS
ACTION_TYPE NEXT * , CHARACTER
NULL if ACTION_TYPE = BLANKS
DEFAULT_TEXT NEXT * , CHARACTER
NULL if DEFAULT_TEXT = BLANKS
MODIFICATION_TS NEXT * , DATE MM/DD/YY
NULL if MODIFICATION_TS = BLANKS
MODIFIED_BY NEXT * , CHARACTER
NULL if MODIFIED_BY = BLANKS
Record 1: Rejected - Error on table TMS_DEF_ACTIONS, column DEFAULT_TEXT.
Field in data file exceeds maximum length
Record 2: Rejected - Error on table TMS_DEF_ACTIONS, column DEF_ACTION_KEY.
Field in data file exceeds maximum length
Record 3: Rejected - Error on table TMS_DEF_ACTIONS, column DEF_ACTION_KEY.
Field in data file exceeds maximum length
Record 4: Rejected - Error on table TMS_DEF_ACTIONS, column DEFAULT_TEXT.
Field in data file exceeds maximum length
Record 5: Rejected - Error on table TMS_DEF_ACTIONS, column DEF_ACTION_KEY.
Field in data file exceeds maximum length
Record 6: Rejected - Error on table TMS_DEF_ACTIONS, column DEF_ACTION_KEY.
Field in data file exceeds maximum length
Table TMS_DEF_ACTIONS:
0 Rows successfully loaded.
6 Rows 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.
any ideas?
thanks
"High Salaries = Happiness = Project Success."
-
Hi.
I'm not surprised you've had no answers to this post. You really need to read and correct the post if you expect an answer.
First, your table definition is bad as it has to be edited to run it. Post a real statement in future like:
Code:
CREATE TABLE "TMS_DEF_ACTIONS" (
"DEF_ACTION_KEY" VARCHAR2(15) NOT NULL,
"CREATION_TS" DATE NOT NULL,
"CREATED_BY" VARCHAR2(30) NOT NULL,
"ACTION_TYPE" VARCHAR2(15) NOT NULL,
"DEFAULT_TEXT" VARCHAR2(2000),
"MODIFICATION_TS" DATE,
"MODIFIED_BY" VARCHAR2(30),
CONSTRAINT "TMS_DEF_ACTIONS_PK" PRIMARY KEY("DEF_ACTION_KEY")
);
Next, the controlfile was missing a terminating ")" character. That's just sloppy.
Next, the data was not displayed correctly as it looked like one big line, rather than two. I assume you meant to post this:
"CLARIFY ","21.04.2004 11:03:04","OPS$KOO ","C ","Please clarify this term. "," "," "
"SPLIT ","21.04.2004 11:03:04","OPS$KOO ","N ","There is more than one medical concept. Please split for accurate coding. "
Finally, you did not include the command you used to run the sqlldr process. I used this:
sqlldr userid=username/password@service control=c:\control.ctrl log=c:\sqlldr.log bad=c:\bad.log data=c:\TMS_DEF_ACTIONS.lst
If you had done all this I could have run the example really quickly, but as you did not I ignored the post for some time.
Anyway, the controlfile you posted had a couple of issues I could see.
First. You did not tell SQL*Loader that the fields could be enclosed by " characters, which meant the date conversions failed.
Second. The date formats listed did not match the data.
I was able to load the data when I used this control file:
Code:
LOAD DATA
INFILE 'TMS_DEF_ACTIONS.lst'
INTO TABLE TMS_DEF_ACTIONS
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY "\""
TRAILING NULLCOLS
(
DEF_ACTION_KEY CHAR NULLIF (DEF_ACTION_KEY=BLANKS)
, CREATION_TS DATE "DD.MM.YYYY HH24:MI:SS" NULLIF (CREATION_TS=BLANKS)
, CREATED_BY CHAR NULLIF (CREATED_BY=BLANKS)
, ACTION_TYPE CHAR NULLIF (ACTION_TYPE=BLANKS)
, DEFAULT_TEXT CHAR NULLIF (DEFAULT_TEXT=BLANKS)
, MODIFICATION_TS DATE "MM/DD/YY" NULLIF (MODIFICATION_TS=BLANKS)
, MODIFIED_BY CHAR NULLIF (MODIFIED_BY=BLANKS)
)
Note, the table contains more columns that the file contains data. I assume this is by design.
Cheers
Tim...
Last edited by TimHall; 06-22-2005 at 07:23 AM.
-
you are a very patient man Mr Hall
-
I am compared to you. I seem to remember you used to call Murdock a "Damn fool!" every week on TV!
;-)
-
call a spade a spade and all that
-
Bu I call it a shovel
-
Thankyou Tim, I appreciate your response. I initially was going to post the entire create table command but I thought i leave it out so i dont fill up the unneccesary space but the with the sql*loader, I havent had much experience so i do appreciate your guidance.
I will go ahead and try the controlfile you gave me and give you feedback on it.
thanks again.
"High Salaries = Happiness = Project Success."
-
PLSQL Developer
or you can use the new feature on PLSQL Developer 6 (if you have it),"Text importer". It works like a dream importing from csv format....it generates the controlfile for you and you can edit it manually, however you need to create the table in your desired schema first.
I recently used it to import info from a synonym (table) linked through db-link.
I saved the output of a select * statement on the synonym to csv, and used the "new" table for some local testing on our application.
Able was I ere I saw Elba
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|