Home » RDBMS Server » Server Utilities » Loading data via external table -when csv file doesn't exactly map to table (Oracle 10.2.0.3 on Solaris 8)
Loading data via external table -when csv file doesn't exactly map to table [message #430913] Fri, 13 November 2009 02:00 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi,

I have a requirement to load text files to oracle. The text file has filed that are separated by '|' (pipe symbol). The issue is that the table has two extra columns compared to the file from which it is to be loaded. can you suggest how it can be done? - here is a example of my scenario:

the table is empdata having following structure:
SQL> DESC EMPDATA 
Name Null? Type
--------------------------------------------------------------------------------
 --------
--------------------------------------------------------------------------------
 
EMPID VARCHAR2(100) 
ENAME VARCHAR2(100) 
SAL VARCHAR2(100) 
DEPTID VARCHAR2(100) 
DNAME VARCHAR2(100) 

Now the text file which data is to be loaded has this structure:

deptid=10|dname=sales
101|david|1200
102|nash|1000
103|pat|1500

Here the last two column values for each of the rows has to come from first line in the file. The first line of text file needs to be parsed and from that, value of deptid (which is 10) should be picked up. Same way dname should be parsed and picked up as 'sales' and these two values should be inserted inteh last two columns of each of the rows. Can you suggest a way how I can use the external table concept and load the data in the table?

Thanks
Re: Loading data via external table -when csv file doesn't exactly map to table [message #430918 is a reply to message #430913] Fri, 13 November 2009 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First remove "deptid=" and "dname=" strings from the file.
Then you can use a single SQL statement, something like (emp_ext is the external table):
insert into empdata 
select e1.*, e2.empid, e2.ename 
from (select * from (select e.*, rownum rn from emp_ext e) where rn>1) e1,
     (select * from (select e.*, rownum rn from emp_ext e) where rn=1) e2
/

Or, maybe better, use a PL/SQL block, something like:
declare
  deptid integer;
  dname  varchar2(30);
begin
  select empid, ename into deptid, dname from emp_ext where rownum=1;
  insert into emp
    select e.*, deptid, dname
    from (select e.*, rownum rn from emp_ext)
    where rn > 1;
end;
/

Regards
Michel


Re: Loading data via external table -when csv file doesn't exactly map to table [message #430949 is a reply to message #430918] Fri, 13 November 2009 06:34 Go to previous message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thank you VERY MUCH Michel!! This is a very big help to me...I will try this approach.

Thanks
Nirav
Previous Topic: dataloading with sql*loader without archie
Next Topic: ORA-01555: snapshot too old during export.
Goto Forum:
  


Current Time: Wed Dec 07 04:38:14 CST 2016

Total time taken to generate the page: 0.10239 seconds