Odtnhj

I am loading CSV file data into table called EMPLOYEE using SQL*Loader.

My CSV file data is separated by commas (,):

EMPID,EMPNAME,SALARY,GRADE
123,Rams,1000,A1
124,Sand,"2,000",A2
125,Bhas,"3,00,000",A3

and my control file is:

LOAD DATA
Insert INTO TABLE EMPLOYEE
Fields terminated by "," Optionally enclosed by '"' TRAILING NULLCOLS
(
EMPID,
EMPNAME,
SALARY,
GRADE
)

When I load the data using above control file, the first record is loading fine and from the second record there is a problem as there is a comma in salary field (salary is loading in multiple column as there is a comma).

In my data salary field we are getting in double quotes though it has a comma(,). SQL*Loader should consider the value between the double quotes as a single field and salary also should get load properly.

Please suggest changes to be done to load the data properly.

A sample table:

SQL> create table test
 2 (empid number,
 3 empname varchar2(20),
 4 salary varchar2(20),
 5 grade varchar2(2));

Table created.

SQL>

Control file:

load data 
infile *
replace
into table test
fields terminated by ',' optionally enclosed by '"' trailing nullcols
( 
 empid,
 empname,
 salary,
 grade
)

begindata
123,Rams,1000,A1
124,Sand,"2,000",A2
125,Bhas,"3,00,000",A3

Loading session:

SQL> $sqlldr scott/tiger@xe control=test03.ctl log=test03.log

SQL*Loader: Release 11.2.0.2.0 - Production on ╚et Stu 15 22:27:27 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL>

Result:

SQL> select * From test;

 EMPID EMPNAME SALARY GR
---------- -------------------- -------------------- --
 123 Rams 1000 A1
 124 Sand 2,000 A2
 125 Bhas 3,00,000 A3

SQL>