Need help on SQL Loader for loading data

Hi Team,

I need to load the below data in to an table using sqlloader utility , Iam getting an error because of 3rd column data and 4th column data , how do i address these.

Software Programming ,Sam, ,"java
Oracle",
Software Programming ,raj,BR123,"java
Oracle",1000

Target Table
Degree varchar2(50)
StudentName varchar2(50)
PANNo VARCHAR2(40),
Courses VARCHAr2(80),
Fees number

The control file
LOAD DATA
INFILE 'D:\Data-Load\new-2013year.csv'
TRUNCATE
INTO TABLE StudentData_STG
FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED '"'
TRAILING NULLCOLS
(
Degree char(50)
StudentName char(50)
PANNo CHAR(40),
Courses CHAr(80),
Fees number
)
sam_2012Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
What is the error?

Is there a line break in the actual data?
0
sam_2012Author Commented:
Two problems
1. One line break
2. no data for the 3rd column . i get the below error
Record 1: Rejected - Error on table StudentData_STG column .PANNo
second enclosure string not present
0
slightwv (䄆 Netminder) Commented:
Just to confirm:  there is a line break in the CSV file?  So you need to load those 4 lines into two database rows?

>>Record 1: Rejected - Error on table StudentData_STG column .PANNo

Does the log file not tell you why it was rejected?  Do you have a not null constraint or is there a Unique Index on it and there is already a row with a space in it?

>>second enclosure string not present

Not sure I understand what you mean by that.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sam_2012Author Commented:
No ,
Actually , It has to be 2 records

Software Programming ,Sam, ,"java Oracle",
Software Programming ,raj,BR123,"java Oracle",1000

Because of the line break it is comming in 2 lines .

Also , in the input record if you see , 3rd column there is no value in the first record, this is causing an issue.  There is no index or constraint on any column
0
slightwv (䄆 Netminder) Commented:
So:  The CSV has 4 physical lines?

Does it ALWAYS have 2 lines per database row?

If so, I think I can work with that.  If not, there might be a problem.

I realize this probably won't give you what you want because of how it joins the rows but it loads otherwise.  The missing data doesn't cause any problems.

Setup:
drop table tab1 purge;
create table tab1(
Degree varchar2(50),
StudentName varchar2(50),
PANNo VARCHAR2(40),
Courses VARCHAr2(80),
Fees number
);

Open in new window


My control file (notice the CONCATENATE 2):
LOAD DATA
INFILE *
TRUNCATE CONCATENATE 2
INTO TABLE tab1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED '"'
TRAILING NULLCOLS
(
Degree char(50),
StudentName char(50),
PANNo CHAR(40),
Courses CHAr(80),
Fees char(50)
) 
BeginData
Software Programming ,Sam, ,"java
Oracle",
Software Programming ,raj,BR123,"java
Oracle",1000

Open in new window


My run:
sqlldr.exe user/password@orcl control=q1.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Apr 20 14:48:45 2018

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

Path used:      Conventional
Commit point reached - logical record count 2

Table TAB1:
  2 Rows successfully loaded.

Check the log file:
  q1.log
for more information about the load.

Open in new window


My results:
SQL> select * from tab1;

DEGREE                         STUDENTNAME                    PANNO                                    COURSES                                        FEES
------------------------------ ------------------------------ ---------------------------------------- ---------------------------------------- ----------
Software Programming           Sam                                                                     javaOracle
Software Programming           raj                            BR123                                    javaOracle                                     1000

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam_2012Author Commented:
awesome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.