4 Replies Latest reply: Dec 15, 2007 4:40 AM by 610973 RSS

    IMPORT DATA FROM EXCEL SHEET TO ORACLE FORM

    Dollongo
      Dear all,

      I have developed my application using forms 6i. In one block i want to be importing data FROM excel sheet file. The block has fields Item_No, Item_Name, Group_Name, Unit_Measurement, Qty

      Suppose i have an excel sheet with the above number of columns and data of the same format as specified in the block fields, how can i import INTO ORACLE FORM FROM EXCEL SHEET. How do i go about it? Any scripts to import from flat file to oracle form?

      Thanks in advance
        • 1. Re: IMPORT DATA FROM EXCEL SHEET TO ORACLE FORM
          Grant Ronald-Oracle
          Couple of options - you could save the file out to a comma delimited file and read using TEXT_IO.
          Or using the OLE package you could read the data from Excel directly.

          Another solution would be using something like BPEL with one of the file adaptors that allows reading in of data but if you are using 6i my guess is you don't have BPEL.
          Regards
          Grant
          • 2. Re: IMPORT DATA FROM EXCEL SHEET TO ORACLE FORM
            613383
            Look at this Package About Read and Write to EXCEL using OLE ..



            PACKAGE EXCEL IS
            application OLE2.OBJ_TYPE;
            workbooks OLE2.OBJ_TYPE;
            workbook OLE2.OBJ_TYPE;
            worksheets OLE2.OBJ_TYPE;
            worksheet OLE2.OBJ_TYPE;
            cell OLE2.OBJ_TYPE;
            procedure init ;
            procedure open(dosya_adi varchar2) ;
            procedure show ;
            procedure add(prow in number,pcol in out number, pvalue in varchar2);
            function get(prow in number,pcol in out number) RETURN varchar2;
            procedure destroy ;
            END;


            PACKAGE BODY EXCEL IS
            args OLE2.LIST_TYPE;
            /********************************************************/

            procedure show is
            begin
            OLE2.SET_PROPERTY(application, 'Visible', 'True');
            end;

            /********************************************************/

            procedure init is
            begin
            application:=OLE2.CREATE_OBJ('Excel.Application');
            -- OLE2.SET_PROPERTY(application, 'Visible', 'True');
            -- Return object handle to the Workbooks collection
            workbooks:=ole2.get_obj_property(application, 'Workbooks');

            -- Add a new Workbook object to the Workbooks collection
            workbook:=ole2.get_obj_property(workbooks,'Add');

            -- Return object handle to the Worksheets collection for the
            -- Workbook
            worksheets:=ole2.get_obj_property(workbook, 'Worksheets');

            -- Add a new Worksheet to the Worksheets collection
            worksheet:=ole2.get_obj_property(worksheets,'Add');
            end;

            /********************************************************/

            procedure open(dosya_adi varchar2) is
            begin
            application:=OLE2.CREATE_OBJ('Excel.Application');
            -- OLE2.SET_PROPERTY(application, 'Visible', 'True');
            -- Return object handle to the Workbooks collection
            workbooks:=ole2.get_obj_property(application, 'Workbooks');

            -- Open a Workbook object to the Workbooks collection
            args:=OLE2.CREATE_ARGLIST;
            OLE2.ADD_ARG(args, dosya_adi);

            workbook := OLE2.GET_OBJ_PROPERTY(workbooks, 'Open', args);
            OLE2.DESTROY_ARGLIST(args);


            -- Return object handle to the Worksheets collection for the
            -- Workbook
            worksheets:=ole2.get_obj_property(workbook, 'Worksheets');

            -- Open a Worksheet to the Worksheets collection
            args:=OLE2.CREATE_ARGLIST;
            OLE2.ADD_ARG(args, 1); -- Birinci sheet icin args 1 olmali.
            worksheet := OLE2.GET_OBJ_PROPERTY(worksheets,'item',args);
            OLE2.DESTROY_ARGLIST(args);

            end;

            /********************************************************/

            procedure add(prow in number,pcol in out number, pvalue in varchar2) is
            Begin
            if pvalue is not null then
            -- Return object handle to cell A1 on the new Worksheet
            args:=OLE2.CREATE_ARGLIST;
            OLE2.ADD_ARG(args, prow);
            OLE2.ADD_ARG(args, pcol);
            cell:=ole2.get_obj_property(worksheet, 'Cells', args);
            OLE2.DESTROY_ARGLIST(args);
            OLE2.SET_PROPERTY(cell, 'Value', pvalue);
            --str := OLE2.Get_Char_Property(obj, 'text');

            OLE2.RELEASE_OBJ(cell);
            end if;
            pcol := pcol + 1;
            end;

            /********************************************************/

            function get(prow in number,pcol in out number) RETURN varchar2 is
            deger varchar2(100);
            Begin

            -- Return object handle to cell A1 on the new Worksheet
            args:=OLE2.CREATE_ARGLIST;
            OLE2.ADD_ARG(args, prow);
            OLE2.ADD_ARG(args, pcol);
            cell:=ole2.get_obj_property(worksheet, 'Cells', args);
            OLE2.DESTROY_ARGLIST(args);

            deger := OLE2.Get_Char_Property(cell, 'Text');
            --deger := OLE2.Get_Num_Property(cell, 'Value');

            OLE2.RELEASE_OBJ(cell);

            pcol := pcol + 1;

            return(deger);

            end;

            /********************************************************/

            procedure destroy is
            Begin

            --args:=OLE2.CREATE_ARGLIST;
            --OLE2.ADD_ARG(args, 'C:\OLETEST2.XLS');
            --OLE2.INVOKE(worksheet, 'SaveAs', args);
            OLE2.INVOKE(application,'quit');
            --OLE2.DESTROY_ARGLIST(args);

            -- Release the OLE objects
            OLE2.RELEASE_OBJ(worksheet);
            OLE2.RELEASE_OBJ(worksheets);
            OLE2.RELEASE_OBJ(workbook);
            OLE2.RELEASE_OBJ(workbooks);
            OLE2.RELEASE_OBJ(application);
            end;

            /********************************************************/

            procedure eset(x in number,y in out number,
                 palan in varchar2,
                 pdeger in varchar2) IS
            xdeger varchar2(200);
            BEGIN
                 xdeger := pdeger;
            ADD(x,y,xdeger);
            END;

            /********************************************************/

            END;
            • 3. Re: IMPORT DATA FROM EXCEL SHEET TO ORACLE FORM
              KevinDClarke
              If you have the choice of XLS or CSV format, use CSV then you can simply use text_io as Grant says, or do a sqlload from a host command.

              If you have a spreadsheet with multiple sheets which must be in XLS format you can use OLE to loop through the cells as in the example above.

              Another option is to use OLE to convert each worksheet to a seperate CSV and then use text_io or sqlload to load them. I have found that this method results in simpler code and performs better if there is a lot of data.
              • 4. Re: IMPORT DATA FROM EXCEL SHEET TO ORACLE FORM
                610973
                Hey,
                I am calling MS word as a editor for text item by using client_OLE2.
                I got luck to open MS word when I press ctrl+e on that text item.
                but I am unable to carry same text from word to text item.
                how can I do it?
                Please me any code for it.

                this is my code

                DECLARE
                          app CLIENT_OLE2.OBJ_TYPE;
                          docs CLIENT_OLE2.OBJ_TYPE;
                          doc CLIENT_OLE2.OBJ_TYPE;
                          selection CLIENT_OLE2.OBJ_TYPE;
                          args CLIENT_OLE2.LIST_TYPE;
                          MyApplication CLIENT_OLE2.OBJ_TYPE;
                          spe CLIENT_OLE2.OBJ_TYPE;
                     MySelection CLIENT_OLE2.OBJ_TYPE;
                          sel_text varchar2(10000);
                BEGIN
                     
                -- create a new document
                          app := CLIENT_OLE2.CREATE_OBJ('Word.Application');
                          CLIENT_OLE2.SET_PROPERTY(app,'Visible',1);
                          docs := CLIENT_OLE2.GET_OBJ_PROPERTY(app, 'Documents');
                doc := CLIENT_OLE2.INVOKE_OBJ(docs, 'add');
                          selection := CLIENT_OLE2.GET_OBJ_PROPERTY(app, 'Selection');
                -- insert data into new document from long item

                          CLIENT_OLE2.SET_PROPERTY(selection, 'Text', :b1.t1);
                          spe:= CLIENT_OLE2.GET_OBJ_PROPERTY(MyApplication, 'ActiveDocument');
                -- save document as example.tmp
                          
                sel_text:=CLIENT_OLE2.GET_CHAR_PROPERTY(MySelection, 'Text');
                :b1.t1:=:b1.t1||sel_text;

                /*args := CLIENT_OLE2.CREATE_ARGLIST;
                CLIENT_OLE2.ADD_ARG(args, :b1.t1);
                          CLIENT_OLE2.INVOKE(doc, 'SaveAs', args);
                          CLIENT_OLE2.DESTROY_ARGLIST(args);
                -- close example.tmp
                          
                --args := CLIENT_OLE2.CREATE_ARGLIST;
                          --     CLIENT_OLE2.ADD_ARG(args, 0);
                          --     CLIENT_OLE2.INVOKE(doc, 'Close', args);
                          --     CLIENT_OLE2.DESTROY_ARGLIST(args);*/
                          CLIENT_OLE2.RELEASE_OBJ(selection);
                          CLIENT_OLE2.RELEASE_OBJ(MyApplication);
                          CLIENT_OLE2.RELEASE_OBJ(spe);
                     CLIENT_OLE2.RELEASE_OBJ(MySelection);
                          
                               CLIENT_OLE2.RELEASE_OBJ(doc);
                               CLIENT_OLE2.RELEASE_OBJ(docs);
                -- exit MSWord

                               CLIENT_OLE2.INVOKE(app,'Quit');

                END;

                help me.

                Thanking you
                Avinash