?
Solved

reading Excel file using PL/SQL

Posted on 2006-07-12
6
Medium Priority
?
19,747 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 500 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 500 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

762 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