Wonder
Log in
Research Outline
Prepared for Dan S. | Delivered January 10, 2020
Converting Data from Excel to Oracle
Review your project details
Goals
To detail how to successfully convert an Excell macro to Oracle from a 64-bit machine.
View less
Early Findings
The most important thing is to first ensure that all the drivers and programs are
64-bit
in order to ensure compatibility, as a mix of 32-bit and 64-bit could pose problems and crashes.
Transferring data between Excel and Oracle
If the idea is to transfer data between the Excel sheet and the Oracle database, the easiest way is to use the
ODBC driver
.
As such, the
Connecting string
is very important and should be set as such: "Driver={Microsoft Excel Driver (*.
x
l
s
, *.
x
l
s
x
, *.
x
l
s
m
, *.
x
l
s
b
)};DBQ=MyPath/MyFile"
Then, you need to set up a system
Data Source Name
(DSN) using the Microsoft ODBC Administrator and create a heterogeneous service initialization file.
You should then modify the
listener.
o
r
a
file. Here, you need to pay attention to the way you set up the SID when creating the heterogeneous services file, as they need to be the same.
Finally, you need to create the
ODBC source module
and import the metadata from Excel by using the Metadata Import Wizard.
Connecting to an Oracle database through an Excel macro
The connection is done through the
VBA function of Excel
, which is used to retrieve the records and populate the spreadsheet.
r
e
c
o
r
d
S
e
t
.
O
p
e
n
can also be used to work on tables of a database.
An Excel macro can also be used to compare data from different Oracle databases. Here a specific "
Do While
" and “While
–
Wend” loops are sued to get the column headers, to retrieve the records and populate the sheet.
All the coding language for the macro used in the operation can be found
here.
View less