4

I have an Oracle 10G database and I need to write a fairly straightforward query that joins two tables and selects some data. However, I'd like to export the result list to an excel, so end users can use this .xls document to see the results and filter by one of the fields (location)

When I write the query, is there an easy way I can generate/ create an excel document that would hold these results as described above? The SQL doesn't need to run from within excel, but I guess that would be a useful feature now that I think about it! Thanks.

Improve this question
0

7 Answers 7

Reset to default
4

There is simple solution for your request.

By using ora_excel, small pl/sql package which generates Excel xlsx file, you can select data and export selected data to Excel and set filtering.

Please see following example:

BEGIN    
    ORA_EXCEL.new_document;    

    ORA_EXCEL.add_sheet('My sheet');        
    ORA_EXCEL.query_to_sheet('select * from employees'); -- Select data from database   
    ORA_EXCEL.set_cells_filter('A1', 'K1'); -- Add cell filtering from column A1 to column K1    


    -- Save generated Excel to file with name example.xlsx to Oracle folder EXAMPLE_XLSX
    ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx'); 
END;

For more details please check here

Cheers

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

if I do several query_to_sheet will the previous data be overwritten or will the data from next query be placed in the next row?
4

Pretty easy to do in excel; and when done user can right click the data and say "Refresh" to get the latest updates.

but why reinvent the wheel lots of online articles already explain how to do this... Here's one

http://blog.mclaughlinsoftware.com/microsoft-excel/how-to-query-oracle-from-excel-2007/

After you've connected to a table, you can edit the properties on the connection and enter custom SQL (copy and paste from your developer tools)

Improve this answer

1 Comment

Unfortunately I'm stuck on excel 2003. I guess I should be able to use sql developer to export the results to an excel doc for the moment?
3

Since you cannot use OLE DB in your version of Excel. Use SPOOL to create a CSV file.

 SQL> SET echo off
 SQL> SET verify off
 SQL> SET colsep ,     
 SQL> SET pagesize 0   
 SQL> SET trimspool on
 SQL> SET feedback off
 SQL> SPOOL ON
 SQL> SPOOL C:\data.csv
 SQL> SELECT COLUMN1,COLUMN2,COLUMN3....
      FROM TABLE;
 SQL> SPOOL OFF

The .csv file should open in Excel by default. Use proper column aliases so that users understand the column headers.

Improve this answer

4 Comments

This method just creates a .csv file of my actual oracle query.
Yes, you can view the .csv file using Excel.
Yes, but the file contains the query itself; not the query result.
you need to put your SELECT, and the SPOOL commands into a script. and you might as well put the set commands into the script as well. and also add SET TRIMSPOOL ON
2

Quick way:

At first create a view which contains your Query(Best way because you might need to change this query later).

Be sure to properly have installed oracle client.

  • In Excel(2007 and above) in Data tab go this way:

From Other sources -> From Data Connection Wizard -> Microsoft Data Access - OLE DB Provider for Oracle

  • Now Enter your DataSource Name(Stored in tnsnames.ora) and user password

  • Find you view and Then You'll have what you need.

You can save password and set option to refresh automatically in connection properties.

Improve this answer

Comments

2

You are able to query an oracle database directly from Excel 2003 however, your sql statements are interpreted by MS Query and because of this it can often be frustrating. I will assume the machine in question already has the ability to query your database and has properly configured the database naming.

To query your database from excel 2003 you must:

  1. Install and configure oracle's ODBC Driver (You must have the 32bit drivers installed since excel03 is a 32bit application). ODBC can be configured under start > administrative tools > ODBC Data Source Administrator

  2. Open excel 2003 and goto data > import external data > new database query.

This should bring up MS Query which is an Access-like interface.

Obviously this is a very brief starter to get you stepping in the right direction. If you have any specific questions, please comment and I will try and help you.

Improve this answer

2 Comments

Getting this error. dba-oracle.com/t_ora_12560_tns_protocol_adapter_error.htm I can access the database fine from sql developer on this machine. SO trying to troubleshoot it now.
My SQL was too complex for this method to understand - (I fixed the above error by editing TNSNAMES) The connection was successful, but the Microsoft SQL tool didn't like my query.
2

Step 1 Run Your Query Right Click on Resultenter image description here

Step 2 Click on Export enter image description here

Step 3 Select Format To Excel Enter datasheet name and location

Step 4 Click on Next and then finish enter image description here

Improve this answer

Comments

1

You can do one thing.

  1. First generate the output in a form that includes column separators using symbols (like , or #).
  2. Import the data to the excel and then define the placeholders as the column separators.
Improve this answer

2 Comments

I usually use your method! because it is better in performance.(Excel table updates are slow!)
Exactly. It is much faster this way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.