Home » RDBMS Server » Server Utilities » need help with sql loader (8.1.7, win)
need help with sql loader [message #468670] Sun, 01 August 2010 02:09 Go to next message
ultgnp
Messages: 105
Registered: May 2010
Location: Germany
Senior Member
Hi,
I have an excel table whose data i have to transfer in oracle.
I did the following steps:

1) converted excel data in CSV format.
2) Created a control file test.ctl
LOAD DATA
INFILE      'C:\IDB Price List.csv'
BADFILE     'C:\IDB Price List.bad'
DISCARDFILE 'C:\IDB Price List.dsc'
INSERT INTO TABLE idb_price_list 
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(group_no,type,description,amt,flag)


3) when i run the sql loader utility, i get an error "sql-524: partial record found at end of datafile"


Where am i doing wrong. Please guide me
Re: need help with sql loader [message #468671 is a reply to message #468670] Sun, 01 August 2010 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Where am i doing wrong.

1/ You didn't post the table definition
2/ You didn't post an example of data that fails

What you have to do:
1/ Start SQL*Plus and post "DESC idb_price_list"
2/ In DOS box, post your SQL*Loader session
3/ Post your SQL*Loader log file

Regards
Michel

[Updated on: Sun, 01 August 2010 02:51]

Report message to a moderator

Re: need help with sql loader [message #468672 is a reply to message #468671] Sun, 01 August 2010 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SQL*Loader-00524: partial record found at end of datafile (string)
Cause: An incomplete record was found at the end of the indicated datafile.
Action: Make sure the last record in the datafile is complete and has the correct terminating character(s). Also, if fixed-length records are in use, verify that no record exceeds the platform-specific length for a single record.


Post the last record of the datafile.

Regards
Michel

[Updated on: Sun, 01 August 2010 02:52]

Report message to a moderator

Re: need help with sql loader [message #468673 is a reply to message #468670] Sun, 01 August 2010 03:17 Go to previous messageGo to next message
ultgnp
Messages: 105
Registered: May 2010
Location: Germany
Senior Member
create table idb_price_list
(
group_no varchar2(3),
stype varchar2(3),
sdescription varchar2(100),
samt number(5),
sflag varchar2(1)
)



C:\Documents and Settings\Administrator>sqlldr80 userid=ultgnp/test control=C:\test.ctl log=c:\IDB01.log

SQL*Loader: Release 8.1.7.0.0 - Production on Sun Aug 1 10:52:59 2010

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 50
Commit point reached - logical record count 100





Control File:   C:\test.ctl
Data File:      c:\idb01.txt
  Bad File:     C:\idb01.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 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table IDB_PRICE_LIST, 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
------------------------------ ---------- ----- ---- ---- ---------------------
GROUP_NO                            FIRST     *   ,  O(") CHARACTER            
STYPE                                NEXT     *   ,  O(") CHARACTER            
SDESCRIPTION                         NEXT     *   ,  O(") CHARACTER            
SAMT                                 NEXT     *   ,  O(") CHARACTER            
SFLAG                                NEXT     *   ,  O(") CHARACTER            

Record 1: Rejected - Error on table IDB_PRICE_LIST, column GROUP_NO.
ORA-01722: invalid number
Record 46: Rejected - Error on table IDB_PRICE_LIST, column GROUP_NO.
ORA-01722: invalid number

Record 47: Rejected - Error on table IDB_PRICE_LIST, column GROUP_NO.
ORA-01722: invalid number

Record 48: Rejected - Error on table IDB_PRICE_LIST, column GROUP_NO.
ORA-01722: invalid number

Record 49: Rejected - Error on table IDB_PRICE_LIST, column GROUP_NO.
ORA-01722: invalid number

Record 50: Rejected - Error on table IDB_PRICE_LIST, column GROUP_NO.
ORA-01722: invalid number

Record 51: Rejected - Error on table IDB_PRICE_LIST, column GROUP_NO.
ORA-01722: invalid number


MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table IDB_PRICE_LIST:
  0 Rows successfully loaded.
  51 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.


Space allocated for bind array:                  64500 bytes(50 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:           100
Total logical records rejected:        51
Total logical records discarded:        0

Run began on Sun Aug 01 10:49:34 2010
Run ended on Sun Aug 01 10:49:35 2010

Elapsed time was:     00:00:00.86
CPU time was:         00:00:00.02    


Re: need help with sql loader [message #468675 is a reply to message #468673] Sun, 01 August 2010 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not the same error than previously.
Fix your data.

Regards
Michel

[Updated on: Sun, 01 August 2010 12:13]

Report message to a moderator

Re: need help with sql loader [message #468691 is a reply to message #468673] Sun, 01 August 2010 10:36 Go to previous message
Barbara Boehmer
Messages: 7969
Registered: November 2002
Location: California, USA
Senior Member
It would help to see a few rows of sample data. The pieces that you have posted do not match. Your error message is saying that you are trying to load an invalid number into a number field, group_no, but your group_no is varchar2, not number. Typically, if your group_no were a number, the problem might be caused by leading or trailing spaces or by having group separators and decimal separators reversed, such as 1.234,5 instead of 1,234.5.
Previous Topic: Oracle export and import performance
Next Topic: sql loader
Goto Forum:
  


Current Time: Fri Aug 22 13:31:44 CDT 2014

Total time taken to generate the page: 0.13709 seconds