reading Excel file using PL/SQL

I need to read excel file using PL/SQL, please provide step by step procedure.
shan_saiAsked:
Who is Participating?
 
sathyagiriConnect With a Mentor Commented:
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
 
MohanKNairConnect With a Mentor Commented:
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
 
darkomkCommented:
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
 
klm11204Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.