Geek Answers Handbook

Output Oracle queries in excel

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

When I write a query, is there an easy way to create / create an excel document that will contain these results as described above? SQL does not need to be run from excel, but I think now it will be a useful function that I think of! Thanks.

+4
source share
6 answers

There is a simple solution for your request.

Using ora_excel, a small pl / sql package that generates an Excel xlsx file, you can select data and export the selected data to Excel and set filtering.

See the 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;

See here for more details.

Greetings

+3
source

Pretty easy to do in excel; and after that, the user can right-click the data and say “Update” to get the latest updates.

but why reinvent the wheel a lot of online articles already explain how to do it ... Here's one

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

After you have connected to the table, you can edit the properties in the connection and enter your own SQL (copy and paste from the developer tools)

+2
source

Quick way:

First, create a view that contains your query (the best way, because you may need to modify this query later).

Be sure to install the installed oracle client.

  • In Excel (2007 and later), on the Data tab, do the following:

From other sources → From the Data Connection Wizard → Microsoft Data Access - OLE DB Provider for Oracle

  • Now enter the name of the data source (stored in tnsnames.ora) and the user password

  • Find you, and then you will have what you need.

You can save the password and set the parameter to be automatically updated in the connection properties.

+2
source

Since you cannot use OLE DB in your version of Excel. Use SPOOL to create the 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 the correct column aliases so that users understand the column headers.

+2
source

You can do one thing.

  • First, generate output in a form that includes column separators using characters (such as or #).
  • Import the data into excel and then define the placeholders as column separators.
+1
source

You can query the oracle database directly from Excel 2003, however your SQL queries are interpreted by MS Query, and because of this it is often unpleasant. I assume that the machine in question already has the ability to query your database and correctly configured the database name.

To query your database from excel 2003, you must:

  • Install and configure the oracle ODBC driver (you must have 32-bit drivers installed, since excel03 is a 32-bit application). ODBC can be configured at Start> Administration Tools> ODBC Data Source Administrator

  • Open Excel 2003 and goto data> import external data> a new database query.

This should invoke MS Query, which is an access-like interface.

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

+1
source

More articles:


All Articles