Converting Data from Excel to Oracle

Part
01
of one
Part
01

Converting Data from Excel to Oracle

In order to import information from Oracle to Excel (and vice versa) certain drivers and codes are needed to connect the two systems. Importantly, the correct drivers must match the architecture of the chosen Excel version, in this case, 64-bit. Full details and step-by-step instructions are available below.

Oracle to Excel

  • If not already done, the user needs to add a reference for ADODB connection. This is done by: going to the VB Editor, selecting Tools>References, select "Microsoft ActiveX Data Objects 2.0 Library (2.0 or higher), click OK.
  • By default, the relevant Oracle drivers should be installed on a Windows machine. If not, they can be accessed from the Oracle website or here. It is important that the architecture of the Oracle driver must be the same as Excel (64-bit).
  • The first step is to install the Oracle client for Oracle 11g database. Then one should add the tnsnames.ora file path to the environment variable. This file can usually be located in C:\oracle\product\11.2.0.4\client_1\NETWORK\ADMIN for the full client or C:\oracle\instantclient_11_2_0_4 for the Instant.
  • Next, open the tnsnames.ora file and make sure to add a description for the XE data source.
  • When this is done, one should create a new user for the Oracle 11g database.
  • Next, open a blank Excel workbook to compose the VBA scripts. The steps to actually creating the macro, with sample code, are here.
  • Once this is done, click run. This action will then retrieve the relevant records and populate the default sheet (Sheet 1) in Excel. Furthermore, any query with recordSet.Open can be used to work on any tables inside the database.
  • There are other actions that can be taken if someone wants to compare different Oracle databases within the same macro.

Additional Commands

  • This webpage has dozens of additional Oracle connection strings that can be used in the coding, depending on the user's needs.
  • Additionally, one can actually connect Oracle through Excel through SID or service name. This webpage gives the coding for either option.

Further Information and Help

  • A screenshotted step-by-step of the initial steps is available here, for extra assistance. It shows how to initially link Oracle to Excel.
  • Another pictorial tutorial for the connection process is located here.

Excel to Oracle

  • Additionally, it is possible to add code in order to get Excel to add information to the Oracle database (the reverse of above). First, download the Oracle Objects for OLE here.
  • A sample code for the VBA is available on this page.
  • Here is another sample code that can be manipulated.
Sources
Sources