SQL * Loader problem [message #233853] |
Fri, 27 April 2007 14:43 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
houbcm
Messages: 14 Registered: April 2007
|
Junior Member |
|
|
I imported data from a .csv file to oracle table. But the rows with empty values are not loaded. How to solve this problem? Here is my control file:
load data
infile 'c:/diagnosis.csv'
into table diagnosis
fields terminated by "," optionally enclosed by '"'
(D1,
D2,
D3)
Some of D3 values are empty.
|
|
|
|
Re: SQL * Loader problem [message #233855 is a reply to message #233853] |
Fri, 27 April 2007 14:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
houbcm
Messages: 14 Registered: April 2007
|
Junior Member |
|
|
My table is very simple:
table Diagnosis
Column: D1 char(100),
D2 char(100),
D3 char(100)
If the data are like the following:
(row 1) aaaaaa bbbbb cccc
(row 2) fffjfj fdfdf fjjj
(row 3) fjfjfj jfjjf
(row 4) jjjjjj jjjjj
If i import the above data: row 3 and 4 are not loaded.
[Updated on: Fri, 27 April 2007 15:00] by Moderator Report message to a moderator
|
|
|
|
|
Re: SQL * Loader problem [message #234278 is a reply to message #234269] |
Mon, 30 April 2007 12:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> create table diagnosis (d1 varchar2(30), d2 varchar2(30), d3 varchar2(30));
Table created.
C:\Documents and Settings\Michel\Mes documents>sqlldr michel/michel log=d.log control=diagnosis.ctl
SQL*Loader: Release 10.2.0.3.0 - Production on Lun. Avr. 30 19:46:03 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 10
C:\Documents and Settings\Michel\Mes documents>type diagnosis.ctl
load data
infile 'diagnosis.csv'
into table diagnosis
fields terminated by "," optionally enclosed by '"'
(D1,
D2,
D3)
SQL> select * from diagnosis ;
D1 D2 D3
------------------------------ ------------------------------ ------------------------------
Cranial Congenital Anencephaly
Cranial Congenital Aqueductal stenosis
Cranial Congenital Ataxia telangiectasia
Cranial Congenital Cerebral palsy
Cranial Congenital Chiari I malformation
sdffffffffffffffff fdssssssssssssss
fdssssssssss dfffffffffff dfddddddddd
sdffffffffffffffff dff
8 rows selected.
All works perfectly except your "c:/" must be "c:\"
Regards
Michel
|
|
|
|
Re: SQL * Loader problem [message #234298 is a reply to message #234296] |
Mon, 30 April 2007 14:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Sorry missed them.
Errors are:
Record 6: Rejected - Error on table DIAGNOSIS, column D3.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 7: Rejected - Error on table DIAGNOSIS, column D3.
Column not found before end of logical record (use TRAILING NULLCOLS)
So just add TRAILING NULLCOLS as Mahesh showed it.
Regards
Michel
|
|
|