Learn how to connect an Excel workbook to an Oracle database using Power Query.
Step 1. Open your workbook in Microsoft Excel
Power Query (in the Retrieve and Transform group) is a feature of Excel that allows you to easily connect a workbook to an Oracle database.
If you have not already installed the Oracle client software on your computer, you must do so before continuing. You can download the latest 64-bit version here and 32-bit there
Step 2. Click the Data tab
It's at the top of the screen.
Step 3. Click Get
If you don't see this option, click New request in place.
Step 4. Click From Database
Step 5. Click From an Oracle Database
Step 6. Enter the name of your Oracle server
Type the name of your server in the Oracle Database field. This is the name or address of the server that hosts your database.
If a server ID (IDS) is required, you can specify it as follows: server name / IDS
Step 7. Specify a Native Database Query (optional)
If it is necessary to specify a particular query to import data from a database, expand the SQL Statement box by clicking the small triangle, and then typing the query.
Step 8. Click OK
Your options are then saved and the connection to the database is established.
Step 9. Connect to the database
If you are asked to connect to the database, identify yourself with your username and password, then click To log in. The workbook is then connected to the database.
- Depending on your settings, you can choose the appropriate authentication mode.
- If you specified a native database query, the results will appear in the Query Editor window.