|
|
|
|
| Re: "field in datafile exceeds maximum length" for number field [message #452684 is a reply to message #452680] |
Fri, 23 April 2010 06:27   |
nprativa
Messages: 9
Registered: April 2010
|
Junior Member |
|
|
Hi Michel,
I have columns like country,company_owner for which i am using VARCHAR2 datatypes.For Country column i am using VARCHAR2(100) in my staging table.But while i am trying to load the data through sqlloader it fails and showing error message like
"Record 5: Rejected - Error on table XXX,, column COUNTRY.
Field in data file exceeds maximum length"
Then i am using
COUNTRY CHAR(100) NULLIF COUNTRY=BLANKS,
Then also the control file fails to load.
Can you please help me.
|
|
|
|
|
|
| Re: "field in datafile exceeds maximum length" for number field [message #452764 is a reply to message #452684] |
Fri, 23 April 2010 19:23 |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I received the following from Prativa via email:
Quote:
I want to load data from excel sheet through sqlloader.I have created a control file but everytimes it fails to load data.My CSV file contains some newline character for which it got errored out.Could you please suggest me how i can remove these newline character from my control file.Also for some columns it shows message like
"Field in data file exceeds maximum length".For one column though the CSV file contains " then also it shows message like "second enclosure string not present".What i can do to load my data
Please post all questions on the forums not via email, so that everyone may contribute and learn, which is the whole idea of the forums.
There are various ways of dealing with the newline character, depending on the cirumstances. You can use a text editor to remove them prior to processing or you can set sqlldr to recognize them, or you may need to use CONTINUEIF if it is part of your data. We would need to see your data, control file, and table structure to determine what is appropriate.
If it says that the field exceeds the maximum length, it can be because it does not recognize the end of the field and includes the next field in the length. Once again, we need to see the data, control file, and table structure.
The second enclosure string not present means what it says. If you say it is optionally enclosed by something and it sees the first something, but the second something is missing, then you get that error or perhaps the length error because it continued to the next field looking for the second something. Once again, we need to see the data, control file, and table structure to determine what is happening.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: "field in datafile exceeds maximum length" for number field [message #453046 is a reply to message #452910] |
Mon, 26 April 2010 17:18 |
|
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Since your load.csv file begins with a comma, it appears that your first column is blank, but that column is not null in your table, so you need to fix that by making it nullable or providing a default value.
The other problem is strange and although I can identify it, I don't have a clear solution for it, but if I can describe it, then perhaps you or someone else can figure out how to fix it. If I make the first column nullable and add a blank line to the top of the load.csv file, just by editing it, moving the cursor to the top left, then pressing the enter key once, and saving, then that row loads as it should. Without that blank line, it appears that something is throwing it off so that it is not reading things where they are and is trying to read one row as three and once it fails to find the second quote, then it thinks the rest is the beginning of the next row and so it is too long for those fields. Apparently you have some strange invisible character at the beginning of the row that is throwing everything off, so that it is trying to read the wrong data into the wrong fields.
[Updated on: Mon, 26 April 2010 17:20] Report message to a moderator |
|
|
|
|
|
| Re: "field in datafile exceeds maximum length" for number field [message #453200 is a reply to message #453067] |
Tue, 27 April 2010 17:57 |
|
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You need to decide how you want to handle null values for country, whether you want to make country nullable or provide a default value or reject null values or what.
You need to experiment with adding a blank line at the top of your data file and see if that helps. You only provided one row, so I can only say that it works for one row.
In the following example, I have changed only three things. I removed the not null from the country column in the table. I added a blank line to the top of the data file. I changed the directory path to match mine. I added some column formats, just to make the results easier to see.
-- c:\oracle11g\load.csv:
,111111,BCH,BCH,BDW,"Dear BDW Secondary Customer,
Please find attached a Debit Letter related to your BDW Secondary account.Please check all details carefully and let us know immediately of any issues.
All queries should be addressed to your known contact person at BDW Primary .",BDW Secondary Direct Debit Letter Attached
-- load.ctl:
LOAD DATA
INFILE 'c:\oracle11g\load.csv'
BADFILE 'c:\oracle11g\load.bad'
DISCARDFILE 'c:\oracle11g\load.dsc'
INSERT INTO TABLE MOT_STA_DATA
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(COUNTRY CHAR(100) NULLIF COUNTRY='(null)',
ENTITY CHAR(100),
SECTOR CHAR(100),
LANGUAGE_TYPE CHAR(100),
A_COMPANY_OWNER CHAR(100) NULLIF A_COMPANY_OWNER='(null)',
ORG_EMAILBODY CHAR(800) "TRIM(:ORG_EMAILBODY)",
ORG_EMAILSUBJECT CHAR(800)
)
-- table, load, and result:
SCOTT@orcl_11g> CREATE TABLE MOT_STA_DATA
2 (COUNTRY VARCHAR2(100),
3 ENTITY VARCHAR2(100) NOT NULL,
4 SECTOR VARCHAR2(100) NOT NULL,
5 LANGUAGE_TYPE VARCHAR2(100) NOT NULL,
6 A_COMPANY_OWNER VARCHAR2(100),
7 ORG_EMAILBODY VARCHAR2(800),
8 ORG_EMAILSUBJECT VARCHAR2(800));
Table created.
SCOTT@orcl_11g> HOST SQLLDR Scott/tiger CONTROL=load.ctl LOG=load.log
SCOTT@orcl_11g> COLUMN country FORMAT A7
SCOTT@orcl_11g> COLUMN entity FORMAT A6
SCOTT@orcl_11g> COLUMN sector FORMAT A6
SCOTT@orcl_11g> COLUMN language_type FORMAT A13
SCOTT@orcl_11g> COLUMN a_company_owner FORMAT A15
SCOTT@orcl_11g> COLUMN org_emailbody FORMAT A20 WORD_WRAPPED
SCOTT@orcl_11g> COLUMN org_emailsubject FORMAT A20 WORD_WRAPPED
SCOTT@orcl_11g> SELECT * FROM mot_Sta_data
2 /
COUNTRY ENTITY SECTOR LANGUAGE_TYPE A_COMPANY_OWNER ORG_EMAILBODY ORG_EMAILSUBJECT
------- ------ ------ ------------- --------------- -------------------- --------------------
111111 BCH BCH BDW Dear BDW Secondary BDW Secondary Direct
Customer, Debit Letter
Please find attached Attached
a Debit Letter
related to your BDW
Secondary
account.Please check
all details
carefully and let us
know immediately of
any issues.
All queries should
be addressed to your
known contact person
at BDW Primary .
SCOTT@orcl_11g>
|
|
|
|
|
|
| Re: "field in datafile exceeds maximum length" for number field [message #453339 is a reply to message #453319] |
Wed, 28 April 2010 05:33 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
nprativa wrote on Wed, 28 April 2010 11:31You are adding some column formatting.What is that column formatting.
BarbaraI added some column formats, just to make the results easier to see.
Column formatting is irrelevant here.
What you should have done is to provide the same amount of information as Barbara did in her previous post. Saying that "it doesn't work" means nothing.
Follow Barbara's steps and copy/paste your SQL*Plus session (as well as additional information, such as control file and sample data) so that we could see what you did.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
