CODEWITHSUNDEEP
oracle-databaseoracle11gsql-loader
user1463065
user1463065

Reputation: 595

Read data from CSV file(seperated by comma(,)) data using SQL loader when there is a comma(,) in data

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.

Upvotes: 1

Views: 5222

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

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>

Upvotes: 0

Related Questions