1 2 Previous Next 26 Replies Latest reply: Nov 19, 2013 9:23 AM by Andreas Weiden RSS

    Import Data From Excel

    rdnits

      I am trying to import data from excel file test.xml to db table emp.

      What i have done is

       

      DECLARE

      application OLE2.OBJ_TYPE;

      workbooks OLE2.OBJ_TYPE;

      workbook OLE2.OBJ_TYPE;

      worksheets OLE2.OBJ_TYPE;

      worksheet OLE2.OBJ_TYPE;

      cell OLE2.OBJ_TYPE;

      args OLE2.OBJ_TYPE;

      ctr NUMBER(12);

      cols NUMBER(2);

      name_var1 VARCHAR2(2000);

      name_var2 VARCHAR2(2000);

      name_var3 VARCHAR2(2000);

      name_var4 VARCHAR2(2000);

      name_var5 VARCHAR2(2000);

      filename varchar2(100);

        vcInsert                           VARCHAR2(3500);

       

       

      PROCEDURE OLEARG IS

      args OLE2.OBJ_TYPE;

      BEGIN

      args := OLE2.CREATE_ARGLIST;

      ole2.add_arg(args,ctr); -- row value

      ole2.add_arg(args,cols); -- column value

      cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args); -- initializing cell

      ole2.destroy_arglist(args);

      END;

       

       

      BEGIN

      filename :=GET_FILE_NAME('V:\Corp\IS\ALL\a\New folder','Test.xls','XLS Files (*.xls)|*.xls|');

      application := OLE2.CREATE_OBJ('Excel.Application');

      ole2.set_property(application,'Visible','true');

      workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

      args := OLE2.CREATE_ARGLIST;

      ole2.add_arg(args,filename); --'c:\13s002.xls'); -- file path and name

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

      ole2.destroy_arglist(args);

      /*args := OLE2.CREATE_ARGLIST;

      ole2.add_arg(args,'Sheet1');

      worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);

      ole2.destroy_arglist(args); */

      worksheet := OLE2.GET_OBJ_PROPERTY(application,'activesheet');

      OLE2.SET_PROPERTY(worksheet , 'Value','Sheet1');

       

       

      ctr := 2; --row number

      cols := 1; -- column number

       

       

      FIRST_RECORD;

       

       

      LOOP

      OLEARG;

      name_var1 := ole2.get_num_property(cell,'Value'); --cell value of the argument

      cols := cols+1;

       

       

      OLEARG;

      name_var2 := ole2.get_char_property(cell,'Value'); --cell value of the argument

      cols := cols+1;

       

       

      OLEARG;

      name_var3 := ole2.get_char_property(cell,'Value'); --cell value of the argument

      cols := cols+1;

       

       

      OLEARG;

      name_var4 := ole2.get_num_property(cell,'Value'); --cell value of the argument

      cols := cols+1;

       

       

      OLEARG;

      name_var5 := ole2.get_num_property(cell,'Value'); --cell number value of the argument

       

       

      IF ctr = 1 then

      vcInsert :=('INSERT INTO emp VALUES('||name_var1||''','''||name_var2||''','''||name_var3||''','''||name_var4||''','''||name_var5||')') ;

      FORMS_DDL(vcInsert);

      commit_form;

      ELSE

      NEXT_RECORD;

      vcInsert :=('INSERT INTO emp VALUES('||name_var1||''','''||name_var2||''','''||name_var3||''','''||name_var4||''','''||name_var5||')') ;

      FORMS_DDL(vcInsert);

      commit_form;

      END IF;

       

       

      EXIT WHEN length(name_var1) = 0 or length(name_var1) is null;

      ctr := ctr + 1;

      cols := 1;

       

       

      END LOOP;

      OLE2.INVOKE(application,'Quit');

      OLE2.RELEASE_OBJ(cell);

      OLE2.RELEASE_OBJ(worksheet);

      OLE2.RELEASE_OBJ(worksheets);

      OLE2.RELEASE_OBJ(workbook);

      OLE2.RELEASE_OBJ(workbooks);

      OLE2.RELEASE_OBJ(application);

      OLE2.INVOKE(worksheet, 'Save');

      OLE2.INVOKE(workbook, 'Save');

      OLE2.INVOKE(workbook,'CLOSE');

      END;

       

       

      I am new to OLE2 package , so took some help of google and still confused with what's wrong.

      Its compiling successfully and opening the excel file but now writing any data on it + when i close the excel file it gives me error 40102:-Records must be entered or deleted first

      FRM 40401:-No changes to save

      and ORA305500

        • 1. Re: Import Data From Excel
          Priyasagi

          Hi, have you convert it into CSV file format?  Read the following discussion it's helpful to you.

           

          How to import a xls excel file to oracle database PL/SQL?

          • 2. Re: Import Data From Excel
            rdnits

            MODIFIED CODE

             

            The requirement wants me to import using ole2 only

             

            I want to export the data from excel to DB,code is compiling without any issues and running but when i am executing the following code it is not exiting the loop

            In the progress field i am getting next row inserted and after that it keeps on inserting 0's to the table .

             

            DECLARE

            application OLE2.OBJ_TYPE;

            workbooks OLE2.OBJ_TYPE;

            workbook OLE2.OBJ_TYPE;

            worksheets OLE2.OBJ_TYPE;

            worksheet OLE2.OBJ_TYPE;

            cell OLE2.OBJ_TYPE;

            args OLE2.OBJ_TYPE;

            ctr NUMBER(12);

            cols NUMBER(2);

            name_var1 Number(38);

            name_var2 VARCHAR2(20);

            name_var3 VARCHAR2(20);

            name_var4 Number(5);

            name_var5 Number(5);

            filename varchar2(100);

              vcInsert                           VARCHAR2(3500);

             

             

             

            PROCEDURE OLEARG IS

            args OLE2.OBJ_TYPE;

            BEGIN

            args := OLE2.CREATE_ARGLIST;

            ole2.add_arg(args,ctr); -- row value

            ole2.add_arg(args,cols); -- column value

            cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args); -- initializing cell

            ole2.destroy_arglist(args);

            END;

             

             

             

            BEGIN

            filename :=GET_FILE_NAME('V:\Corp\IS\ALL\a\New folder','Test.xls','XLS Files (*.xls)|*.xls|');

            application := OLE2.CREATE_OBJ('Excel.Application');

            ole2.set_property(application,'Visible','true');

            workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

            args := OLE2.CREATE_ARGLIST;

            ole2.add_arg(args,filename); --'c:\13s002.xls'); -- file path and name

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

            ole2.destroy_arglist(args);

            /*args := OLE2.CREATE_ARGLIST;

            ole2.add_arg(args,'Sheet1');

            worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);

            ole2.destroy_arglist(args); */

            worksheet := OLE2.GET_OBJ_PROPERTY(application,'activesheet');

            OLE2.SET_PROPERTY(worksheet , 'Value','Sheet1');

             

             

            ctr := 1; --row number

            cols := 1; -- column number

             

             

            :progress:= 'Excel File Open';

            FIRST_RECORD;

             

             

            LOOP

            OLEARG;

            name_var1 := ole2.get_num_property(cell,'Value'); --cell value of the argument

            cols := cols+1;

             

             

            OLEARG;

            name_var2 := ole2.get_char_property(cell,'Value'); --cell value of the argument

            cols := cols+1;

             

             

            OLEARG;

            name_var3 := ole2.get_char_property(cell,'Value'); --cell value of the argument

            cols := cols+1;

             

             

            OLEARG;

            name_var4 := ole2.get_num_property(cell,'Value'); --cell value of the argument

            cols := cols+1;

             

             

            OLEARG;

            name_var5 := ole2.get_num_property(cell,'Value'); --cell number value of the argument

            :progress:='First Row Fetched';

             

            IF ctr = 1 then

            vcInsert :=('INSERT INTO emp VALUES('||name_var1||' ,' '||name_var2||' ',' '||name_var3||' ','||name_var4||','||name_var5||')') ;

            FORMS_DDL(vcInsert);

            --commit_form;

            STANDARD.COMMIT;

            :progress:='First Row Inserted';

            ELSE

            NEXT_RECORD;

            vcInsert :=('INSERT INTO emp VALUES('||name_var1||',' '||name_var2||' ',' '||name_var3||' ','||name_var4||','||name_var5||')') ;

            FORMS_DDL(vcInsert);

            --commit_form;

            STANDARD.COMMIT;

            :progress:='Next Row Inserted';

            END IF;

            standard.commit;

             

             

            EXIT WHEN length(name_var1) = 0 or length(name_var1) is null;

            ctr := ctr + 1;

            cols := 1;

             

             

            END LOOP;

             

             

             

             

            :progress:=' Data inserted into table Successfully';

             

             

            OLE2.INVOKE(worksheet, 'Save');

            OLE2.INVOKE(worksheet,'CLOSE');

            OLE2.INVOKE(workbook, 'Save');

            OLE2.INVOKE(workbook,'CLOSE');

            OLE2.INVOKE(application,'Quit');

            OLE2.RELEASE_OBJ(cell);

            OLE2.RELEASE_OBJ(worksheet);

            OLE2.RELEASE_OBJ(worksheets);

            OLE2.RELEASE_OBJ(workbook);

            OLE2.RELEASE_OBJ(workbooks);

            OLE2.RELEASE_OBJ(application);

             

             

            END;

             

             

            I am having 2 errors

             

            1-> Code is not exiting the loop , i have to manually close the excel file to make it exit the loop, tried various possiblities but nothing is working

            2-> While inserting varchar it is just inserting ||name_var2|| and ||name_var3||

             

            Please help, stuck on the infinite loop since 2 days!!!!!!!!!

            • 3. Re: Import Data From Excel
              Andreas Weiden

              Did you check what is in your name1 when it should exit the loop?

              • 4. Re: Import Data From Excel
                rdnits

                in name1 0 is there because it is inserting another row into the database infinitely with all feilds as 0;

                • 5. Re: Import Data From Excel
                  Andreas Weiden

                  Well, if its is 0 and you check for a length of 0 or null to exit the loop, its quite clear that this condition is never met.

                   

                  Is there really a 0 in all rows? If not, it could be that the get_num_property returns 0 for empty cells, you could read the first cell as char-cell and check that for null-

                  • 6. Re: Import Data From Excel
                    Priyasagi

                    Before 'exit when' display the values of the name_var1,name_var2,name_var3,name_var4 and name_var5.

                    • 7. Re: Import Data From Excel
                      rdnits

                      i used

                      END IF;

                      standard.commit;

                      :val_check1:=name_var1;

                      :val_check2:=name_var2;

                      :val_check3:=name_var3;

                      :val_check4:=name_var4;

                      :val_check5:=name_var5;

                       

                      EXIT WHEN length(name_var3) = 0 or length(name_var3) is null;

                      ctr := ctr + 1;

                      cols := 1;

                       

                       

                      END LOOP;


                      Now the excel sheet is not closing but it is inserting only 3 rows instead of 2 and value of :val_check1,4,5 =0

                      and :val_check2,3=null.

                      • 8. Re: Import Data From Excel
                        rdnits

                        This is the final modified code,

                        Its working fine and also inserting the correct amount of rows.

                        But couple of issues are there

                        1:-The excel sheet is not closing itself, i have to manually close it(I am using Forms6i).

                             And when i manuallty close it is showing trigger raise unhandled exception ORA-06502

                        2:-All the feilds in which i want to actually impliment this functionality are number feilds only hence i am afraid these       will run infinitely, but again i can check the value for that if it is 0, but this is just in case.

                         

                        Kindly help in resolving the first issue or all the point of my automation will be waste!!!!!!!

                         

                         

                        DECLARE

                        application OLE2.OBJ_TYPE;

                        workbooks OLE2.OBJ_TYPE;

                        workbook OLE2.OBJ_TYPE;

                        worksheets OLE2.OBJ_TYPE;

                        worksheet OLE2.OBJ_TYPE;

                        cell OLE2.OBJ_TYPE;

                        args OLE2.OBJ_TYPE;

                        ctr NUMBER(12);

                        cols NUMBER(2);

                        name_var1 Number(38);

                        name_var2 VARCHAR2(20);

                        name_var3 VARCHAR2(20);

                        name_var4 Number(5);

                        name_var5 Number(5);

                        filename varchar2(100);

                          vcInsert                           VARCHAR2(3500);

                         

                         

                        PROCEDURE OLEARG IS

                        args OLE2.OBJ_TYPE;

                        BEGIN

                        args := OLE2.CREATE_ARGLIST;

                        ole2.add_arg(args,ctr); -- row value

                        ole2.add_arg(args,cols); -- column value

                        cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args); -- initializing cell

                        ole2.destroy_arglist(args);

                        END;

                         

                         

                        BEGIN

                        filename :=GET_FILE_NAME('V:\Corp\IS\ALL\a\New folder','Test.xls','XLS Files (*.xls)|*.xls|');

                        application := OLE2.CREATE_OBJ('Excel.Application');

                        ole2.set_property(application,'Visible','true');

                        workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

                        args := OLE2.CREATE_ARGLIST;

                        ole2.add_arg(args,filename); --'c:\13s002.xls'); -- file path and name

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

                        ole2.destroy_arglist(args);

                        /*args := OLE2.CREATE_ARGLIST;

                        ole2.add_arg(args,'Sheet1');

                        worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);

                        ole2.destroy_arglist(args); */

                        worksheet := OLE2.GET_OBJ_PROPERTY(application,'activesheet');

                        OLE2.SET_PROPERTY(worksheet , 'Value','Sheet1');

                         

                         

                        ctr := 1; --row number

                        cols := 1; -- column number

                         

                         

                        :progress:= 'Excel File Open';

                        FIRST_RECORD;

                         

                         

                        LOOP

                        OLEARG;

                        name_var1 := ole2.get_num_property(cell,'Value'); --cell value of the argument

                        cols := cols+1;

                         

                         

                        OLEARG;

                        name_var2 := ole2.get_char_property(cell,'Text'); --cell value of the argument

                        cols := cols+1;

                         

                         

                        OLEARG;

                        name_var3 := ole2.get_char_property(cell,'Text'); --cell value of the argument

                        cols := cols+1;

                         

                         

                        OLEARG;

                        name_var4 := ole2.get_num_property(cell,'Value'); --cell value of the argument

                        cols := cols+1;

                         

                         

                        OLEARG;

                        name_var5 := ole2.get_num_property(cell,'Value'); --cell number value of the argument

                        :progress:='First Row Fetched';

                         

                         

                        EXIT WHEN length(name_var3) = 0 or length(name_var3) is null or name_var1=0;

                        IF ctr = 1 then

                        vcInsert :=('INSERT INTO emp VALUES('||name_var1||','''||name_var2||''','''||name_var3||''','||name_var4||','||name_var5||')') ;

                        FORMS_DDL(vcInsert);

                        --commit_form;

                        STANDARD.COMMIT;

                        :progress:='First Row Inserted';

                        ELSE

                        NEXT_RECORD;

                        vcInsert :=('INSERT INTO emp VALUES('||name_var1||','''||name_var2||''','''||name_var3||''','||name_var4||','||name_var5||')') ;

                        FORMS_DDL(vcInsert);

                        --commit_form;

                        STANDARD.COMMIT;

                        :progress:='Second Row Inserted';

                        END IF;

                        standard.commit;

                        :val_check:=name_var3;

                        :val_check:=name_var1;

                        EXIT WHEN length(name_var3) = 0 or length(name_var3) is null or name_var1=0;

                        ctr := ctr + 1;

                        cols := 1;

                         

                         

                        END LOOP;

                         

                         

                         

                         

                        :progress:=' Data inserted into table Successfully';

                         

                         

                        OLE2.INVOKE(worksheet, 'Save');

                        OLE2.INVOKE(worksheet,'CLOSE');

                        OLE2.INVOKE(workbook, 'Save');

                        OLE2.INVOKE(workbook,'CLOSE');

                        OLE2.INVOKE(application,'Quit');

                        OLE2.RELEASE_OBJ(cell);

                        OLE2.RELEASE_OBJ(worksheet);

                        OLE2.RELEASE_OBJ(worksheets);

                        OLE2.RELEASE_OBJ(workbook);

                        OLE2.RELEASE_OBJ(workbooks);

                        OLE2.RELEASE_OBJ(application);

                         

                         

                        END;

                        • 9. Re: Import Data From Excel
                          Priyasagi

                          Replace

                          ole2.set_property(application,'Visible','true');

                          with

                          ole2.set_property(application,'Visible','false');

                          • 10. Re: Import Data From Excel
                            Priyasagi

                            Correct your insert statements as

                            vcInsert:= ('insert into emp values('||name_var1||','||name_var2||','||name_var3||','||name_var4||','||name_var5||')');

                            • 11. Re: Import Data From Excel
                              rdnits

                              Replacing true with false is a workaround which can be done to not open the file at all.

                              After that also i am getting the same exception and the : progress tab is not showing 'Data inserted successfully ', it is showing row inserted which means it got out of the loop but never executed any code after that ,

                               

                              END LOOP;

                               

                                 :progress:=' Data inserted into table Successfully';

                               

                               

                              OLE2.INVOKE(worksheet, 'Save');

                              OLE2.INVOKE(worksheet,'CLOSE');

                              OLE2.INVOKE(workbook, 'Save');

                              OLE2.INVOKE(workbook,'CLOSE');

                              OLE2.INVOKE(application,'Quit');

                              OLE2.RELEASE_OBJ(cell);

                              OLE2.RELEASE_OBJ(worksheet);

                              OLE2.RELEASE_OBJ(worksheets);

                              OLE2.RELEASE_OBJ(workbook);

                              OLE2.RELEASE_OBJ(workbooks);

                              OLE2.RELEASE_OBJ(application);

                               

                              The bold part never got executed.

                              • 12. Re: Import Data From Excel
                                Priyasagi

                                No its not coming out the loop. That is the problem.

                                Please correct your insert statement as per my previous post.

                                • 13. Re: Import Data From Excel
                                  rdnits

                                  Changed my insert command with ur's , still it is not changing the value or : progress to:='Data inserted into table Successfully'.

                                   

                                     :progress:=' Data inserted into table Successfully';

                                   

                                   

                                  OLE2.INVOKE(worksheet, 'Save');

                                  OLE2.INVOKE(worksheet,'CLOSE');

                                  OLE2.INVOKE(workbook, 'Save');

                                  OLE2.INVOKE(workbook,'CLOSE');

                                  OLE2.INVOKE(application,'Quit');

                                  OLE2.RELEASE_OBJ(cell);

                                  OLE2.RELEASE_OBJ(worksheet);

                                  OLE2.RELEASE_OBJ(worksheets);

                                  OLE2.RELEASE_OBJ(workbook);

                                  OLE2.RELEASE_OBJ(workbooks);

                                  OLE2.RELEASE_OBJ(application);


                                  This part is still not getting executed.

                                  • 14. Re: Import Data From Excel
                                    Andreas Weiden

                                    CHeck your OLE-commands. I don't think that your can Save a worksheet and i don't know if you can Close it. But i'm quite sure that you can't CLOSE either a worksheet nor a workbook (check case-sensitivity)

                                    1 2 Previous Next