Split flat file data and load in multiple tables 1

[killerguy2003]

I am a newbie to the oracle scene. I am trying to split a flat file and load into multiple tables. I know this can be done using a cursor and read each row and split the data based on the first column and load into different tables. But I am not sure where to start. This is what I am trying to accomplish, if the row starts with UserArray(1st column) then it should be inserted into USER Table and if the row starts with ContactArray(1st column) then it should be inserted into CONTACT table. I would really appreciate if anybody can shed some light on this.

Here is how my data looks seperated by a single pipe '|'

UserArray1|TESTUSER|OWNER|TEST@TEST.com|7195355353|123|TEST|T|Miachel|7195355353|
ContactArray1|DAR|test@hotmail.com|91565656577788787|111|doe||John|91565656577788787|
UserArray2|TESTUSER|OWNER|TEST@TEST.com|7195355353|123|TEST|T|Miachel|7195355353|
UserArray3|TESTUSER|OWNER|TEST@TEST.com|7195355353|123|TEST|T|Miachel|7195355353|
ContactArray2|CompanyName|test@hotmail.com|0001987123456|789|doe||John|0001987123456|
ContactArray3|DAR|test@hotmail.com|91565656577788787|111|doe||John|91565656577788787|

Thanks!
KG

 

[LKBrwnDBA]

Check out the SQL*Loader manual, specialy the section dealing with the WHEN clause.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Killer,

There is a neat feature that you can use that allows you to define your flat file as an Oracle table, and if the data in the flat file changes, you do not need to perform some sort of re-load of the data. That feature is called Oracle's externally organized tables.

In my example, below, to give you the greatest flexibility, I have defined your flat file as an externally organized table named, KG_SOURCE. From that table, I have created two views, KG_USERS and KG_CONTACTS:

create directory TTFlatFiles as 'd:\dhunt\sqldba'
/
drop table kg_source;
REM ========================================================
REM Creation of Source table from a flat file
REM ========================================================
create table kg_source
	(Rec_Type	varchar2(20)
	,col1		varchar2(20)
	,col2		varchar2(20)
	,col3		varchar2(20)
	,col4		varchar2(20)
	,col5		varchar2(20)
	,col6		varchar2(20)
	,col7		varchar2(20)
	,col8		varchar2(20)
	,col9		varchar2(20)
	)
organization external
(  type oracle_loader
   default directory TTFlatFiles
   access parameters
   (records delimited by newline
    fields terminated by '|'
    missing field values are null
   )  
location ('KG.txt')
)
reject limit unlimited;

Table created.

create or replace view kg_users as
select * from kg_source where rec_type like 'User%';

View created.

create or replace view kg_contacts as
select rec_type,col1,col2,col3,col4,col5,col6,col7,col8
  from kg_source where rec_type like 'Contact%';

View created.

set linesize 200
col rec_type format a13
col col1 format a8
col col2 format a5
col col3 format a14
col col4 format a10
col col5 format a4
col col6 format a4
col col7 format a4
col col8 format a7
col col9 format a10
select * from kg_users;

REC_TYPE      COL1     COL2  COL3           COL4       COL5 COL6 COL7 COL8    COL9
------------- -------- ----- -------------- ---------- ---- ---- ---- ------- ----------
UserArray1    TESTUSER OWNER TEST@TEST.com  7195355353 123  TEST T    Miachel 7195355353
UserArray2    TESTUSER OWNER TEST@TEST.com  7195355353 123  TEST T    Miachel 7195355353
UserArray3    TESTUSER OWNER TEST@TEST.com  7195355353 123  TEST T    Miachel 7195355353

col rec_type format a13
col col1 format a11
col col2 format a16
col col3 format a17
col col4 format a4
col col5 format a4
col col6 format a4
col col7 format a4
col col8 format a17
col col9 format a10
select * from kg_contacts;

REC_TYPE      COL1        COL2             COL3              COL4 COL5 COL6 COL7 COL8
------------- ----------- ---------------- ----------------- ---- ---- ---- ---- -----------------
ContactArray1 DAR         test@hotmail.com 91565656577788787 111  doe       John 91565656577788787
ContactArray2 CompanyName test@hotmail.com 0001987123456     789  doe       John 0001987123456
ContactArray3 DAR         test@hotmail.com 91565656577788787 111  doe       John 91565656577788787
==================================================================================================

Since you didn't post the names of the columns, I just used stub names like "COL_x". The only things that you really need to modify in my code, above, to test it out, are the names of the directory where you wish to store the flat file and the name of the flat file.

Let us know if this gives you the tools to resolve your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[killerguy2003]

SantaMufasa,

I really appreciate your input on this one. I think this is going to be useful for me. But we use Unix Shell Scripts to load our tables and now I have to figure out how this piece is going to fit into the puzzle.

Thanks for your help!!
KG

 

[SantaMufasa]

Killer,

The neat thing about using External Tables is that you do not need to run even Unix Shell scripts...
All you need is the flat file existing in a Unix file system and the data automatically is "appears" in the table.
Specifically, you don't need to LOAD the data into a table via any script or explicit action...the data is
"in" the table by virtue of your running only once the "CREATE TABLE...ORGANIZATION EXTERNAL..." statement.
Even if the data in the flat file changes, all of the changes immediately appear in the table without
your needing to do ANYTHING ! Cool, huh.

So, what I'm trying to say is that not only should there be no changes in your Unix shell scripts...
You probably don't even need the Unix shell scripts any longer to access data from your
flat files, if you use External Organization tables.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[killerguy2003]

SantaMufasa,

Thanks a lot for your suggestions. It really worked.

Thanks!
KG