How to import csv Data in oracle

[Fall_2]

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

[Zafar A. Mohammed_1]

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

Thanks
Zafar

[Fall_2]

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

[Zafar A. Mohammed_1]

Check this complete chapter link

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch03.htm#2436

Thanks
Zafar

[Yogeeraj_1 · Accepted solution]

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

[Fall_2]

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

[Yogeeraj_1]

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

[Fall_2]

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