Introduction
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>
Background
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
Language”<o:p>
What is ODBC?<o:p>
According to Wikipedia, ODBC (Open Database
Connectivity) 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
query.
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">
<v:stroke joinstyle="miter">
<v:formulas>
Click add and select Microsoft ODBC for Oracle
In this step, I would usually increase the buffer size immensely
for a faster data retrieval…<o:p>
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>
Now back to Excel, open any excel workbook and select Data >
From Other Sources > From Data Connection Wizard<o:p>
Unless you have an oracle provider in the list, select Other
Advanced and then “Microsoft OLE DB Provider for Oracle”<o:p>
Configure to your preferences, like data refresh
intervals and most importantly now, your connection string and command text<o:p>
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
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>
<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:
<o:p>
1. Make sure your SQL statement returns one single value “mostly aggregated”<o:p>
2. From the External Data Properties window, select the “Overwrite existing cells with new data, clear unused cells”
option.<o:p>
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">
<o:p>
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
<o:p>
<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">
<o:p>
