• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 754
  • Last Modified:

load a multi-line field line using Oracle SQL LOADER

I have a simple file attached. It has 4 columns, and the last column had multiple lines in each of its field. It is shown in .CSV file(attached)
How am I supposed to load it in Oracle table, which is built with same structure.

Any kind of help is greatly appreciated.

Thanks,
Vishal
----This is what is present in BAD FILE below(could not upload it)-----

HRDDSCAVHU,7/12/2002,0,"Date Saved: 7/12/2002

"
HRDDSCAVHU,7/12/2002,2,"
This is an online test which will assist staff to search for information on the agency intranet.  Hints are offered to staff that may not have confidence.  Online test form will ""dump"" participant's answers into an Access databse.  Completion will be noted and recorded in Registrar.  Completion reports will be generated and a follow up will be done with all staff who have not completed.  

"
HRDDSCAVHU,7/31/2002,0,"Date Saved: 7/31/2002

"
HRDDSCAVHU,7/31/2002,2,"
This is an online test which will assist staff to search for information on the agency intranet.  Hints are offered to staff that may not have confidence.  Online test form will ""dump"" participant's answers into an Access databse.  Completion will be noted and recorded in Registrar.  Completion reports will be generated and a follow up will be done with all staff who have not completed.  

"
HRDDNEONET,4/27/2004,0,"Date Saved: 4/27/2004

"
HRDDNEONET,4/27/2004,2,"

"
BTDTORB,11/16/2005,0,"Date Saved: 11/16/2005

"
BTDTORB,11/16/2005,2,"
Organizational structure and mission of TCB; how goal setting works in TCB; the rehabilitation team; federal and state legislation; attitudes and misconceptions about blindness; functional aspects of vision limitations; sighted guide techniques; how blindness affects the family and others; overview of adaptive technology; introduction to other resources; advocacy and rights of people who are blind or visually impaired.

"
HRDTADVTTT,4/24/2009,0,"Date Saved: 4/24/2009

"
HRDTADVTTT,4/24/2009,2,"

"
HRDDMAPS,5/8/2009,0,"Date Saved: 5/8/2009

"
HRDDMAPS,5/8/2009,2,"

"
Course Code              	: HRDDSCAVHU
Class name               	: SCAVENGER HUNT
Start date               	: 7/18/2002
End date                 	: 8/19/2002
Start time               	: 08:00
End time                 	: 17:00
Contact hours            	:    0.00
Capacity                 	:  100
Sessions                 	:   1
Location                 	: ONLINE TRAINING
Out-of-state training?   	: 
Room name                	: OTHER
Lead trainer             	: KATHLEEN NEVILLE
Lead support             	: CONNIE POMIKAHL
Instructor               	: KATHLEEN NEVILLE
Sponsor                  	: TCB
Number of CEU's awarded  	: 
Charge per seat          	:        0.00
Charge per class         	: 
Training grant           	: N
Room rental budget       	:   0.00
Consultant budget        	:   0.00
Class canceled?          	: 
Address 1                	: 
Date last update         	: 7/12/2002
Exceed capacity          	: 
Check prereq             	: 
Course conflict          	: 
Time Conflict            	: 
Import date              	: 
Close date               	: 
User ID                  	: Connie
Cost tracking            	: 
Target Audience - All staff  will be enrolled (mandatory).  
After this course, students will be able to:
Know what information and resources are located on the intranet.
Be able to use online manuals and perform the following tasks:
  performing search using search engine and browser find feature;
  copy and paste selected sections into Word document;
  print selected sections - regular print, large print, braille;
  send e-mail to manual manager.
Locate and use links provided in intranet home page (via guided tour)
Work with multiple windows open.
Course Code              	: HRDDSCAVHU
Class name               	: SCAVENGER HUNT
Start date               	: 7/31/2002
End date                 	: 8/23/2002
Start time               	: 08:00
End time                 	: 17:00
Contact hours            	:    0.00
Capacity                 	:  100
Sessions                 	:   1
Location                 	: ONLINE TRAINING
Out-of-state training?   	: 
Room name                	: OTHER
Lead trainer             	: KATHLEEN NEVILLE
Lead support             	: CONNIE POMIKAHL
Instructor               	: KATHLEEN NEVILLE
Sponsor                  	: TCB
Number of CEU's awarded  	: 
Charge per seat          	:        0.00
Charge per class         	: 
Training grant           	: N
Room rental budget       	:   0.00
Consultant budget        	:   0.00
Class canceled?          	: 
Address 1                	: 
Date last update         	: 7/31/2002
Exceed capacity          	: 
Check prereq             	: 
Course conflict          	: 
Time Conflict            	: 
Import date              	: 
Close date               	: 
User ID                  	: Connie
Cost tracking            	: 
Target Audience - All staff  will be enrolled (mandatory).  
After this course, students will be able to:
Know what information and resources are located on the intranet.
Be able to use online manuals and perform the following tasks:
  performing search using search engine and browser find feature;
  copy and paste selected sections into Word document;
  print selected sections - regular print, large print, braille;
  send e-mail to manual manager.
Locate and use links provided in intranet home page (via guided tour)
Work with multiple windows open.
Course Code              	: HRDDNEONET
Class name               	: GUIDED TOUR OF TCB INTRANET & ONLINE MAN
Start date               	: 2/6/2002
End date                 	: 2/6/2002
Start time               	: 09:30
End time                 	: 11:30
Contact hours            	:    0.00
Capacity                 	:   15
Sessions                 	:   1
Location                 	: TELECOURSE
Out-of-state training?   	: 
Room name                	: OTHER
Lead trainer             	: KATHLEEN NEVILLE
Lead support             	: CONNIE POMIKAHL
Instructor               	: KATHLEEN NEVILLE
Sponsor                  	: TCB
Number of CEU's awarded  	: 
Charge per seat          	:        0.00
Charge per class         	: 
Training grant           	: N
Room rental budget       	:   0.00
Consultant budget        	:   0.00
Class canceled?          	: 
Address 1                	: 
Date last update         	: 2/15/2002
Exceed capacity          	: 
Check prereq             	: 
Course conflict          	: 
Time Conflict            	: 
Import date              	: 
Close date               	: 
User ID                  	: Connie
Cost tracking            	: 
An introductory course into how to use online manuals and how to find and use resources located on Division for Blind Services'  intranet.  Participants will receive a hands-on tour of the online manuals and other features, at their workstation, over their telephone.  Exercises will be given to provide practice opportunities.  Job aids will be developed for basic tasks that are covered.  A follow-up activity (Intranet Scavenger Hunt) will be required of all staff, whether they took this class or not, to test for basic skill levels in work-related internet skills needed to use the intranet.
Target Audience - All new agency staff, during their first week of hire (mandatory).  To all agency staff as an elective course, if they believe they can benefit from it.
After this course, students will be able to:
Know what information and resources are located on the intranet.
Be able to use online manuals and perform the following tasks:
  performing search using search engine and browser find feature;
  copy and paste selected sections into Word document;
  print selected sections - regular print, large print, braille;
  send e-mail to manual manager.
Locate and use links provided in intranet home page (via guided tour)
Work with multiple windows open.
One 2-hour telecourse session, limit of 10 participants per session.  Job aids, hands-on exercises, group discussion, lecturette.  Follow-up test.  Participants who use adaptive technology will have an opportunity to receive alternative JAWS-related training before the session from Belinda Lane, our adaptive technology trainer.
Course Code              	: BTDTORB
Class name               	: ORIENTATION TO REHABILITATION & BLINDNES
Start date               	: 10/13/2003
End date                 	: 10/17/2003
Start time               	: 01:00
End time                 	: 04:45
Contact hours            	:   32.00
Capacity                 	:   30
Sessions                 	:   5
Location                 	: AUSTIN
Out-of-state training?   	: 
Room name                	: 1ST FLOOR CONF. ROOM
Lead trainer             	: BILLY BROOKSHIRE
Lead support             	: BARBARA HOFFMAN
Instructor               	: 
Sponsor                  	: TCB
Number of CEU's awarded  	: 
Charge per seat          	:        0.00
Charge per class         	: 
Training grant           	: N
Room rental budget       	:   0.00
Consultant budget        	:   0.00
Class canceled?          	: 
Address 1                	: 
Date last update         	: 10/17/2003
Exceed capacity          	: 
Check prereq             	: 
Course conflict          	: 
Time Conflict            	: 
Import date              	: 
Close date               	: 6/25/1999
User ID                  	: Barbara
Cost tracking            	: 
1-2:10	Introduction (Scott/All)
2:10-2:30	Agency goals/mission (Barbara Madrigal)
2:45-4:45	Rehab teamwork/org chart/dept overview (Nancy/Sue/All)
8:30-9:15	Protective techniques (All)
9:15-11	Sighted guide/with break (Sue/All)
1-3:30	Working without sight/with break (Gloria/Sue/All)
3:30-4:30	Touch of technology (Sue)
8:30-10	Coping with the sighted world panel (Billy)
10:15-11	Legislative review (Scott)
11-11:45	Dog guides (Nancy)
1:15-2:45	Attitudes/low vision (Gloria/Billy/Sue)
3-4:30	Attitudes/low vision (Gloria/Billy/Sue)
8:30-8:45	Legislative review (Billy)
8:45-9:45	Legilsative jeopardy (Scott/All)
10-11:30	Braille (Gloria/All)
1-4:30	Disability simulation (Sue)
8:30-9:15	Simulation debriefing (Gloria)
9:15-10:30  Stages of grief (Gloria/Billy)
10:45-11:05  Charge (Scott/Terry Murphy)
11:05-12	Wrap-up - Delta Chart and 184's (Kat/All)
Training topics included in this one-week program:
Trainees will participate in a number of activities conducted under blindfold.
Required training for all new employees.
Course Code              	: HRDTADVTTT
Class name               	: ADVANCED TEACHER TECHNIQUES TRAINING
Start date               	: 9/7/2009
End date                 	: 9/11/2009
Start time               	: 01:00
End time                 	: 12:00
Contact hours            	:   32.00
Capacity                 	:   15
Sessions                 	:   5
Location                 	: CCRC
Out-of-state training?   	: 
Room name                	: 
Lead trainer             	: SMCCRACKEN/GBOWMAN
Lead support             	: BARBARA HOFFMAN
Instructor               	: 
Sponsor                  	: BT&D
Number of CEU's awarded  	: 
Charge per seat          	: 
Charge per class         	: 
Training grant           	: N
Room rental budget       	: 
Consultant budget        	: 
Class canceled?          	: 
Address 1                	: 
Date last update         	: 4/24/2009
Exceed capacity          	: 
Check prereq             	: 
Course conflict          	: 
Time Conflict            	: 
Import date              	: 
Close date               	: 
User ID                  	: Barbara
Cost tracking            	: 
This one week training will provide participants from the Center and the field with:
- a better understanding of the differences in teaching environment
- a better understanding of scheduling and paperwork requirements
- an opportunity to observe field teaching services and Center teaching services in one-on-one sessions and group classes, if available
- an opportunity to participate in teaching sessions with field teaching and CCRC teaching staff
- a debriefing with the field teaching staff or CCRC staff regarding the outcome of experiences
Participants- Individuals as follows:
- CCRC staff as selected by supervisors
- Field Independent Living Workers
Location:  various district and regional offices and CCRC
Course Code              	: HRDDMAPS
Class name               	: MAPS: 2009 ONLINE SURGERY / LOW VISION
Start date               	: 1/1/2009
End date                 	: 12/31/2009
Start time               	: 18:00
End time                 	: 18:30
Contact hours            	:    0.00
Capacity                 	:  100
Sessions                 	:   1
Location                 	: ONLINE
Out-of-state training?   	: 
Room name                	: 
Lead trainer             	: ONLINE
Lead support             	: CHRISTINE PENNY
Instructor               	: 
Sponsor                  	: BT&D/DBS
Number of CEU's awarded  	: 
Charge per seat          	:        0.00
Charge per class         	: 
Training grant           	: N
Room rental budget       	:   0.00
Consultant budget        	:   0.00
Class canceled?          	: 
Address 1                	: 
Date last update         	: 5/8/2009
Exceed capacity          	: 
Check prereq             	: 
Course conflict          	: 
Time Conflict            	: 
Import date              	: 
Close date               	: 
User ID                  	: Christine
Cost tracking            	: 
This online course is designed to familiarize participants with the MAPS coding system, basic procedures for planning and purchasing medical services and low vision devices, and guidelines for entering information into Rehab Works. The course is divided into two modules and is partially self-paced. That is to say, both modules must be successfully completed within one month

Open in new window

corsgen.csv
corsgen.log
0
vishal_singh
Asked:
vishal_singh
  • 4
  • 3
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
can you provide me your control file and the table structure with its data types, so i can try to modify it to make it work.
0
 
gatorvipCommented:
Open the text file in a hex editor and examine the contents. There are two options:

a) the line ends with CR LF and your text field only contains CR or LF (not both) - then set the record delimiter in your control file to CR LF ( chr(10)|| chr(13) )
b) the line ends with CR LF and your text field contains the same - in this case you will need to preprocess the file to "clean up" the unneeded whitespace.
0
 
vishal_singhAuthor Commented:
Hello gatorvip,

I dont have hex editor with me. All I can do is to open up the text file in the txt format.

nav_kum_v: Please fiund my control file as per your request. And also table structure
-------------CORSGEN.CTL-----------------
OPTIONS(SKIP=1, DIRECT=TRUE, ERRORS = 500)
UNRECOVERABLE
LOAD DATA 
INFILE          'C:\sql_loader\DBS_foxpro\corsgen.csv'
BADFILE         'C:\sql_loader\DBS_foxpro\corsgen.bad'
DISCARDFILE     'C:\sql_loader\DBS_foxpro\corsgen.dsc'
TRUNCATE 
INTO TABLE dbs_corsgen
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
xcode	          char,
cdate  	    date "MM/DD/YYYY",
folder   	    char,
text            char)


---------------------Table Structure--------------

create table DBS_corsgen(
xcode	  varchar2(25),
cdate    varchar2(45),
folder         number,
text    varchar2(30)
)
tablespace EXAMPLE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
before i can try, a basic thing which i have spotted is the TEXT column in your table is just having length 30 but the data in your .csv is around in hundreds.

any idea why ?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
for example, the below is the data in your csv file for the TEXT column of the first record and it can never get loaded into 30 length as it has more data as shown below...

Date Saved: 7/12/2002
Course Code                    : HRDDSCAVHU
Class name                     : SCAVENGER HUNT
Start date                     : 7/18/2002
End date                       : 8/19/2002
Start time                     : 08:00
End time                       : 17:00
Contact hours                  :    0.00
Capacity                       :  100
Sessions                       :   1
Location                       : ONLINE TRAINING
Out-of-state training?         :
Room name                      : OTHER
Lead trainer                   : KATHLEEN NEVILLE
Lead support                   : CONNIE POMIKAHL
Instructor                     : KATHLEEN NEVILLE
Sponsor                        : TCB
Number of CEU's awarded        :
Charge per seat                :        0.00
Charge per class               :
Training grant                 : N
Room rental budget             :   0.00
Consultant budget              :   0.00
Class canceled?                :
Address 1                      :
Date last update               : 7/12/2002
Exceed capacity                :
Check prereq                   :
Course conflict                :
Time Conflict                  :
Import date                    :
Close date                     :
User ID                        : Connie
Cost tracking                  :
.
.
.

why don't you increase the length of the text column in your table so that it can hold the max length data which is present in your csv file and give it a retry ?

Thanks,
0
 
vishal_singhAuthor Commented:
Let me try it
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
ok. let me know how it goes once you are done with your testing.
0
 
vishal_singhAuthor Commented:
thanx
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now