DalTXColtsFan
asked on
Easiest ways to import a gig and a half of pipe-delimited text into an Oracle 9i table
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Make sure you don't use direct path inport as it woll not fire the trigger.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is SQL*Loader a third-party utility, or is it possible that I have it and just can't find it?
ASKER
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.
you should have it on your client machine as well. just check it
c:> sqlldr
c:> sqlldr
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.
SQL*Loader is great, but since you can't use direct path for your load, I'd use the EXTERNAL table method instead.
Through multiple instances of SQL*Loader for different files............paralleli
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
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
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@
Sujit
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@
Sujit
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
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
ASKER
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.
Felt y'all deserved points for teaching me something though.
ASKER