7 Replies Latest reply: Mar 5, 2013 10:16 AM by nydba RSS

    excel question

    nydba
      DB version:11gr2 and 10.2.0.3
      client version: ranges from 9i to 11.2
      excel version on local laptop/desktop: MS office 2003-till 2010

      Hello

      I have the following script ,which generates data into excel format and use batch /shell program to email the spreadsheet

      when opening the spreadsheet I get the following
      The file you are trying to open, abc.xls, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?
      alter session set nls_date_format = 'MM/DD/YYYY HH24:MI:SS';
      set pagesize 1000
      set linesize 2000
      set termout off
      set echo off
      set feed off markup html on spool on
      spool c:\temp\clients_report.xls
      
      select * from clients_info;
      
      spool off
      set markup html off spool off
      exit 
      is there different way to generate data in excel without getting above error

      Please advise and thank you
        • 1. Re: excel question
          sb92075
          spool c:\temp\clients_report.html
          • 2. Re: excel question
            Marwim
            Hello,

            you don't generate an Excel file, but an output with HTML tags. Excel can interpret the format and display the content. Yet Excel 2007 (and later) checks the content of the file against the expected format that is derived from the file extension. In your case it expects a binary (proprietary MS) format and finds a simple HTML-file.

            You can either give the file the correct extension as suggested above, but then the file will not be associated with Excel and it will be opened by default in your browser. Another possibility is to generate a different format, see {message:id=9360007}.

            Regards
            Marcus
            • 3. Re: excel question
              user-Keen
              You may get your desire result throug PL/SQL Developer Tool.

              By running query on above tool which place data in excel file.

              l
              • 4. Re: excel question
                nydba
                Hello

                thank you for the link

                I created that package and ran the following code and running into following errors

                begin
                as_xlsx.query2sheet( 'select * from dual' );
                as_xlsx.save( 'MY_DIR', 'my.xlsx' );
                end;





                Connected to:
                Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                With the Partitioning, Automatic Storage Management, OLAP, Data Mining
                and Real Application Testing options

                5 /
                begin
                *
                ERROR at line 1:
                ORA-29280: invalid directory path
                ORA-06512: at "SYS.UTL_FILE", line 41
                ORA-06512: at "SYS.UTL_FILE", line 478
                ORA-06512: at "tst.AS_XLSX", line 140
                ORA-06512: at "tst.AS_XLSX", line 1812
                ORA-06512: at line 3


                SQL>



                Marwim wrote:
                Hello,

                you don't generate an Excel file, but an output with HTML tags. Excel can interpret the format and display the content. Yet Excel 2007 (and later) checks the content of the file against the expected format that is derived from the file extension. In your case it expects a binary (proprietary MS) format and finds a simple HTML-file.

                You can either give the file the correct extension as suggested above, but then the file will not be associated with Excel and it will be opened by default in your browser. Another possibility is to generate a different format, see {message:id=9360007}.

                Regards
                Marcus
                • 5. Re: excel question
                  Marwim
                  as_xlsx.save( 'MY_DIR', 'my.xlsx' );
                  ...
                  ORA-29280: invalid directory path
                  How did you create the directory and does the user have the necessary permissions?
                  See Prerequisites at http://matzberger.de/oracle/file_access-en.html
                  • 6. Re: excel question
                    nydba
                    thank you

                    now I get this

                    as_xlsx.query2sheet( 'select * from dual' );
                    *
                    ERROR at line 2:
                    ORA-06550: line 2, column 3:
                    PLS-00201: identifier 'AS_XLSX.QUERY2SHEET' must be declared
                    ORA-06550: line 2, column 3:
                    PL/SQL: Statement ignored
                    ORA-06550: line 3, column 3:
                    PLS-00201: identifier 'AS_XLSX.SAVE' must be declared
                    ORA-06550: line 3, column 3:
                    PL/SQL: Statement ignored
                    • 7. Re: excel question
                      nydba
                      nm , user error , thank you ,it worked this time

                      Edited by: nydba on Mar 5, 2013 8:15 AM