Home » RDBMS Server » Server Utilities » Re: "field in datafile exceeds maximum length" for number field (splilt from hijacked thre
Re: "field in datafile exceeds maximum length" for number field (splilt from hijacked thre [message #452678] Fri, 23 April 2010 06:05 Go to next message
nprativa
Messages: 9
Registered: April 2010
Junior Member
Even though i am using
COL1 CHAR(500) NULLIF COL1=BLANKS,
then also i am getting same error for those columns.Can you please help me.
Re: "field in datafile exceeds maximum length" for number field [message #452680 is a reply to message #452678] Fri, 23 April 2010 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67652
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some errors? Which errors?
Post what you have and you get.

Regards
Michel
Re: "field in datafile exceeds maximum length" for number field [message #452684 is a reply to message #452680] Fri, 23 April 2010 06:27 Go to previous messageGo to next message
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 #452686 is a reply to message #452684] Fri, 23 April 2010 06:31 Go to previous messageGo to next message
Littlefoot
Messages: 21648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you post a control file, table description and a few sample records you are trying to load?
Re: "field in datafile exceeds maximum length" for number field [message #452764 is a reply to message #452684] Fri, 23 April 2010 19:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 #452906 is a reply to message #452764] Mon, 26 April 2010 03:32 Go to previous messageGo to next message
nprativa
Messages: 9
Registered: April 2010
Junior Member
Here i am posting my data file,Control file,table and what error i am getting.There are other ccolumns but i am getting error for these particular columns only.

  • Attachment: load.ctl
    (Size: 0.68KB, Downloaded 340 times)
Re: "field in datafile exceeds maximum length" for number field [message #452908 is a reply to message #452906] Mon, 26 April 2010 03:36 Go to previous messageGo to next message
nprativa
Messages: 9
Registered: April 2010
Junior Member
This is the data file
  • Attachment: load.csv
    (Size: 0.32KB, Downloaded 300 times)
Re: "field in datafile exceeds maximum length" for number field [message #452909 is a reply to message #452908] Mon, 26 April 2010 03:36 Go to previous messageGo to next message
nprativa
Messages: 9
Registered: April 2010
Junior Member
This is the table
  • Attachment: table.txt
    (Size: 0.38KB, Downloaded 285 times)
Re: "field in datafile exceeds maximum length" for number field [message #452910 is a reply to message #452909] Mon, 26 April 2010 03:37 Go to previous messageGo to next message
nprativa
Messages: 9
Registered: April 2010
Junior Member
This is the error mesaage i got.
  • Attachment: error.txt
    (Size: 1.67KB, Downloaded 296 times)
Re: "field in datafile exceeds maximum length" for number field [message #453046 is a reply to message #452910] Mon, 26 April 2010 17:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 #453067 is a reply to message #453046] Tue, 27 April 2010 00:57 Go to previous messageGo to next message
nprativa
Messages: 9
Registered: April 2010
Junior Member
Hi Barbara,
Thanks for your solution.But could you please let me know what is the way to load the data.
Re: "field in datafile exceeds maximum length" for number field [message #453200 is a reply to message #453067] Tue, 27 April 2010 17:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 #453319 is a reply to message #453200] Wed, 28 April 2010 04:31 Go to previous messageGo to next message
nprativa
Messages: 9
Registered: April 2010
Junior Member
Thank You for your solution but still i am getting same error.You are adding some column formatting.What is that column formatting.
Re: "field in datafile exceeds maximum length" for number field [message #453339 is a reply to message #453319] Wed, 28 April 2010 05:33 Go to previous message
Littlefoot
Messages: 21648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
nprativa wrote on Wed, 28 April 2010 11:31
You are adding some column formatting.What is that column formatting.

Barbara
I 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.
Previous Topic: SQL LOAD
Next Topic: imp/exp
Goto Forum:
  


Current Time: Sun Jan 24 16:54:25 CST 2021