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.
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.
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?
Use "+Connect to New Data Source" then choose "Oracle" in the database connection wizard from Excel.
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
Use "+Connect to New Data Source" then choosethanx....it works as you say .... thanx alot
"Oracle" in the database connection wizard from Excel.
Simply follow this procedure
- Open NEW EXCEL SHEET
- Select DATA
- Then select External Data
- Then select From Other Resources
- Then select from Data Connection Wizard
- Then select Microsoft Data Access- OLE DB Provider for Oracle
- Then enter Next Button
- Then enter your required Server name,Username and Password
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