Load CSV with SQLLDR (Rejected)

Question

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.

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

Answer 1

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.