Configure Excel 2010 for Oracle

Problem - Data stored in an Oracle database needs to be accessed in Excel.  First step is to install Oracle software and configure.

In this case, database is Oracle 11gR2 on OEL.  From oracle.com/downloads, download the correct version of the Oracle client software (32-bit or 64-bit).

Install on your client machine.

Using netca.bat (located in $ORACLE_HOME/bin on your client machine), create a Net Service name to connect to the database e.g. orcl.

Do a quick tnsping to make sure that connectivity is available:
tnsping orcl

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.111.28)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (110 msec)

Make sure that port 22 is not blocked on your database server.

Excel Configuration
Ok, now the Excel config part.

• Open Excel 2010 and in a blank workbook, click the Data ribbon.

• Then click the From Other Sources button.

• From the list, select From Data Connection Wizard

• Select Oracle Provider for OLE DB and click Next.

• In Data Source enter the TNS Names alias configured earlier e.g. orcl.
• Supply a valid user name and password and click Test Connection.  If the test does not succeed, you will need to troubleshoot the error.

• In the Select Database and Table, select the table/view and click Next.

• Click Finish.

• Click OK to import your data into the current sheet.

• When prompted for a password, enter your credentials and press OK.

• Data should now appear in your spreadsheet.