Load CSV with SQLLDR (Rejected)

i have CSV file. i want to load content of csv file to oracle database with SQLLDR.

My SQLLDR is

@echo off
sqlldr black@user/password data=D:\csv\data.csv control=D:\ctl\loader.ctl log=D:\ctl    \loader.log bad=D:\ctl\loader.bad
pause

My loader.ctl is

  OPTIONS (SKIP=1)
    LOAD DATA
    APPEND  
  INTO TABLE data2007
  FIELDS TERMINATED BY ',' TRAILING NULLCOLS
  ( number "TRIM (:number)",
    name "TRIM (:name)",
    total "TRIM (:total)",
  )

Table

CREATE TABLE DATA2007 (
    number      VARCHAR2(6),
    name        VARCHAR2(30),
    total       NUMBER NULL,
  )

My data.csv :

Number,name,Total
1,"Marlyn",2000
2,"Bobby",1000
3,"Rina",2000
4,"Robby,Mr",5000
5,"juliet,Mrs",5000

rows 1,2,3 = successful, but rows 4 and 5 rejected. I know what the problem are rows 4 and 5 there are four columns.

how to solve this problem?
during load can I insert the current date after "total" field?

Solution

You need to delimit the field so the comma between the double quotes is treated as just part of the value, not as a separator.

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

This should really be a separate question, but to load a fixed value you can either set the column to have a default value and not specify it in the control file at all, or optionally use a DEFAULTIF clause if you might sometimes want to override it; or more simply use an SQL string to get the value. Modifying your table definition to include a load_date field (and change the first column name from number to id, which is not a reserved word, and making it type NUMBER):

CREATE TABLE DATA2007 (
  id          NUMBER,
  name        VARCHAR2(30),
  total       NUMBER NULL,
  load_date   DATE
);

Then the control file is:

OPTIONS (SKIP=1)
  LOAD DATA
  APPEND
INTO TABLE data2007
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
( id "TRIM (:id)",
  name "TRIM (:name)",
  total "TRIM (:total)",
  load_date "SYSDATE"
)

Running that with your original data file gives:

Total logical records skipped:          1
Total logical records read:             5
Total logical records rejected:         0
Total logical records discarded:        0

And:

SELECT * FROM data2007;

        ID NAME                                TOTAL LOAD_DATE
---------- ------------------------------ ---------- -------------------
         1 Marlyn                               2000 2013-08-21 09:37:38
         2 Bobby                                1000 2013-08-21 09:37:38
         3 Rina                                 2000 2013-08-21 09:37:38
         4 Robby,Mr                             5000 2013-08-21 09:37:38
         5 juliet,Mrs                           5000 2013-08-21 09:37:38

5 rows selected.