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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
sam_2012Author Commented:
awesome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.