Solved

reading Excel file using PL/SQL

Posted on 2006-07-12
6
18,280 Views
1 Endorsement
Last Modified: 2013-12-12
I need to read excel file using PL/SQL, please provide step by step procedure.
1
Comment
Question by:shan_sai
6 Comments
 
LVL 14

Accepted Solution

by:
sathyagiri earned 125 total points
ID: 17096375
You can use UTL_FILE to do this.

here's a sample
create or replace procedure proc1
is
file_handle           utl_file.file_type;
var1 table1.col1%type;
var2 table1.col2%type;
v_string varchar2(1000);
file_handle := UTL_FILE.FOPEN('/directory','fname.csv','R',5000);
begin
loop
begin
UTL_FILE.get_line(file_handle,v_string);
var1 := susbstr(v_string,1,instr(v_string,',',1,1)-1);
var2 := substr(v_string, instr(v_string,',',1,1)+1,  instr(v_string,',',1,2) - instr(v_string,',',1,1) -1);
insert into table1 (col1,col2) values (var1,var2);
exception
  when no_data_found then
      UTL_FILE.fclose (file_handle);
      exit;
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(sqlcode||sqlerrm);
end;
end loop;
commit;
end;
0
 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 125 total points
ID: 17098147
User Oracle Generic connectivity to connect excel using ODBC. See the link below

Worked example for using Excel through ODBC
http://asktom.oracle.com/pls/ask/f?p=4950:8:3250259327871965997::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4406709207206#18830681837358

0
 

Expert Comment

by:darkomk
ID: 17461877
A good way to read/write/control Miscrosoft office apps is through the OLE system.
You can create a PLL that can be attached to any form that you need to work with Excel.

The PLL should consist of a pl/sql package:

The package spec:

PACKAGE excel IS
 -- global variables (constants)
  application      ole2.obj_type;
  workbooks       ole2.obj_type;
  workbook            ole2.obj_type;
  worksheets       ole2.obj_type;
  worksheet      ole2.obj_type;

  procedure open_excel;
  procedure close_excel;
  procedure read_num_cell( p_row number, p_col number, p_value in out number );
  procedure read_chr_cell( p_row number, p_col number, p_value in out varchar2 );
  procedure open_workbook;
  procedure close_workbook;
  procedure open_excel_doc( p_pateka varchar2 );
  procedure close_excel_doc;
  procedure activate_sheet( p_redosled integer );
end;

PACKAGE BODY excel IS

  procedure open_excel is
  begin
            application := ole2.create_obj('Excel.Application');
  end;
  procedure close_excel is
  begin
        if worksheet is not null then
                  ole2.release_obj(worksheet);
        end if;
        if worksheets is not null then
                              ole2.release_obj(worksheets);
        end if;
        if workbook is not null then
                  ole2.release_obj(workbook);
        end if;
        if workbooks is not null then
                  ole2.release_obj(workbooks);
        end if;
        if application is not null then
                  ole2.release_obj(application);
      end if;
  end;
  procedure read_num_cell( p_row number, p_col number, p_value in out number ) is
            args       ole2.list_type;
            cell      ole2.obj_type;
      begin
            args := ole2.create_arglist;
            ole2.add_arg(args, p_row );
            ole2.add_arg(args, p_cel );
            cell := ole2.get_obj_property(worksheet,'Cells',args);
            ole2.destroy_arglist(args);
            p_value := ole2.get_num_property(cell,'Value');
            ole2.release_obj(cell);
      end;
  procedure read_chr_cell( p_row number, p_col number, p_value in out varchar2 ) is
            args       ole2.list_type;
            cell      ole2.obj_type;
      begin
            args := ole2.create_arglist;
            ole2.add_arg(args, p_row );
            ole2.add_arg(args, p_col );
            cell := ole2.get_obj_property(worksheet,'Cells',args);
            ole2.destroy_arglist(args);
            p_value := ole2.get_char_property(cell,'Value');
            ole2.release_obj(cell);
      end;

  procedure open_workbook is
  begin
            workbooks := ole2.get_obj_property(application,'Workbooks');
      end;      

  procedure close_workbook is
  begin
            ole2.invoke(workbooks,'Close');
      end;      
  procedure open_excel_doc( p_pateka varchar2 ) is
            args ole2.list_type;
  begin
            args := ole2.create_arglist;
            ole2.add_arg(args, p_pateka );
            workbook := ole2.invoke_obj(workbooks, 'Open', args);
            ole2.destroy_arglist(args);
            worksheets := ole2.get_obj_property (workbook,'Worksheets');
  end;
  procedure close_excel_doc is
  begin
            ole2.invoke(workbook, 'Close');
      end;      
  procedure activate_sheet( p_redosled integer ) is
            args ole2.list_type;
      begin
            args := ole2.create_arglist;
            ole2.add_arg(args, p_redosled );
            worksheet := ole2.get_obj_property(worksheets,'Item', args);
            ole2.destroy_arglist(args);
            ole2.invoke( worksheet, 'Activate');
      end;
end;


THEN attach the PLL to the form and IN THE MAIN PROCEDURE in the form:

declare
      p_value number := 4;
begin
      EXCEL.open_excel;
      EXCEL.open_workbook;
      EXCEL.open_excel_doc('c:\a.xls');
      EXCEL.activate_sheet(1);  -- 1 is the first worksheet from left
      EXCEL.READ_NUM_CELL(1, 1, p_value);
-- do what is needed with the p_value
      EXCEL.close_excel_doc;
      EXCEL.close_workbook;
      EXCEL.close_excel;
end;
0
 

Expert Comment

by:klm11204
ID: 22556865
Hello,
I need a pure pl/sal code that can read from xls sheet as table i want to update 5 oracle tables from
this excel sheet
question do you to load this xls sheet to a templ  table and do my business logic on it?
or can i deal with excel sheet as an oracle table ie reading from it using sql statment?
is this doable?
please help how i can do that?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now