import tab delimited file

[umeman]

DBA's

I have the following problem:

I need to import a multi-record type tab delimited file
into a set of Oracle tables based on each record type.

If anyone has done something similar or has any idea how
do import this type of file I will appreciate the help.

The tab delimited file looks like :

101-1TABjoeTABsmith
101-2TABNew Jersey
102-1TABPaulTABJones
102-2TABNew York

101 is the employee number -1 and -2 are the record type indicators.

Record type 1 records are loaded into table

emp_names
(emp_num varchar(10),
fname varchar(20),
lname varchar(20))

Record type 2 records are loaded into table

emp_state
(emp_num varchar(10),
state varchar(20),
)


Thanks
Umesh

 

[lfoata]

For this type of thing, we usually user sqlldr (SQL Loader). Look at the doc for the exact syntax of the parameter file. Too bad I.T. is not cash business

Luc Foata
Unix sysadmin, Oracle DBA

 

[umeman]

Correct me if I am wrong, but I don't think SQLLDR can
handle a file with multiple record types

Thanks
Umesh

 

[karluk]

I think SQL*Loader can handle your situation. Your load statement will have two "into table" clauses, each of which will use a "when" clause to select the records that should be loaded into the given table. Please check the Oracle utilities manual for details.

 

[bookouri]

I usually just use msaccess for things like this. I can set up an odbc connection to my oracle database and link the oracle tables i need to use. Once the tables are available in your Access database you can do anything with them. With Access its usually just plain easy to accomplish more with less effort than trying to use the Oracle tools...