Converting Data from Excel to Oracle

Goals

To detail how to successfully convert an Excell macro to Oracle from a 64-bit machine.

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 (*.xls, *.xlsx, *.xlsm, *.xlsb)};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.ora 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.
  • recordSet.Open 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.

Proposed next steps:

You need to be the project owner to select a next step.