This article explains how you can link an Excel workbook to an Oracle database using the tools on the Power Query tab.
Steps
Step 1. Open the file to edit using Microsoft Excel
Excel comes with a set of tools called "Power Query" (or "Get & Transform") that allows you to connect to an external data source, such as an Oracle database, quickly and easily.
If you have not installed an Oracle client on your computer, you will need to do so now before continuing. You can download the latest 64-bit version from this link. Alternatively you can download the 32-bit version from this link
Step 2. Click on the Data tab
It is located at the top of the Excel window.
Step 3. Click on the Recover Data button
If the indicated option is not visible, click on the option New query.
Step 4. Click on the From database entry
Step 5. Click on the From Oracle database option
Step 6. Enter the name of the server where the Oracle database is installed in the "Server Name" text field
This is the domain name or IP address of the server where the Oracle database to use is stored.
If the database requires the SID to connect, enter the required information in the following format: server_name / SID
Step 7. Enter a native query for the database (optional)
If you need to import data from the database using a specific query, expand the "SQL Statement" section by clicking on the small triangle on the left, then type the query to use.
Step 8. Click the OK button
In this way the entered settings will be saved and a connection to the indicated database will be established.
Step 9. Log in to the database
If the database is configured to require user authentication, log in providing username and password, then click on the button Connect. This will connect the Excel document to the Oracle database.
- Depending on the settings you have chosen, you may need to select an authentication method.
- If you have specified to use a native query, the connection result will be displayed in the query editor window.
