Home » RDBMS Server » Server Utilities » SQL * Loader problem
SQL * Loader problem [message #233853] Fri, 27 April 2007 14:43 Go to next message
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 #233854 is a reply to message #233853] Fri, 27 April 2007 14:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Post a sample datafile,table structure and your LOGFILE.
Re: SQL * Loader problem [message #233855 is a reply to message #233853] Fri, 27 April 2007 14:56 Go to previous messageGo to next message
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 #233856 is a reply to message #233855] Fri, 27 April 2007 15:08 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
sqlldr should understand your terminator and fact that your rows have trailing null columns
try this.
load data
infile *
truncate
into table t1
FIELDS TERMINATED BY X'09' trailing nullcols
 (D1,
 D2,
 D3)
begindata
aaaaaa  bbbbb   cccc
fffjfj  fdfdf   fjjj
fjfjfj  jfjjf
jjjjjj  jjjjj
Re: SQL * Loader problem [message #234269 is a reply to message #233853] Mon, 30 April 2007 11:56 Go to previous messageGo to next message
houbcm
Messages: 14
Registered: April 2007
Junior Member
It is still not working. Could somebody tell me how to write the control file?

see attached for the data file
Re: SQL * Loader problem [message #234278 is a reply to message #234269] Mon, 30 April 2007 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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 #234296 is a reply to message #233853] Mon, 30 April 2007 14:28 Go to previous messageGo to next message
houbcm
Messages: 14
Registered: April 2007
Junior Member
Michel, thanks for your reply.

However, There are ten rows in my data files. Row 6 and 7 didn't get inserted.
Re: SQL * Loader problem [message #234298 is a reply to message #234296] Mon, 30 April 2007 14:35 Go to previous message
Michel Cadot
Messages: 64152
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
Previous Topic: SQL Loader ... Data file at http location
Next Topic: how to create target database while importing full database
Goto Forum:
  


Current Time: Sat Dec 10 10:41:10 CST 2016

Total time taken to generate the page: 0.08655 seconds