How to Connect Excel to Oracle

Oracle is a renowned database management system. Business analytics tools such as Excel, Tableau, and Power BI can be used to visualize the data stored in Oracle databases, transforming it into valuable business insights. However, a key challenge in visualizing Oracle data with analytics tools is the ability to connect to and securely access your Oracle data. Progress DataDirect's ODBC driver for Oracle offers a secure, flexible solution for connecting Excel to Oracle. This tutorial walks you through the process of connecting Excel to Oracle using the ODBC driver on Windows. The following tasks are covered:

• Determine your Excel version
• Install and configure the Oracle ODBC driver
• Connect to Oracle from Excel and import its data

Determine whether your version of Excel is a 32-bit or 64-bit installation:

1. Open your workbook in Excel.
2. On the File menu, click Account, and then click About Excel. The About Microsoft Excel window appears.

   The version information is available at the top of the About Microsoft Excel window.

The following steps describe how to install and configure the driver for ODBC.

1. Click the Oracle ODBC Driver button.
The Progress DataDirect for ODBC for Oracle Wire Protocol driver provides ODBC connectivity to the Oracle database servers.
Oracle ODBC Driver
2. Select the driver you want to download based on your Excel version:
   • If you are using 32-bit Excel, select the Windows 32-bit driver.
   • If you are using 64-bit Excel, select the Windows 64-bit driver.
3. Provide the required information; then, click DOWNLOAD.
4. After downloading the product, unzip the installer files into a temporary directory, and then double-click the installer program:
   • For 32-bit drivers: PROGRESS_DATADIRECT_ODBC_8.0_WIN_32_INSTALL.exe
   • For 64-bit drivers: PROGRESS_DATADIRECT_ODBC_8.0_WIN_64_INSTALL.exe
5. Follow the prompts to complete the installation.
6. Start the ODBC Administrator by selecting its icon from the Progress DataDirect for ODBC program group.
7. On the ODBC Administrator, select the User DSN tab, and then configure a data source. If you want to configure:
   • An existing data source, select the data source name and click Configure. The driver Setup dialog box appears.
   • A new data source, click Add to display a list of installed drivers. Select the Oracle Wire Protocol driver and click Finish. The driver setup dialog box appears.

     
     

8. Specify values for the following connection options to authenticate using the UserID/Password authentication; then, click Test Connect.
   • Host: Type either the name or the IP address of the server to which you want to connect.
   • Port Number: Type the port number of your Oracle listener. Check with your database administrator for the correct number.
   • SID: Type the Oracle System Identifier that refers to the instance of Oracle running on the server. This option and the Service Name option are mutually exclusive. If the Service Name option is specified, do not specify this option.
   • Service Name: Type the Oracle service name that specifies the database used for the connection. The service name is a string that is the global database name—a name that is comprised of the database name and domain name, for example: sales.us.acme.com. This option and the SID option are mutually exclusive. If the SID option is specified, do not specify this option.

   Note: If no values are specified for the SID, Service Name, and TNSNames options, the driver attempts to connect to the ORCL SID by default.

   Note: For demonstration purposes, this tutorial uses the default authentication method, user ID and password (1 - Encrypt Password), to authenticate to the server. However, you can use any of the authentication methods supported by the driver. For more information, refer to the user's guide for the driver.

9. The Logon dialog box appears. Specify the user name and password; then, click OK.

   

10. If the test was successful, the window displays a confirmation message.
11. Click OK to close the setup dialog. The values you have specified are saved and are the defaults used when you connect to the data source. You can change these defaults by using the setup dialog to modify your data source, or you can override these defaults by connecting to the data source using a connection string with alternate values.
12. Connect to your server and begin accessing data with your applications, BI tools, database tools, and more. Proceed to the next section to learn how to access Oracle data from Excel.

The following steps show how to use the driver to connect to Oracle from Excel and import its data.

1. Open your workbook in Excel.
2. From the Data menu, select Get Data>From Other Sources>From ODBC.
3. The From ODBC dialog appears. Select your data source from the Data Source Name (DSN) drop-down list; then, click OK.

   
   

4. You are prompted for logon credentials for your data source. Select the Database tab; then, provide your user name and password. Optionally, specify a connection string in the provided field. Click Connect to proceed.

   
   

5. The Navigator window appears. 



From the list, select the tables you want to access. A preview of your data appears in the pane on the right. Optionally, click Edit to modify the results using the Query Editor. Refer to the Microsoft Excel product documentation for detailed information on using the Query Editor.

6. Load your data:
   1. Click Load to import your data into your worksheet. Skip to the end.
   2. Click Load>Load To to specify a location to import your data. Proceed to the next step.
7. The Import Data window appears. Select the desired view and insertion point for the data. Click OK.

   
   

Thank you for taking the time to consider the Progress DataDirect solution for connecting Excel to Oracle. Please contact us for additional information.