Home » RDBMS Server » Server Utilities » another sql*loader doubt with excel sheet (Oracle, 10g, windows XP)
another sql*loader doubt with excel sheet [message #338280] Mon, 04 August 2008 07:45 Go to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
No records in table using sql*loader

Here are the details:
The csv file: "mystates.csv" in C: dir {in excel sheet}
12, 	Research, 	Saratoga
10, 	Accounting,	 Cleveland

Table
CREATE TABLE testdept
(deptno NUMBER(2) NOT NULL,
 dname VARCHAR2(14),
 loc VARCHAR2(13));

control file is "mystates.ctl" in C:dir
LOAD DATA
INFILE 'c:\mystates.csv'
INTO TABLE testdept
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(deptno, dname, loc)


And in the command prompt i tried the following and got the o/p as
C:\>sqlldr macjyo/macjyo@testdb control='c:\mystates.ctl'
 
SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 4 18:03:23 2008
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
Commit point reached - logical record count 15
 
C:\>


Here it is not giving any error but bad file is created
and also no records in the table
SQL> select count(*) from testdept;
 
  COUNT(*)
----------
         0


Re: another sql*loader doubt with excel sheet [message #338283 is a reply to message #338280] Mon, 04 August 2008 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Here it is not giving any error but bad file is created

Check the content of the logfile.

Regards
Michel

Re: another sql*loader doubt with excel sheet [message #338285 is a reply to message #338283] Mon, 04 August 2008 07:55 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi michel

In log file it's giving the following error:
Table TESTDEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,  O(") CHARACTER            
DNAME                                NEXT     *   ,  O(") CHARACTER            
LOC                                  NEXT     *   ,  O(") CHARACTER            

Record 1: Rejected - Error on table TESTDEPT, column DEPTNO.
Field in data file exceeds maximum length
Record 3: Rejected - Error on table TESTDEPT, column DNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table TESTDEPT, column DNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 5: Rejected - Error on table TESTDEPT, column DNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 6: Rejected - Error on table TESTDEPT, column DNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 7: Rejected - Error on table TESTDEPT, column DNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 8: Rejected - Error on table TESTDEPT, column DNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 9: Rejected - Error on table TESTDEPT, column DNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 10: Rejected - Error on table TESTDEPT, column DNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 11: Rejected - Error on table TESTDEPT, column DNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 12: Rejected - Error on table TESTDEPT, column DNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 13: Rejected - Error on table TESTDEPT, column DEPTNO.
Field in data file exceeds maximum length
Record 14: Rejected - Error on table TESTDEPT, column DEPTNO.
Field in data file exceeds maximum length
Record 15: Rejected - Error on table TESTDEPT, column DEPTNO.
Field in data file exceeds maximum length
Record 2: Rejected - Error on table TESTDEPT, column DEPTNO.
ORA-01722: invalid number

Record 16: Rejected - Error on table TESTDEPT, column DEPTNO.
Field in data file exceeds maximum length

Table TESTDEPT:
  0 Rows successfully loaded.
  16 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.


Re: another sql*loader doubt with excel sheet [message #338286 is a reply to message #338285] Mon, 04 August 2008 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you have all the errors, fix them.

Regards
Michel
Re: another sql*loader doubt with excel sheet [message #338288 is a reply to message #338286] Mon, 04 August 2008 08:00 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
In the excel sheet i entered only 2 records,

But the log file is showing 15 records, why it is so???
Re: another sql*loader doubt with excel sheet [message #338296 is a reply to message #338288] Mon, 04 August 2008 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How could I know? I have not the file but maybe "Field in data file exceeds maximum length" will help you.

Regards
Michel

[Updated on: Mon, 04 August 2008 08:15]

Report message to a moderator

Re: another sql*loader doubt with excel sheet [message #338336 is a reply to message #338280] Mon, 04 August 2008 11:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As suggested in the BAD file
Quote:
Column not found before end of logical record (use TRAILING NULLCOLS)
modify control file as
FIELDS TERMINATED BY ','
TRAILING NULLCOLS              --> add this line


"15 lines in bad file" against "3 lines in data file" probably means that there are 12 empty lines behind the last meaningful line in the file; perhaps you'd want to delete them.
Re: another sql*loader doubt with excel sheet [message #351615 is a reply to message #338280] Wed, 01 October 2008 13:43 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
Hi, I met the exact same thing you encountered before. Can you share how to fix it.

THANK YOU very much.
Re: another sql*loader doubt with excel sheet [message #351616 is a reply to message #338280] Wed, 01 October 2008 13:52 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
Sorry, here it is my control file

load data
infile 'D:\Profiles\bonita\Desktop\loadfile.csv'
into table emp_excel
fields terminated by ","
optionally enclosed by '"'
trailing nullcols
(empno,ename,edate)

Got ' Commit point reached. Logical Record count 4 '. But nothing is table. Please let me know. Thank you.
Re: another sql*loader doubt with excel sheet [message #351618 is a reply to message #351616] Wed, 01 October 2008 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 04 August 2008 14:50
Quote:
Here it is not giving any error but bad file is created

Check the content of the logfile.

Regards
Michel



Re: another sql*loader doubt with excel sheet [message #351619 is a reply to message #351618] Wed, 01 October 2008 14:39 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
The problem is that the bad file contains all records from .csv file.

I used following command
c>sqlldr sa/sa@asd control=myloader.ctl bad = mybad.txt

mynad.txt shows extact the same data that be suposed in sa.emp_excel table.

thank you.
Re: another sql*loader doubt with excel sheet [message #351622 is a reply to message #351619] Wed, 01 October 2008 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 01 October 2008 20:58
Michel Cadot wrote on Mon, 04 August 2008 14:50
..
Check the content of the logfile.

Regards
Michel





Re: another sql*loader doubt with excel sheet [message #351628 is a reply to message #351622] Wed, 01 October 2008 15:08 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
It really confuse me , Sir.
The .csv shows below

empno,ename,edate
123,smith,6/3/2005
234,mark,5/20/2000
345,high,4/20/2001

Log file shows


SQL*Loader: Release 9.2.0.1.0 - Production on Wed Oct 1 14:58:13 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: myloader.ctl
Data File: d:\profiles\bonita\desktop\loadfile.csv
Bad File: mybad.txt
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 SA.EMP_EXCEL, 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
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * , CHARACTER
ENAME NEXT * , CHARACTER
EDATE NEXT * , CHARACTER

Record 1: Rejected - Error on table SA.EMP_EXCEL, column EMPNO.
ORA-01722: invalid number

Record 2: Rejected - Error on table SA.EMP_EXCEL, column EDATE.
ORA-01843: not a valid month

Record 3: Rejected - Error on table SA.EMP_EXCEL, column EDATE.
ORA-01843: not a valid month

Record 4: Rejected - Error on table SA.EMP_EXCEL, column EDATE.
ORA-01843: not a valid month


Table SA.EMP_EXCEL:
0 Rows successfully loaded.
4 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: 49536 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 4
Total logical records rejected: 4
Total logical records discarded: 0

Run began on Wed Oct 01 14:58:13 2008
Run ended on Wed Oct 01 14:58:13 2008

Elapsed time was: 00:00:00.15
CPU time was: 00:00:00.06

The table emp_excel structure

SQL> desc emp_excel;
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
EMPNO NUMBER
ENAME VARCHAR2(20)
EDATE DATE Y


All matchwe well. Do I need to add date format, and number format into control file ? May I bug your minutes to help me out ??

THANK YOU.
Re: another sql*loader doubt with excel sheet [message #351635 is a reply to message #351628] Wed, 01 October 2008 16:13 Go to previous message
Bonita
Messages: 32
Registered: June 2008
Member
Finally found a solution. Need to add format for each fields

Contrl File

load data
infile 'd:\profiles\bonita\desktop\loadfile.csv'
append
into table sa.emp_excel
fields terminated by ","
trailing nullcols
(empno integer external,
ename char ,
edate date "mm/dd/yyyy",
eage decimal external)

THANK YOU VERY MUCH.
Previous Topic: Problem with EXPDP
Next Topic: sqlldr inserts null when column data default exists
Goto Forum:
  


Current Time: Fri Dec 09 01:56:57 CST 2016

Total time taken to generate the page: 0.07665 seconds