Solved

reading Excel file using PL/SQL

Posted on 2006-07-12
6
19,377 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

696 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