Click here to Skip to main content
15,506,865 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel
Posted 3 Jul 2012


3 bookmarked

Oracle data into Excel sheet

Rate me:
Please Sign up or sign in to vote.
4.47/5 (4 votes)
3 Jul 2012CPOL4 min read
Dynamic data retrieval from Oracle database to Excel sheet based on a parameter from Excel cell value

Image 1


In MS. Excel, as we are dealing with more data, it may become 

difficult to store, analyze and filter data, in which, many of us would decide
to move to an RDBMS system like MS Access; however, in many cases, this
decision may not be taken easily which is why most of us would just drop the
idea and deal with what we have. In this article, we’re going to learn how we
can move the bigger chunk of data and keep the spreadsheets for a neat
presentation of our information.<o:p>


Note: Please notice that this article is only dealing with one way
data flow i.e. from Oracle database to MS Excel.<o:p>




As a software developer, I have tried performing this task by
means of developing a solution, a C# application and when things got fuzzy, I
moved to a neat peace of software called Excel DNA by CodePlex “still using C#”.
And then paused for a minute and thought, since this’s a simple task that involves
data retrieval from Oracle database to Excel “but not the opposite”, why not
use a standard ODBC driver? And that’s when the problem was solved.<o:p>


To carry out with this tutorial, you’ll need to know about ODBC
drivers and basic knowledge about composing SQL statements “Structured Query


What is ODBC?<o:p>


According to Wikipedia, ODBC (Open Database
) is a standard C programming language interface for
accessing database management systems (DBMS).
The designers of ODBC aimed to make it independent of database systems
and operating systems. An application can use ODBC
to query data from a DBMS, regardless of the operating system or DBMS it uses.<o:p>


ODBC accomplishes DBMS independence by using an ODBC
driver as a translation layer between the application and the DBMS. The
application uses ODBC functions through an ODBC driver manager with
which it is linked, and the driver passes the query to the DBMS.<o:p>


ODBC defines a standard C API for accessing a relational DBMS.
It was developed by the SQL Access Group in
1992 to standardize the use of a DBMS by an application. ODBC provides a
universal middleware layer between the application and DBMS,
allowing the application developer to use a single interface. If changes are
made to the DBMS specification, only the driver needs updating. An ODBC driver
can be thought of as analogous to a printer or other driver, providing a
standard set of functions for the application to use, and implementing
DBMS-specific functionality.<o:p>


An application that can use ODBC is referred to as
"ODBC-compliant". Any ODBC-compliant application can access any DBMS
for which a driver is installed. Drivers exist for all major DBMSs and even for
text or CSV files.<o:p>




Getting started 


In this tutorial, we are going to retrieve data from Oracle
database into an Excel sheet, using data in an excel cell as a parameter in our

First, make sure you have the correct ODBC driver installed and

properly configured, and in this case, it is Oracle database.<o:p>


Open your windows 7 start menu and type odbc, select Data Sources
(ODBC) “alternatively, you may access it from Control Panel”<o:p>


<v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f">Image 2
<v:stroke joinstyle="miter">
Click add and select Microsoft ODBC for Oracle


 Image 3 


In this step, I would usually increase the buffer size immensely

for a faster data retrieval…<o:p>

 Image 4

Back to the drivers list, note that you can always configure the
basics of your driver by selecting is from the drivers list and clicking on “Configure”<o:p>

  Image 5

Now back to Excel, open any excel workbook and select Data >
From Other Sources > From Data Connection Wizard<o:p>

 Image 6

Unless you have an oracle provider in the list, select Other
Advanced and then “Microsoft OLE DB Provider for Oracle”<o:p>

 Image 7

Image 8

Image 9 

Configure to your preferences, like data refresh
intervals and most importantly now, your connection string and command text<o:p>

 Image 10

In the command text, box, you realize the part: LIKE ‘%’||?||’%’  I’m using for the criteria, the question mark ?
will prompt you later on for a parameter which will be proceeded and prefixed
by the modulus sign % which is a wildcard 
in Oracle equivalent to asterisk * representing >= 0 characters,
meaning, what ever word that contains the input parameter of which the latest
can be configured by the parameter screen

Image 11

We have used cell A3 in Sheet2 as a parameter here and
notice that we have selected the “Refresh automatically when cell value changes”
check box<o:p>

 Image 12

<v:shape id="Picture_x0020_4" o:spid="_x0000_i1027" type="#_x0000_t75" style="width: 269.25pt; height: 216.75pt; visibility: visible; ">
To maintain a fixed layout in your excel sheet:


  1.   Make sure your SQL statement returns one single value “mostly aggregated”


  2.      From the External Data Properties window, select the “Overwrite existing cells with new data, clear unused cells”


  You are ready! You just need to load your data and might do that by refreshing your connection

<v:shape id="Picture_x0020_6" o:spid="_x0000_i1026" type="#_x0000_t75" style="width: 426.75pt; height: 294.75pt; visibility: visible; "> <v:imagedata src="file:///C:\Users\MUAMMA~1.YAC\AppData\Local\Temp\msohtmlclip1\01\clip_image012.png">Image 13

  This example loads the relative gross metric tons for an

instruction order made by the logistics unit “as a parameter entered into cell
A2” and return its value into cell B2


Image 14 

<v:shape id="Picture_x0020_7" o:spid="_x0000_i1025" type="#_x0000_t75" style="width: 322.5pt; height: 230.25pt; visibility: visible; ">
<v:imagedata src="file:///C:\Users\MUAMMA~1.YAC\AppData\Local\Temp\msohtmlclip1\01\clip_image013.png">


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Retired QSoft
Yemen Yemen
Biography?! I'm not dead yet!

Comments and Discussions

QuestionThanks! Pin
Member 83394611-May-13 5:45
Member 83394611-May-13 5:45 
AnswerRe: Thanks! Pin
Member 1132120718-Dec-14 8:52
Member 1132120718-Dec-14 8:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.