Connecting Excel to Oracle Using ODBC Drivers

• It is possible to use Excel to access data/information from an Oracle database using an ODBC connector.
• ODBC drivers facilitate the importation of data directly into an Excel spreadsheet.
• If Excel is running in 64-bit, a user must install a 64-bit ODBC driver; this is because the bits for Microsoft Excel and ODBC Driver must match for there to be a connection between the two. Here are the steps of installing the ODBC Driver for Oracle.

Connecting Excel to Oracle From Various Data Sources Using ODBC Drivers

• The assumption here is that the user has already installed an ODBC driver for Oracle, which should be 64-bit in this case. Below are the steps to use when connecting Excel (64-bit) to Oracle from various data sources.

Connecting Excel to Oracle with Power Query

• Click on "Data in Excel" > expand the "Get Data" drop-down list > select "From Other Sources" > select "From ODBC".
• At the "From ODBC" dialog, choose the data source name (DSN). If this hasn't been configured for the ODBC driver yet, expand the "Advanced Options" dialog box and enter the connection string for the data source (without credentials, which are defined in the credential's dialog box in the next step).
• Additionally, the user can enter an SQL statement that will be executed right after establishing a connection to the data source. After this action, click "OK."
• When using a database username or password, the user should select "Database" and enter the credentials in the dialog box, then click Connect. However, if the database is not password-protected or there are already specified credentials in the ODBC data source settings, then select "Default" or "Custom" and press "Connect".
• In the window that appears, the user should select the table to retrieve data from and click "Load".
• After the above steps, the data from the table will be displayed in an Excel spreadsheet where the user can further work with it.

Connecting Excel to Oracle with Legacy Wizard

• In Excel, the user should go to the "Data tab" > "From Other Sources" > "From Data Connection Wizard".
• In the opened dialog box, select "ODBC DSN" and click "Next" to continue.
• Then, select a "data source" to connect to, and click "Next".
• To connect to the table containing the required data, select its name and click "Next" to enter and save information about the new file or click "Finish".
• In the "Import data dialog", the user can select the way the data will be viewed in Excel and the place where to put it in the worksheet, and click OK.
• After the above steps, the required data will now be displayed in the existing Excel worksheet.

Connecting Excel to Oracle with Query Wizard

• First, the user should "Open Excel", in the main menu, then click the "Data tab".
• Next, click the "From Other Sources" dropdown menu and then click "From Microsoft Query".
• In the appeared dialog, choose the data source to connect to.
• After a successful connection, the user can select the data to be displayed in Excel and click "Next".
• The next two steps allow filtering and sorting the data. Therefore, the user should click "Next" to skip these procedures.
• To use the query further, it can then be saved by clicking the "Save" button on the right.
• After saving, select "Return Data To Microsoft Excel" and click "Finish".
• In the Import Data dialog, the user can select the way the data will be viewed in Excel and the place where to put it in the worksheet, and click OK.
• After the above steps, the required data will be imported successfully to Excel.

Connecting Excel to Oracle with Microsoft Query

• Here, the user should first "Open Excel", then click the "Data tab".
• In the appeared ribbon, click "From Other Sources", and then "From Microsoft Query".
• In the next dialog box, choose the data source to connect to (e.g., using data source name - Devart ODBC Oracle). Next, uncheck "Use the Query Wizard" to "Create/Edit Queries" and click OK.
• Next, select the tables to add to the query, and after finishing, click the "Add" button.
• In the graphical editor, the user can filter rows or columns of data, sort data, join multiple tables, and create a parameter query, among other actions.

Connecting Excel to Oracle with PowerPivot

• In Excel, the user should click the "PowerPivot tab", then click "Manage" to go to the "PowerPivot window".
• In the opened window, click "From Other Sources".
• When the "Table Import Wizard" opens, select "Others (OLEDB/ODBC)" and click "Next".
• In the "Specify a Connection String" window, click the "Build" button.
• In the "Data Link Properties" dialog, the user must specify the data source to connect to (e.g., using data source name - Devart ODBC Oracle), and then click "Next".
• Next, choose how to import the data (either by selecting a table from the list or write a query to specify the data to be imported).
• When the import operation has succeeded, click the "Close" button. The retrieved data is usually inserted in the active worksheet.

Research Strategy