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.
oracle oracle11g sql-loader
edited Nov 15 '18 at 11:01
Alex Poole
133k6106179
asked Nov 15 '18 at 10:20
user1463065user1463065
2211516
add a comment |
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.
oracle oracle11g sql-loader
edited Nov 15 '18 at 11:01
Alex Poole
133k6106179
asked Nov 15 '18 at 10:20
user1463065user1463065
2211516
1
Your control file is OK and treats the fields properly (though maybe needs askipif 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
Nov 15 '18 at 11:04
add a comment |
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.
oracle oracle11g sql-loader
edited Nov 15 '18 at 11:01
Alex Poole
133k6106179
asked Nov 15 '18 at 10:20
user1463065user1463065
2211516
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.
oracle oracle11g sql-loader
oracle oracle11g sql-loader
edited Nov 15 '18 at 11:01
Alex Poole
133k6106179
asked Nov 15 '18 at 10:20
user1463065user1463065
2211516
edited Nov 15 '18 at 11:01
Alex Poole
133k6106179
asked Nov 15 '18 at 10:20
user1463065user1463065
2211516
edited Nov 15 '18 at 11:01
Alex Poole
133k6106179
edited Nov 15 '18 at 11:01
Alex Poole
133k6106179
edited Nov 15 '18 at 11:01
Alex Poole
133k6106179
133k6106179
asked Nov 15 '18 at 10:20
user1463065user1463065
2211516
asked Nov 15 '18 at 10:20
user1463065user1463065
2211516
asked Nov 15 '18 at 10:20
user1463065user1463065
2211516
2211516
1
Your control file is OK and treats the fields properly (though maybe needs askipif 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
Nov 15 '18 at 11:04
add a comment |
1
Your control file is OK and treats the fields properly (though maybe needs askipif 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
Nov 15 '18 at 11:04
1
1
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
Nov 15 '18 at 11:04
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
Nov 15 '18 at 11:04
add a comment |
1 Answer
1
active
oldest
votes
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>
answered Nov 15 '18 at 21:28
LittlefootLittlefoot
23.6k71534
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53317198%2fread-data-from-csv-fileseperated-by-comma-data-using-sql-loader-when-there%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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>
answered Nov 15 '18 at 21:28
LittlefootLittlefoot
23.6k71534
add a comment |
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>
answered Nov 15 '18 at 21:28
LittlefootLittlefoot
23.6k71534
add a comment |
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>
answered Nov 15 '18 at 21:28
LittlefootLittlefoot
23.6k71534
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>
answered Nov 15 '18 at 21:28
LittlefootLittlefoot
23.6k71534
answered Nov 15 '18 at 21:28
LittlefootLittlefoot
23.6k71534
answered Nov 15 '18 at 21:28
LittlefootLittlefoot
23.6k71534
answered Nov 15 '18 at 21:28
LittlefootLittlefoot
23.6k71534
23.6k71534
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53317198%2fread-data-from-csv-fileseperated-by-comma-data-using-sql-loader-when-there%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Your control file is OK and treats the fields properly (though maybe needs a
skipif 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
Nov 15 '18 at 11:04