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

Question

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.

Your control file is OK and treats the fields properly (though maybe needs a skip if your data file has that header row). What is the actual error you get in the log file? The only obvious thing is that you're relying on the comma inside the salary being seeing as a group separator, and the groups in the last row look odd; but those would give ORA-01722 rather than ignoring the double-quotes. — Alex Poole
– Alex Poole, Commented Nov 15, 2018 at 11:04

Littlefoot · Accepted Answer · 2018-11-15 21:28:11Z

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>

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related