5 Replies Latest reply: Mar 6, 2014 12:06 AM by Christian Erlinger RSS

    PlSql open read cell in excel file!!

    user12923153

      I am  searching for how to open file excel , read cell and fetch data cell into a variable in a procedure using PLSQL.

      is DBMS or UTL_FILE package useful for this subject? if yes any one can gave me an example or a link to get a vision on how to use one of these package with excel file??

      thank you.

        • 1. Re: PlSql open read cell in excel file!!
          Christian Erlinger

          That would highly depend on the forms version you are using.

          If you have a xls*x* file you could use a stored procedure to process your file given the fact it resides on the database server.

           

          https://www.google.com/search?q=xlsx+plsql

           

          cheers

          • 2. Re: PlSql open read cell in excel file!!
            user12923153

            I write the flowing procedure to read an excel file:

             

            -- P58_READ_XLS_FILE_DBMS

              AS

                       v_metar   varchar2(200);  

                 result     INTEGER;  

                 i        binary_integer;

                 filename varchar2(255);

              

               BEGIN  

                     filename := 'C:\Climate_File\FILE_TXT_DBMS1.xls';  

                 result := ORDExcelSB.CreateExcelApplication('');

                 result := ORDExcelSB.OpenExcelFile(filename, 'Sheet1');  

                  --Excluding the header row and reading the first 5 row

                FOR n in 6 .. 10 LOOP

                  dbms_output.put_line(to_char(n)); 

                   v_metar          := ORDExcelSB.GetDataStr('A' || to_char(n));    

                   dbms_output.put_line(V_metar);    

                 END LOOP;  

                 result := ORDExcelSB.ExitExcel();

               EXCEPTION

                 WHEN OTHERS THEN

                   result := ORDExcelSB.ExitExcel();

                   RAISE;

               END;

             

            when I execute the following procedure I m getting the following output:

            Creating Excel application...
            Invoking Workbooks...
            Opening Excel file C:\Climate_File\FILE_TXT_DBMS1.xls ...
            Opening WorkBook
            Invoking WorkSheets..
            Invoking WorkSheet
            Opened
            COM-0002: Invalid Token or no interface for token
            COM-0002: Invalid Token or no interface for token
            COM-0002: Invalid Token or no interface for token
            COM-0002: Invalid Token or no interface for token
            COM-0002: Invalid Token or no interface for token
            COM-0002: Invalid Token or no interface for token
            COM-0002: Invalid Token or no interface for token
            COM-0002: Invalid Token or no interface for token
            COM-0002: Invalid Token or no interface for token
            COM-0002: Invalid Token or no interface for token
            Closing workbook and quitting...
            Closing workbook...
            Closing workbooks...
            PL/SQL procedure successfully completed.

             

            don't know why this doesn't write the content of the  FILE_TXT_DBMS1.xls' what dos this output mean ?? is there ay error?? thank you.  

            the content of  FILE_TXT_DBMS1.xls'  must be :

            Aviation Digital Data Service (ADDS)

             

            Output produced by METARs form (1447 UTC 05 March 2014)

            found at http://aviationweather.gov/adds/metars/

             

            OLBA 051400Z 03006KT 350V070 CAVOK 20/08 Q1014 NOSIG

            OLBA 051300Z 33011KT CAVOK 19/11 Q1015 NOSIG

            OLBA 051200Z 03007KT 340V070 CAVOK 21/09 Q1014 NOSIG

            OLBA 051100Z 33010KT CAVOK 20/11 Q1015 NOSIG

            OLBA 051000Z 32008KT 9999 SKC 20/10 Q1016

            OLBA 050900Z 32009KT 8000 SKC 19/11 Q1017 NOSIG

            OLBA 050800Z 30005KT 240V340 9999 SKC 19/10 Q1017

            OLBA 050700Z 01004KT 340V050 CAVOK 18/09 Q1017 NOSIG

            OLBA 050600Z 11003KT 070V190 CAVOK 17/04 Q1016 NOSIG

            OLBA 050500Z 10005KT CAVOK 15/03 Q1016 NOSIG

            OLBA 050400Z 09005KT CAVOK 14/04 Q1016 NOSIG

            OLBA 050300Z 09005KT CAVOK 14/05 Q1016 NOSIG

            OLBA 050200Z 09005KT CAVOK 15/05 Q1016 NOSIG

            • 3. Re: PlSql open read cell in excel file!!
              InoL

              This has nothing to do with Forms, really.

              Furthermore, ORDExcelSB is not an Oracle package. You have to ask the person who created it for assistance.

              • 4. Re: PlSql open read cell in excel file!!
                user12923153

                thank you Mr. I appreciate your support

                I found the problem it was because an error in the sheet name parameter

                 

                result := ORDExcelSB.OpenExcelFile(filename, 'Sheet1');                   must be

                result := ORDExcelSB.OpenExcelFile(filename, 'FILE_TXT_DBMS1'); 

                 

                again thank you a lot for your help, and execute me if I bother you , always you have been ready for any problem when I post it .

                • 5. Re: PlSql open read cell in excel file!!
                  Christian Erlinger

                  and execute me if I bother you

                  Holy cow...! I don't know what you think of me, but I am certainly not a mobster so I am not going to execute you

                   

                  but there is nothing to excuse, if I wouldn't want to help I simply wouldn't respond

                   

                  cheers