Home » Developer & Programmer » Forms » Reading Excel data and inserting into table using forms6i
| Reading Excel data and inserting into table using forms6i [message #84218] |
Mon, 16 February 2004 20:10  |
chs
Messages: 31
Registered: December 2001
|
Member |
|
|
Hi friends,
I need to know the answer for how to read the excel data and insert into tables without using SQL loader. i tried using OLE2 package,but i am getting an non-oracle exception. even i tried using CSV format. but i couldn't make it.
anyone pls help me with proper code and any link that will answer my question.
thanx in advance
|
|
|
|
| Re: Reading Excel data and inserting into table using forms6i [message #84230 is a reply to message #84218] |
Wed, 18 February 2004 03:49  |
Victoria
Messages: 152
Registered: July 2002
|
Senior Member |
|
|
Hi,
Following code might solve your problem...
PROCEDURE PROC_READ_EXCEL
(
p_file_name in varchar2,
p_sheet_name in varchar2,
p_range in varchar2,
p_from_row in number,
p_from_col in number,
p_to_row in number,
p_to_col in number,
p_value out varchar
)
IS
-- Declare handles to OLE objects
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheet ole2.obj_type;
cell ole2.obj_type;
cell1 ole2.obj_type;
-- Declare handles to OLE argument lists
args ole2.list_type;
Check_file text_io.file_type;
no_file exception;
PRAGMA exception_INIT (no_file, -302000);
cell_value varchar2(2000);
sor_start_row number;
sor_start_col number;
l_sor_start_row number; -- l_sor_start_row number; 12
l_eor_start_row number; -- l_eor_start_row number; 236
l_sor_start_col number; -- l_sor_start_col number; 3
l_eor_end_col number; -- l_eor_end_col number; 14
temp number := 0;
l_col_order varchar2(3000) := ' ';
l_ddl varchar2(3000) := ' ';
b_success_flg boolean;
BEGIN
-- Check the file can be found, if not exception no_file will be raised
Check_file := TEXT_IO.FOPEN(p_file_name,'R');
TEXT_IO.FCLOSE(Check_file);
application:= ole2.create_obj('Excel.Application');
workbooks := ole2.get_obj_property(application, 'Workbooks');
-- Open the required workbook
args:= ole2.create_arglist;
ole2.add_arg(args,p_file_name); --ole2.add_arg(args, 'C:test.XLS');
workbook := ole2.invoke_obj(workbooks, 'Open', args);
ole2.destroy_arglist(args);
-- Open worksheet Sheet1 of that Workbook
args:= ole2.create_arglist;
ole2.add_arg(args,p_sheet_name); --ole2.add_arg(args, 'Domestic-Canada-Mexico Layout');
worksheet := ole2.get_obj_property(workbook, 'Worksheets', args);
ole2.destroy_arglist(args);
-- Get value of cell A1 of worksheet Domestic Layout
args:= ole2.create_arglist;
IF p_range <> 'Y' THEN /*when exact cell specifications passed*/
ole2.add_arg(args,p_from_row);
ole2.add_arg(args,p_from_col);
cell:= ole2.get_obj_property(worksheet, 'Cells', args);
ole2.destroy_arglist(args);
args:= ole2.create_arglist;
-- Added by Pal - To toggle between the char and num property cells w.r.t International sht.
if p_from_col = 3 or p_from_col = 7 then
cell_value :=ole2.get_char_property(cell,'Value');
else
cell_value :=ole2.get_num_property(cell,'Value');
end if;
p_value := (cell_value);
message(p_value);
ELSE
/* To find X-Y Co-ordinates of SOR */
for i in 1..15
loop
for j in 1..5
loop
ole2.add_arg(args, i);
ole2.add_arg(args, j);
cell := ole2.get_obj_property(worksheet, 'Cells', args);
ole2.destroy_arglist(args);
args := ole2.create_arglist;
cell_value := ole2.get_char_property(cell,'Value');
if rtrim(ltrim(upper(cell_value))) = 'SOR' then
sor_start_row := i;
sor_start_col := j;
l_sor_start_row := sor_start_row + 1;
l_sor_start_col := sor_start_col;
while temp = 0 loop
ole2.add_arg(args, sor_start_row);
ole2.add_arg(args, sor_start_col);
cell := ole2.get_obj_property(worksheet, 'Cells', args);
ole2.destroy_arglist(args);
args := ole2.create_arglist;
cell_value := ole2.get_char_property(cell,'Value');
if cell_value is NULL then
temp := -1;
l_eor_end_col:= sor_start_col - 1;
end if;
sor_start_col := sor_start_col + 1;
end loop;
exit;
end if;
end loop;
end loop;
sor_start_col:= sor_start_col - 2;
temp:= 0;
/*To find X-Y Co-ordinates of EOR*/
while temp = 0 loop
ole2.add_arg(args, sor_start_row);
ole2.add_arg(args, sor_start_col);
cell:= ole2.get_obj_property(worksheet, 'Cells', args);
ole2.destroy_arglist(args);
args:= ole2.create_arglist;
cell_value :=ole2.get_char_property(cell,'Value');
if rtrim(ltrim(upper(cell_value))) = 'EOR' then
temp := -1;
l_eor_start_row:= sor_start_row - 1;
end if;
sor_start_row := sor_start_row + 1;
end loop;
message('From row'||l_sor_start_row||'To Row'||l_eor_start_row ||'From Col'||l_sor_start_col||'To Col'||l_eor_end_col);
for j in l_sor_start_col..l_eor_end_col
loop
ole2.add_arg(args, l_sor_start_row - 2);
ole2.add_arg(args, j);
cell:= ole2.get_obj_property(worksheet, 'Cells', args);
ole2.destroy_arglist(args);
args:= ole2.create_arglist;
cell_value := ole2.get_char_property(cell,'Value');
cell_value := replace(replace(replace(ltrim(rtrim(cell_value)),'''',''),' ',''),'-','');
l_col_order := ltrim(l_col_order||replace(replace(cell_value,' ','_'),'__','_')||',');
end loop;
l_ddl := replace(substr(l_col_order,1,length(l_col_order)),',',' varchar2(50),');
l_ddl := substr(l_ddl,1,length(l_ddl)-1);
message(substr(l_ddl,1,20));
message(substr(l_ddl,21,20));
message(substr(l_ddl,41,30));
message(substr(l_ddl,71,30));
message(substr(l_ddl,101,30));
message(substr(l_ddl,131,30));
message(substr(l_ddl,161,30));
message(substr(l_ddl,191,30));
message(substr(l_ddl,221,30));
message(substr(l_ddl,251));
--proc_create_intl(l_ddl,b_success_flg);
IF b_success_flg THEN
message('TRUE');
else
message('False');
END IF;
/*for i in p_from_row..p_to_row
loop
for j in p_from_col..p_to_col
loop
ole2.add_arg(args, i);
ole2.add_arg(args, j);
cell:= ole2.get_obj_property(worksheet, 'Cells', args);
ole2.destroy_arglist(args);
args:= ole2.create_arglist;
cell_value :=ole2.get_char_property(cell,'Value');
message(cell_value);
end loop;
end loop;
*/
END IF;
args:=ole2.create_arglist;
ole2.add_arg(args,'C:Ora.prn');
ole2.add_arg(args,'Formatted text(Space delimited)(*.prn)|*.prn|');
ole2.invoke(workbook,'Save As',args);
ole2.destroy_arglist(args);
ole2.invoke(application,'Quit');
-- Release the OLE2 object handles
ole2.release_obj(cell);
ole2.release_obj(worksheet);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
exception
WHEN no_file THEN
MESSAGE('file not found.');
/*WHEN OTHERS THEN
MESSAGE(sqlerrm);
PAUSE;
FOR i IN 1 .. tool_err.nerrors LOOP
MESSAGE(tool_err.message);
PAUSE;
tool_err.pop;
END LOOP;
*/
END;
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 05 21:48:09 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
