8 Replies Latest reply: Sep 10, 2013 4:36 AM by BluShadow RSS

    Export from oracle to excel

    542647
      Hi ,

      I like to export data from oracle to excel.

      2 ways to do it i found is

      First through "Import external Data" function in EXCEL but that is not working on my system
      Secondly through toad utility.

      But i like to have a way where i can press one button and it will refresh the updated data.

      Thanx
        • 1. Re: Export from oracle to excel
          Sven W.
          The import external data is a good way to go.

          What you need to do there is create an ODBC connection to your database. usually you choose the same parameters for your connection as you would do in TOAD.
          • 2. Re: Export from oracle to excel
            BEDE
            You may use a tool like TOAD or PL/SQL Developer, that have the facility to export to Excel.
            Or you may connect the Excel to Oracle through ADO. In Excel you have the querytable object, that may be quite useful.
            Beware of the sheets limit of only 65536 rows. In an Oracle table you may have millions of rows. So, you must filter and/or aggregate for large tables.
            • 3. Re: Export from oracle to excel
              542647
              What i have seen in toad is it export data in txt,sql and other format but not in excel. Secondly i need option where i just have to refresh the data by a single click like we do in "Import external Data". I have used "import External Data" with sql but never with Oracle. and when i am doing that it is asking for tool MS Query? Doest it support MS excel 2000?
              • 4. Re: Export from oracle to excel
                Sven W.
                Use "+Connect to New Data Source" then choose "Oracle" in the database connection wizard from Excel.
                • 5. Re: Export from oracle to excel
                  sql_dev
                  From Sql plus, u can try like this..

                  SQL> set feed off markup html on spool on

                  SQL> spool 'd:\filename.xls'

                  SQL> select * from s_school_type;--Your query

                  SQL> spool off

                  SQL> set markup html off spool off

                  SQL>
                  • 6. Re: Export from oracle to excel
                    542647
                    Use "+Connect to New Data Source" then choose
                    "Oracle" in the database connection wizard from Excel.
                    thanx....it works as you say .... thanx alot
                    • 7. Re: Export from oracle to excel
                      brilliantmind

                      Simply follow this procedure

                      1. Open NEW EXCEL SHEET
                      2. Select DATA
                      3. Then select External Data
                      4. Then select From Other Resources
                      5. Then select from Data Connection Wizard
                      6. Then select Microsoft Data Access- OLE DB Provider for Oracle
                      7. Then enter Next Button
                      8. Then enter your required Server name,Username and Password
                      • 8. Re: Export from oracle to excel
                        BluShadow

                        I think after 5 years the OP will have either found his solution or left for another job.

                         

                        DO NOT drag up old threads unnecessarily.  There are plenty of active discussions that need assistance without you dragging up old ones.

                         

                        Locking this thread