Using Microsoft Query to retrieve data from Oracle 10G in Excel

Configuration:

Windows 7 x64 Enterprise

Office Professional x64 (Excel 2010)

Microsoft Query (part of excel x64)

Step 1:

• Download Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications (instantclient-basic-windows.x64-11.2.0.4.0.zip (54,956,947 bytes)) from the website http:// www.oracle.com/technetwork/topics/winx64soft-089540.html

• Create a home directory for example: C: \ ORACLE \ ORACLI11

• unpack the zip file in the directory C: \ ORACLE \ ORACLI11

Step 2:

• Download Instant Client Package - ODBC: Additional libraries for enabling ODBC applications

• (instantclient-odbc-windows.x64-11.2.0.4.0.zip (1,358,385 bytes))

• unpack the files from the zip file in the folder C: \ ORACLE \ ORACLI11

• Start the Command Prompt as Administrator (Run As Administratr)

• In the directory C: \ ORACLE \ ORACLI11 run file odbc_install.exe

• Copy sqlnet.ora and tnsnames in the folder C: \ ORACLE \ ORACLI11

• Create Enviroment a variable TNS_ADMIN and set the value as C: \ ORACLE \ ORACLI11

• After that, should the ODBC to appear: 

Step 3:

• Start MS Excel

• Choose "Tab Data"

• Choose "From other sources"

• Choose "Microsoft Query"

• "Choose Data Source" than "New Data Source" ("Select the default table ..." is  gray for now) 

• Choose "Connect" 

 Service Name: tnsnames.ora entry for the selected database 

• "Select the default table..." is no longer gray and gives a choice of a table 

 • In the "Query Wizard" choose the columns that will be used: 

• In the "Filter Data" select what row would you like to include

• In the "Sort Order" choose columns to sort data: 

• And finally choose to return the data to Microsoft Excel: