Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Easiest ways to import a gig and a half of pipe-delimited text into an Oracle 9i table

Posted on 2005-02-25
15
Medium Priority
?
788 Views
Last Modified: 2012-06-27
Greetings experts,

I have 8 text files of data, all exactly the same format (which happens to be both pipe-delimited *and* fixed width mysteriously enough, i.e. 30 characters, pipe, 20 characters, pipe etc).  The sizes of these files range from 11 Megs to 832 Megs, total about a gig and a half of data (about 500,000 rows).

I want to append all of the rows from these text files to an existing table in an Oracle 9i database.  Some of the fields are VARCHAR, some NUMBER, some FLOAT, and one DATE.  This is the only "transformation" that's necessary - just converting the all-text file to the appropriate datatypes.

Not all of the fields in the destination table will be populated during the import.  There is a trigger on the table that will put default values in some of the fields and the remainder can just be left blank.

What are some of the easiest ways to accomplish this?

Thanks
Joe
0
Comment
Question by:DalTXColtsFan
  • 4
  • 3
  • 3
  • +4
14 Comments
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 13404339
I should add that it seems like the row delimiter in the text files is just [CR], doesn't seem to be [CR][LF] like it usually is.
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 200 total points
ID: 13404558
Try to SELECT the files defining EXTERNAL TABLES on them.
This example with some evident modification will help you.
You can only SELECT external tables, so you can create regular tables filtering the external tables:

create table new_table as (select * from lsmedical.USERMEDBASIC_EXT where PZN = 'JOHN');

create directory med as 'c:\med' ; -- on main
GRANT READ  ON DIRECTORY med TO lsmedical;
GRANT WRITE ON DIRECTORY med TO lsmedical;



drop table lsmedical.USERMEDBASIC_EXT;
create table lsmedical.USERMEDBASIC_EXT (
                   PZN  VARCHAR2(33),
                   NAME VARCHAR2(111)         )
    organization external  
    (type oracle_loader  
     default directory med
     access parameters (records delimited by newline
     fields terminated by '|')
     location ('med-de-formatted.txt','med-de-insulin-formatted.txt',
               'med-us-formatted.txt','med-us-insulin-formatted.txt'));
0
 
LVL 9

Expert Comment

by:neo9414
ID: 13404759
or you can use sql loader to load the data into your table directly.

Make sure you don't use direct path inport as it woll not fire the trigger.

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 300 total points
ID: 13405648
I agree that defining these as external tables should be the fastest, if it works.  I often have trouble though with date columns when trying to use SQL*Loader, and since that mechanism is what Oracle uses for "external" tables, that column may cause a problem.  If you can't get this to work with "external' tables, then either create a staging table with all varchar2 columns and use use SQL*Loader to load that, then SQL or PL\SQL commands to move the date to the target table, or write a PL\SQL procedure that uses utl_file to read the data files, then do the inserts.
0
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 13405755
Is SQL*Loader a third-party utility, or is it possible that I have it and just can't find it?
0
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 13405778
Never mind I found this:

http://www.orafaq.com/faqloadr.htm
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13405793
SQL*Loader is an Oracle utility that is almost always installed along with the Oracle database software on the server when the database software is installed.  If you have the imp (import) and exp (export) utilities, I am sure that you have SQL*Loader also.  The executable is likely named: sqlldr.
0
 
LVL 9

Expert Comment

by:neo9414
ID: 13406262
you should have it on your client machine as well. just check it

c:> sqlldr

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13406300
You have to be carefull with using sqlldr on a client machine.  First of all, it is not normally installed on clients as part of a typical Oracle client installation.  If it is installed, it must be the same version of Oracle as the database or there will likely be problems.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13413043
SQL*Loader is great, but since you can't use direct path for your load, I'd use the EXTERNAL table method instead.
0
 
LVL 13

Expert Comment

by:riazpk
ID: 13413522
Through multiple instances of SQL*Loader for different files............parallelism....

you will be able to do faster in this way:

sqlldr user/pass control=c1.ctl
sqlldr user/pass control=c2.ctl
sqlldr user/pass control=c3.ctl
sqlldr user/pass control=c4.ctl

and so on





0
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 13416939
SQL Loader Is the best option. Try with "DIRECT=TRUE" for faster operation. Note that in case you have some constraints on the target table, you should disable it for direct path loading.


Consider this example,

Control File:

load data
INFILE 'C:\<your_dat_file_name>'
INTO TABLE <your_table>
APPEND
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
CUSTID,
FORM_NO,
CARDNO,
FIRST_NAME,
LAST_NAME,
MAILINGADD,
CARD_IS_DT,
CITY,
PINCODE)



Contains for the BAT file for running SQLLDR,

sqlldr userid=user_name/password@tns_str control=C:\<control_file_name>.ctl log=C:\<log_file_name>.log DIRECT=TRUE


Sujit
0
 
LVL 9

Expert Comment

by:neo9414
ID: 13417041
sujit_kumar:
Direct = true is not an option for DalTXColtsFan . He has a trigger on the table which needs to be fired to insert the default values
0
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 14311763
It turned out that the data in those text files was all over the frickin place, leaving chr$(0) characters in some places, illegal and horrible characters in others etc.  I ended up demanding that the client send it to me in Access (ended up being 2 huge Access files), importing those into staging tables in Oracle, then doing an INSERT INTO to get it to play nice with the trigger on the destination table.

Felt y'all deserved points for teaching me something though.
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

564 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