0

i have a major error message generated in my log file as i tried loading with sql loader

i created a control file named Ad2.ctl with this

and the loading command with 

sqlldr scott/tiger@MYDB CONTROL='Ad2.ctl' LOG='Ad2.log'

the content of the file include the following sample

After runing the sql loader command i get a long list of error and the ADDRESS table was not populated.

the errors include the following

SQL*Loader: Release 10.2.0.3.0 - Production on Wed Sep 12 08:47:28 2012

 Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 Control File:   Ad2.ctl
 Data File:      Abbeyruntest2.csv
  Bad File:     Abbeyruntest2.bad
  Discard File: Abbeyruntest2.dis 
 (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 ADDRESS, loaded from every logical record.
Insert option in effect for this table: APPEND


Record 2: Rejected - Error on table ADDRESS, column ADDRESSAREA.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table ADDRESS, column ADDRESSAREA.
Column not found before end of logical record (use TRAILING NULLCOLS)

Appologies for posting the long errors as am a newbee and am confused with it.

Abiodun
  • 959
  • 6
  • 17
  • 38
  • you need to show 2 records from your CSV... – Yahia Sep 12 '12 at 08:13
  • Is that really sample data from your file, or just your explanation of it? Based on the control file it should be comma-separated so please show some actual data from the file. It sounds like you have more columns in the control file than in the data file. And the record-1 rejection suggests there's a header row in the data, is that right? – Alex Poole Sep 12 '12 at 11:56
  • @ Alex Poole, there is a header row in it. i have updated it with the commas. – Abiodun Sep 12 '12 at 12:19

2 Answers2

1

It seems the problem lies in the quotes in you csv file.

I found an article here on how to load such files.

LOAD DATA
APPEND INTO TABLE testing
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
  a
 ,b  "replace ( :b ,'"' ,'' )"
)
winkbrace
  • 2,682
  • 26
  • 19
0

Your CSV file does not correspond at all with your control file. You have way too many commas in your CSV file - each comma delimits a new field, according to the FIELDS TERMINATED BY ',' clause. I modified your data thusly, and it worked:

1,FLAT,                    ,1  ,Abesinia Passage   , 
2,Flat e-1,Edmund's Home   ,1a ,Arena's Palace Lane, 
3,flat 1,`Anderson's House',11a,                   ,Laguna Estate

If you can't modify your CSV file, then you'll need to define FILLER fields in your control file so that the field specifications line up with the actual data.

DCookie
  • 42,630
  • 11
  • 83
  • 92