Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to import csv Data in oracle

SOLVED
Go to solution
Fall_2
Occasional Advisor

How to import csv Data in oracle

Hi,

How can I import in oracle table csv Data ?
using DBMS_SQL. Yogeeraj had an expample on how to export that I transformed to my need. Maybe someone have an example to show me

Thank in advance
ib
7 REPLIES
Zafar A. Mohammed_1
Trusted Contributor

Re: How to import csv Data in oracle

You can do by SQL*Loader. I'll send you the example or link.

Thanks
Zafar
Fall_2
Occasional Advisor

Re: How to import csv Data in oracle

Thank safar I will take a look at it. I did a package in pl/sql that export table in csv file and I want to do the same in my package for import.
Ibra
ib
Zafar A. Mohammed_1
Trusted Contributor

Re: How to import csv Data in oracle

Yogeeraj_1
Honored Contributor
Solution

Re: How to import csv Data in oracle

Hi,

If you wish to do this in PLSQL, it is easy enough. Here is a sample routine that will load a delimited text file into a table. You must configure UTL_FILE for this to work correctly.


drop table badlog;
create table badlog( errm varchar2(4000), data varchar2(4000) );

create or replace
function load_data( p_table in varchar2,
p_cnames in varchar2,
p_dir in varchar2,
p_filename in varchar2,
p_delimiter in varchar2 default '|' )
return number
is
l_input utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_buffer varchar2(4000);
l_lastLine varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_cnt number default 0;
l_sep char(1) default NULL;
l_errmsg varchar2(4000);
begin
l_input := utl_file.fopen( p_dir, p_filename, 'r' );

l_buffer := 'insert into ' || p_table || ' values ( ';
l_colCnt := length(p_cnames)-
length(replace(p_cnames,',',''))+1;

for i in 1 .. l_colCnt
loop
l_buffer := l_buffer || l_sep || ':b'||i;
l_sep := ',';
end loop;
l_buffer := l_buffer || ')';

dbms_sql.parse( l_theCursor, l_buffer, dbms_sql.native );


loop
begin
utl_file.get_line( l_input, l_lastLine );
exception
when NO_DATA_FOUND then
exit;
end;
l_buffer := l_lastLine || p_delimiter;


for i in 1 .. l_colCnt
loop
dbms_sql.bind_variable( l_theCursor, ':b'||i,
substr( l_buffer, 1,
instr(l_buffer,p_delimiter)-1 ) ) ;
l_buffer := substr( l_buffer,
instr(l_buffer,p_delimiter)+1 );
end loop;

begin
l_status := dbms_sql.execute(l_theCursor);
l_cnt := l_cnt + 1;
exception
when others then
l_errmsg := sqlerrm;
insert into badlog ( errm, data )
values ( l_errmsg, l_lastLine );
end;
end loop;

dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_input );
commit;

return l_cnt;
end load_data;
/

And here is an example using the above:


yd@mydb.cmt.mu> create table t1 ( x int, y int, z int );
Table created.

yd@mydb.cmt.mu> host echo 1,2,3 > /tmp/t1.dat
yd@mydb.cmt.mu> host echo 4,5,6 >> /tmp/t1.dat
yd@mydb.cmt.mu> host echo 7,8,9 >> /tmp/t1.dat
yd@mydb.cmt.mu> host echo 7,NotANumber,9 >> /tmp/t1.dat

yd@mydb.cmt.mu> begin
2 dbms_output.put_line(
3 load_data( 'T1',
4 'x,y,z',
5 '/tmp',
6 't1.dat',
7 ',' ) || ' rows loaded' );
8 end;
9 /
3 rows loaded

PL/SQL procedure successfully completed.

yd@mydb.cmt.mu> select * from badlog;

ERRM DATA
------------------- -------------------
ORA-01722: invalid number 7,NotANumber,9


yd@mydb.cmt.mu> select * from t1;

X Y Z
---------- ---------- ----------
1 2 3
4 5 6
7 8 9


====================================================
A better solution exists in 9i - External tables. Let us know if you want this solution too.

hope this helps
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Fall_2
Occasional Advisor

Re: How to import csv Data in oracle

Hi,
Yogeeraj thanks I think this will work for me you can also send me the solution from 9i so I can take a look.
Regards,
Ibra
ib
Yogeeraj_1
Honored Contributor

Re: How to import csv Data in oracle

hi ibra,

sorry for this late reply.

This example also shows how to analyze and separate from a input csv file into 3 tables.

yd@ORA920> create or replace directory data_dir as '/tmp'
2 /
Directory created.


yd@ORA920> create table csv_table
2 (c1 varchar2(80),
3 c2 varchar2(80),
4 c3 varchar2(80),
5 c4 varchar2(80),
6 c5 varchar2(80),
7 c6 varchar2(80),
8 c7 varchar2(80),
9 c8 varchar2(80),
10 c9 varchar2(80),
11 c10 varchar2(80)
12 )
13 ORGANIZATION EXTERNAL
14 ( type oracle_loader
15 default directory data_dir
16 access parameters
17 ( fields terminated by ','
18 optionally enclosed by "'"
19 missing field values are null
20 )
21 location ('YD.dat')
22 )
23 /
Table created.

yd@ORA920> create table header( col1 number, col2 date, data varchar2(20));
Table created.

yd@ORA920> create table master( master_id number, creation_date date, data varchar2(20) );
Table created.

yd@ORA920> create table detail( master_id number, detail_id number, amount number, data varchar2(20) );
Table created.

yd@ORA920> !echo header,53,22/04/2003,Hello World > /tmp/YD.dat
yd@ORA920> !echo master,1,01-sep-2002 01:02:03,some info >> /tmp/YD.dat
yd@ORA920> !echo detail,1,1,52321,yddata yddata yddata >> /tmp/YD.dat
yd@ORA920> !echo detail,1,2,233,data data data >> /tmp/YD.dat
yd@ORA920> !echo master,2,11-mar-2001 11:02:03,some info >> /tmp/YD.dat
yd@ORA920> !echo detail,2,1,52324,yddata yddata yddata >> /tmp/YD.dat
yd@ORA920> !echo detail,2,2,25323432,yddata yddata yddata >> /tmp/YD.dat
yd@ORA920> insert /*+ APPEND */ first
2 when (c1 = 'header' ) then
3 into header (col1,col2,data)

4 values ( to_number(c2), to_date(c3,'dd/mm/yyyy'), c4 )
5 when (c1 = 'master' ) then
6 into master (master_id,creation_date,data)
7 values ( to_number(c2), to_date(c3,'dd-mon-yyyy hh24:mi:ss'), c4 )
8 when (c1 = 'detail' ) then
9 into detail (master_id,detail_id,amount,data)
10 values (to_number(c2),to_number(c3),to_number(c4),c5 )
11 select * from csv_table;

7 rows created.

yd@ORA920> commit;

Commit complete.

yd@ORA920>
yd@ORA920> select * from header;

COL1 COL2 DATA
---------- --------- --------------------
53 22-APR-03 Hello World

yd@ORA920> select * from master;

MASTER_ID CREATION_ DATA
---------- --------- --------------------
1 01-SEP-02 some info
2 11-MAR-01 some info

yd@ORA920> select * from detail;

MASTER_ID DETAIL_ID AMOUNT DATA
---------- ---------- ---------- --------------------
1 1 52321 yddata yddata yddata
1 2 233 yddata yddata yddata
2 1 52324 yddata yddata yddata
2 2 25323432 yddata yddata yddata

And that is a direct path load of the single input file into the three tables.


hope this helps!

best regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Fall_2
Occasional Advisor

Re: How to import csv Data in oracle

Thanks Yogeeraj for the examples. Can I sent you a copy by mail of what I did for your input please.
Reagards,
Ibra
ib