?
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
?
783 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +4
15 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

765 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