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

sqlldr - inserting data from text file into table

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
sanjayguptasolntec
Asked:
sanjayguptasolntec
  • 9
  • 4
  • 3
  • +6
1 Solution
 
bkowalskiCommented:
How is your data delimited?  Post a few records from your text file.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
sanjayguptasolntecAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sanjayguptasolntecAuthor Commented:
markgeer thanks for your reply can you please tell me little more about utl_file utility.
0
 
ddaCommented:
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
 
sanjayguptasolntecAuthor Commented:
Hi dda thanks for your reply I am trying it and let you know the feedback on this.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
jtriftsMI and AutomationCommented:
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
 
sanjayguptasolntecAuthor Commented:
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
 
jtriftsMI and AutomationCommented:
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
 
jtriftsMI and AutomationCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
sanjayguptasolntecAuthor Commented:
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
 
sanjayguptasolntecAuthor Commented:
there must be some way to do this thing. The data is crucial and we need to convert it asap.
0
 
sgantaCommented:
Hi Sanjay,

Which version of Oracle you are using ? Please reply me ASAP.
0
 
jtriftsMI and AutomationCommented:
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
 
mkooloosCommented:
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
 
sanjayguptasolntecAuthor Commented:
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
 
rkogelheCommented:
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
 
sanjayguptasolntecAuthor Commented:
rkogelhe
thanks a lot for you advice I will take care during my data conversion.
regards
sanjay
0
 
jkstillCommented:
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
 
mkooloosCommented:
Jkstill,

CONTINUEIF will do it...
0
 
sanjayguptasolntecAuthor Commented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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