| Problem loading a tab delimited file..? [message #72647] |
Thu, 14 August 2003 15:20  |
Eva
Messages: 16
Registered: July 2001
|
Junior Member |
|
|
My table looks like,
SQL> desc cpt_codes;
Name Null? Type
------------------------------- -------- ----
CPT_CODE VARCHAR2(5)
STARRED VARCHAR2(1)
SHORT_DESCR VARCHAR2(35)
LONG_DESCR VARCHAR2(48)
FULL_DESCR VARCHAR2(1741)
NF_TOTAL_RVU VARCHAR2(6)
FACILITY_TOTAL_RVU VARCHAR2(6)
STATUS VARCHAR2(1)
PROC_CODE_EFF_DATE DATE
My CPTPROF02T.txt file looks like(which is tab delimited),
00406 ANES-INTEG;RADL BRST W/NODE DISSECT ANES-INTEG EXTREM TRUNK;RADL BRST W/NODE DISSECT Anesthesia for procedures on the integumentary system on the extremities, anterior trunk and perineum; radical or modified radical procedures on with internal mammary node dissection 0 0
00410 ANES-INTEG EXTRM TRNK;CONVRT ARRYTH ANES-INTEG EXTREM TRUNK PERINEM;CONVERT ARRYTH Anesthesia for procedures on the integumentary system on the extremities, anterior trunk and perineum; electrical conversion of arrhythmias 0 0
00450 ANES-PROC CLAVICLE&SCAPULA; NOS ANESTHESIA PROCEDURES CLAVICLE AND SCAPULA; NOS Anesthesia for procedures on clavicle and scapula; not otherwise specified 0 0
My control file looks like,
LOAD DATA
INFILE 'CPTPROF02T.txt'
append
into table cpt_codes
fields terminated by X'9'
(
CPT_CODE,
STARRED,
SHORT_DESCR,
LONG_DESCR,
FULL_DESCR,
NF_TOTAL_RVU,
FACILITY_TOTAL_RVU,
STATUS,
PROC_CODE_EFF_DATE "TO_DATE('01/01/2002','MM/DD/YYYY')"
I don't see what the problem is..
It is not loading the data though, when i try to load it using source cpt2002.sh syntax..
Any ideas..?
Thank you!
|
|
|
|
| Re: Problem loading a tab delimited file..? an UPDATE.. [message #72648 is a reply to message #72647] |
Thu, 14 August 2003 16:50  |
Eva
Messages: 16
Registered: July 2001
|
Junior Member |
|
|
My file is loading partial data.. what that means is it is loading 5759 of 8000 records..
And the error i am getting is,
Record 5763: Rejected - Error on table CPT_CODES, column FULL_DESCR.
Field in data file exceeds maximum length
Any ideas on what that means??
Thank you!
|
|
|
|
| Re: Problem loading a tab delimited file..? an UPDATE.. [message #72650 is a reply to message #72648] |
Thu, 14 August 2003 20:24 |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
What that means is that some of the data in your fifth column in your tab-delimited file, that it is trying to load into the full_descr column of the cpt_codes table, is longer than the 1741 characters that the column allows. Or, it may be that your delimited file is missing some delimiters, so that two columns are concatenated together into one big column.
If the problem is just that your data is too long and you are not missing delimiters, then you can either take a substring of the data that goes into the full_descr column or increase the size of the full_descr column.
Here is the full text of the error message:
SQL*Loader-00621 Field in data file exceeds maximum length
Cause: A field exceeded its maximum allowable length. The maximum length is either the length specified in the SQL*Loader control file, or, for delimitable fields without a length specified, the default maximum length (255 bytes).
Action: Check for missing delimiters and/or shorten the field.
|
|
|
|
| Re: Problem loading a tab delimited file.? an UPDATE. [message #73602 is a reply to message #72650] |
Mon, 31 May 2004 10:15 |
Albert
Messages: 20
Registered: October 2002
|
Junior Member |
|
|
I tried the above and mae sure that all field delimiters were present. The one field that causes a problem is a field called notes defined as varchar2(4000). Even though the longest field is only 443 characters long, I still get the same error
Field in data file exceeds maximum length
Any thoughts? Thanks
Albert
|
|
|
|
| Re: Problem loading a tab delimited file.? an UPDATE. [message #73607 is a reply to message #73602] |
Tue, 01 June 2004 04:37 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Try taking substrings of any columns that you suspect are causing problems:
LOAD DATA
INFILE 'CPTPROF02T.txt'
append
into table cpt_codes
fields terminated by X'09'
trailing nullcols
(
CPT_CODE "SUBSTR(:cpt_code,1,5)",
STARRED,
SHORT_DESCR,
LONG_DESCR "SUBSTR(:long_descr,1,48)",
FULL_DESCR,
NF_TOTAL_RVU,
FACILITY_TOTAL_RVU,
STATUS,
PROC_CODE_EFF_DATE "TO_DATE('01/01/2002','MM/DD/YYYY')")
|
|
|
|
| Re: Problem loading a tab delimited file.? an UPDATE. [message #73608 is a reply to message #73607] |
Tue, 01 June 2004 04:53 |
Albert
Messages: 20
Registered: October 2002
|
Junior Member |
|
|
Thanks for the input.
I managed to find anoter solution by adding char(2000) in the listing of all the fields in the control file next to the column that had the long strings as in:
(...,...,...,notes char(2000),...,...,)
and that did it.
Mind you do not add varchar(2000) or whatever number or varchar2(2000). That will not work for some reason.
It has to be char(....)
Albert
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
