| Loading data via external table -when csv file doesn't exactly map to table [message #430913] |
Fri, 13 November 2009 02:00  |
orausern
Messages: 826
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  |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
