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  |
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 #338285 is a reply to message #338283] |
Mon, 04 August 2008 07:55  |
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 #351616 is a reply to message #338280] |
Wed, 01 October 2008 13:52 |
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 #351619 is a reply to message #351618] |
Wed, 01 October 2008 14:39 |
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 #351628 is a reply to message #351622] |
Wed, 01 October 2008 15:08 |
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 |
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.
|
|
|
|
Goto Forum:
Current Time: Tue Dec 24 10:42:14 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
