Solved

sqlldr - inserting data from text file into table

Posted on 2000-02-28
23
1,417 Views
Last Modified: 2012-06-21
I am trying to insert data into table from an text file. One of my field is having carriage returns in between such as given below :-

This is the
test data and we
cannot work with it

suppose the data field name is description. My problem is when I am using the following control file to load the data in sqlldr :-

LOAD DATA
INFILE 'jobs.dat'
APPEND
INTO TABLE JOBS
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(REQNUM,DESCRIPTION,CONTRACT,CONTOHIRE,FULLTIMEJOB)  

with the above control file it is not loading the data properly in description field all other fields it is loading data for first record. Wherever carriage returns are there in description field its breaking the information and taking data to new record in the first field such as reqnum. Can anybody help me out from this problem.
0
Comment
Question by:sanjayguptasolntec
  • 9
  • 4
  • 3
  • +6
23 Comments
 
LVL 3

Expert Comment

by:bkowalski
ID: 2566168
How is your data delimited?  Post a few records from your text file.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 2566355
SQL*Loader does not handle data files like this.

I know of only two options:
1. pre-process the data file manually with a text editor, or with a utility that can strip out the carriage returns and replace them with a space.

2. use utl_file to load the data instead of SQL*Loader.
0
 

Author Comment

by:sanjayguptasolntec
ID: 2566402
Hi Bkowalski,

Here are the sample data from my file. The problem is only with description field which is starting immediately after 3 in first record.

3002,10/19/95 10:35:31,"Phoenix Inc","Jennifer ","Orlando",3,"Technical Writer","Full-time","Seeking a Technical Writer for a great full-time opportunity. This Technical Writer will be responsible for documents that include: Installation and implementation documentation, online help and end user guides, technical references,
Detailed explanations of product concepts and functionality, and product release notes. This person will also be working closely with Product Mangement to plan documentation projects.

Requries AA (BS/BA preferred), and 2-5 years Technical Writing experience, the majority of which will be in the software industry, writing documentation for business, and financial applications.  Should have some experience with HTML authoring tools such FrontPage or PageMill, experience with FrameMaker, and familiarity with Adobe Acrobat products. Strongly desire candidates with experience working in a UNIX environment with RDBMS and client/server systems. Must work closely with product managers and developers and be able to meet deadlines.",0,0,1,0,0,2,5,"ASAP","BS/BA",,,,,,,,,,,"Technical Writing","Adobe Pagemill","FrontPage",,"FrameMaker",,"RoboHelp",,"HTML",
3003,10/19/98 14:15:57,"Xylon","Steve","San Jose",3,"Senior Solaris System Admin/Team Lead","Full-time","Seeking a Senior UNIX System Administrator with extensive experience in Sun Solaris administration. Will be responsible for 80+ servers and be a team lead for 3-5 other System Adminstrators. Requires 10+ years experience and comprehensive knowledge of the Sun environment; hardware, O/S, storage devices, network, DNS/NIS, NFS, backup systems, monitoring tools, etc. Must have strong leadership abilities and experience and strong project management skills. Experience with Win NT, Netscape Enterprise Applications, LDAP, Oracle Database, and Auspex, are all desired. This is a great opportunity to join an Industry Leader. ",0,0,1,0,0,6,12,"ASAP","BS/BA",,,,,,,,,,,"UNIX System Administration",,"Solaris",,"NFS",,"NT Administration",,,
3004,10/19/98 14:32:13,"Xilinx","Dawn Lombardi for Steve P.","South Bay",3,"Senior Solaris System Administrator","Full-time","Seeking a Senior UNIX System Administrator with extensive experience in Sun Solaris server-side administration. Will be part of a team responsible for 80+ servers. Requires 5+ years experience supporting mission critical UNIX Systems and comprehensive knowledge of the Sun environment; hardware, O/S, storage devices, network, DNS, NIS, NFS, backup systems, monitoring tools, etc. Must be a strong team player with superior technical abilities and strong project lead abilities.

Experience with Sun Clustering, Win NT, Netscape Enterprise Applications, LDAP, Oracle Databases, and Auspex, are strongly desired.

This is a great opportunity to join an Industry Leader!

Please reference the job number in the subject line of your reply.

** Your resume will be submitted with your permission only **
",0,0,1,0,0,6,8,"ASAP","BS/BA",75,90,,,100,"CA",16,,"$$DOE$$","Solaris 2.5/2.6, SPARC Servers, DNS, NFS, NIS, Win","UNIX System Administration","Cisco Routers","Solaris",,"NFS",,"NT Administration",,"UNIX Shell Scripting",
0
 

Author Comment

by:sanjayguptasolntec
ID: 2566408
markgeer thanks for your reply can you please tell me little more about utl_file utility.
0
 
LVL 4

Expert Comment

by:dda
ID: 2566634
You can also look at 'CONTINUEIF' parameter, but I can't find a way how to save newline characters... They are all ignored:

LOAD DATA
INFILE *
REPLACE
CONTINUEIF LAST != '*'
INTO TABLE ttest
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(id, txt)
BEGINDATA
1,AAAAA
AAAAA
AAAAA,*
2,BBBBB,*

0
 

Author Comment

by:sanjayguptasolntec
ID: 2566678
Hi dda thanks for your reply I am trying it and let you know the feedback on this.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 2566682
Utl_file is Oracle's PL\SQL package for handling file I/O (reading or writing) of ASCII files on the datasbe server.

The functions and procedures in utl_file can be used to open file, read or write a line of text, and close the file when finished.

To use utl_file, you first need at least one "utl_file_dir" entry in your init*.ora file.  This can be "utl_file_dir=*" to allow PL\SQL to read or write to/from any directory the server has access to.  (Note that some Unix system administrators may not want this.)  Use separate "utl_file_dir=[directory path]" entries in your init*.ora file, if necessary.

Using utl_file for data loading requires more manual coding than does SQL*Loader, and it is not as fast, but it is MUCH more flexible in terms of the type of input data it can handle.  It is only limited by your time and/or creativity in coding.
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 2568090
The problem with using SQL*LOADER for these records is that subsequent records are not directly linked  with the previous record.  One way around this is to use UTL_FILE (if loading procedures are on the database) or TEXT_IO if loading procedures are stored in a form.

Since the records you have are sequential, they are related physically witin the data file you are using.  You need to maintain a logical relationship that Oracle understands.

One method is to load the files into a temporary table and then process them in a second step.

Create a temp table and a couple of sequences, e.g.:

CREATE TABLE temp_data (
  record_id   NUMBER(10),
  group_id    NUMBER(10,
  rec_type        VARCHAR2(20),
  raw_data    VARCHAR2(100));

CREATE sequence record_id_seq
START WITH 1
INCREMENT BY 1;

CREATE sequence group_id_seq
START WITH 1
INCREMENT BY 1;

(add grants as required)

Then create a database procedure, e.g.:

PROCEDURE load_data AS

v_newline    VARCHAR2(100);
v_location   VARCHAR2(200);
v_filename   VARCHAR2(200);
v_file_handle  UTL_FILE.FILETYPE;      

BEGIN
   v_location := '/../../..' /* a unix file path goes here */
   --OR
   v_location := 'x:\...\...' /* or an NT filepath depending on the server the database is on */
   v_filename := 'the_file_name'

   v_file_handle := UTL_FILE.FOPEN(v_location,v_filename,'r');

LOOP
   BEGIN
      UTL_FILE.GET_LINE(v_file_handle, v_newline);
      IF UPPER(substr(raw_data,1,1)) = 'R' THEN
         INSERT INTO temp_data (record_id,group_id,rec_type, raw_data)
                        VALUES (record_id_seq.nextval,group_id_seq.nextval,'R',v_newline);
      ELSIF UPPER(substr(raw_data,1,1)) = 'F' THEN
         INSERT INTO temp_data (record_id,group_id,rec_type,raw_data)
                        VALUES (record_id_seq.next_val,group_id_seq.currval,'F',v_newline);

      ELSIF UPPER(substr(raw_data,1,1)) = 'L' THEN
         INSERT INTO temp_data (record_id,group_id,rec_type,raw_data)
                        VALUES (record_id_seq.next_val,group_id_seq.currval,'L',v_newline);
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         EXIT;
      WHEN OTHERS THEN
         /* Do some handling if you wish e.g. */
         DBMS_OUTPUT.PUT_LINE('AN ERROR OCCURRED FOR RECORD ' || v_newline);
   END;
END LOOP;

UTL_FILE.FCLOSE(v_file_handle);
COMMIT;

END load_data;

I would then use a second proc to load into a PL/SQL table all values that have a given group_id (i.e. one record of rollno, one of first name and one of last name) and then insert into your permanent table.

If you have any difficulties (e.g. get invalid_operation error) just give a shout.

Hope this helps...

JTrifts

0
 

Author Comment

by:sanjayguptasolntec
ID: 2569764
I am trying both the utilities such as sqlldr as well as utl_file. The strange this is happening that it is bring the value of my long field into the first field with carriage return breaks of different records whereas the position of long field is not the first its about 8th field in table.
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 2569831
I will withdraw my answer and let some others join in on the discussion...

But here are some other thoughts if using sqlldr.

I think SQLLDR might be recognizing the carriage return as the end of the record.  

Perhaps you can convert the carriage return into another non-used character (e.g. "~").  You could then convert the character back into a carriage return after the load.

Using UTL_file, you would also experience problems with the carriage return, but substituting it with a different caharacter could get around it.  You could then load the data into the long field and use PL/SQL and SQL to convert them back.

JTrifts
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 2569847
Oops.  I now see that Markgeer has already suggested the canversion of the carriage return.

If you do this, then you could follow the example I provided re: UTL_FILE to do the load.

To re-iterate though, I would expect UTL_FILE to process the carriage returns in the same way and so you would still be stuck with converting the carriage retrns into another character.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 2570022
Your data file looks like it will be very difficult to handle with any completely automated process.

I have used utl_file for data loading when the data for one record was on multiple lines, but I always had a clear "end-of-record" or "beginning-of-record" indicator character.  Then I wrote a PL\SQL procedure like this:
1. declare variables for the data
2. open the file for reading
3. loop
  read a line into a holding variable;
  move the data into the separate fields, or concatenate the lengthy data;
  check if this is the last line (or the first of the next record) and insert the data and clear the variables.
4. (end of loop)
5. close the file

I don't see an easy way for a program to recognize either the end of the record or the beginning of the next in your data file.  You may need to manually pre-process it first to add a return character at the end of the description and put a distinctive character as the only character of the following line as an "end-or-record" indicator.  Then utl_file would have no problem.
0
 

Author Comment

by:sanjayguptasolntec
ID: 2570484
Problem is not yet solved I am tyring various options given in sqlldr utility but I appreciate if some solution can be given to me.
0
 

Author Comment

by:sanjayguptasolntec
ID: 2570492
there must be some way to do this thing. The data is crucial and we need to convert it asap.
0
 
LVL 4

Expert Comment

by:sganta
ID: 2571573
Hi Sanjay,

Which version of Oracle you are using ? Please reply me ASAP.
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 2571830
sanjay,

You don't seem to be seriously considering Markgeer's or my suggestions using UTL_FILE.  He has given you the theory and I have provided you with sample code/syntax.  Why don't you at least give it a try?

I myself am working on a large conversion project.  I use both sqlldr and utl_file depending on the circumstance and how clean the data is.  In this case, I would use UTL_FILE.

If you get specific errors after trying what we suggest, we should be able to talk you through it.

Regards,

JTrifts
0
 
LVL 2

Expert Comment

by:mkooloos
ID: 2572044
Sanjay,

When you are sure your description is enclosed by '"', use [ CONTINUEIF LAST != '"' ], besides the [ FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ] you are already using.

Regards,

Michael


0
 

Author Comment

by:sanjayguptasolntec
ID: 2572855
sganta thanks a lot for your help I am using oracle 8.0.5. Yesterday after waiting for long time and no sucess in loading finally I exported data again from ms-access to oracle and now I am trying thru cursor to insert records from my table as it got long field also so the query insert into....... select from ...... is also having limitation so i found only solution to write cursor and insert records one by one. Is there any other short way u know please suggest me.
regards
sanjay gupta
0
 
LVL 3

Expert Comment

by:rkogelhe
ID: 2573758
Sanjay,

I had the same problem once. If you havn't solved the problem yet:

1) Be careful. Access can sometimes translate characters badly when sending to Oracle over ODBC. For example, the reverse quote.

2) Consider exporting with a special record terminator and SQL*Loading in stream record format (SRF) using the infile <datafile> "str 'term'" option.

The advantage of 2 is that it forces Access to translate certain characters to ASCII or PC format at which point when you import it becomes a bit simpler.

Regards,

Ryan
0
 

Author Comment

by:sanjayguptasolntec
ID: 2574332
rkogelhe
thanks a lot for you advice I will take care during my data conversion.
regards
sanjay
0
 
LVL 3

Accepted Solution

by:
jkstill earned 50 total points
ID: 2589761
There is no way that you can easily load this data as is with SQL*Loader or anything else.

Consider this; To SQL*Loader, a record is one line.  There may be ways to make SQL*Loader continue on another line.  I don't know if it can or not, RTFM is your job. :)

Even if SQL*Loader can do it, your data won't let you.

The only apparent way to clean this up is to use a filter ( Perl would be the obvious choice for this task ) to clean up the data before sending it to SQL*Loader.

By looking for closing quotes ( the text data is all enclosed in double quotes )  you could eliminate newlines in the data.

The preferred method though would be to clean this data up at the source, or at least clean it up when the data files are created by removing newlines from the text.

0
 
LVL 2

Expert Comment

by:mkooloos
ID: 2590583
Jkstill,

CONTINUEIF will do it...
0
 

Author Comment

by:sanjayguptasolntec
ID: 2592335
Finally I had to opt for it thru converting from odbc and then write cursor to bring data into my tables as per my new tables structure and why i wrote cursor b'cos i was having long field in my table and i could not use insert into ...... ( select * from ) other table. But I would like to solve this problem in near future.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now