Connect Excel to Oracle Database

In this oracle tutorial, we will learn how to connect excel to oracle database.

Connect Excel to Oracle Database

In this section, we will learn how to connect Excel to Oracle Database without ODBC.

• Microsoft Excel has a built-in feature for connecting to an Oracle database, and we will show you how to use it.
• Once the connection is established, you can access all the files in that user’s Oracle database.
• In our case, c##sqlserverguides is the name of our common username. Please note that it is mandatory to use c## as a suffix when creating a user name.
• There are three things required to connect Excel with the Oracle database.
  • Server or SSID
  • Username
  • Password
• The default SSID is “orcl,” which is also the Global Database name. If you have changed it or created a new one, please mention that one.

Approach-1: Without ODBC

To connect Excel to Oracle Database, follow the below steps.

1. Open Microsoft Excel and click on the Data tab. From there, click on the From Database dropdown and select ‘From Oracle Database‘.

2. If you encounter this error, simply click the OK button. The error won’t create a problem when connecting Excel with the Oracle database.

3. Enter the server’s name or SSID here. The SSID is the name of the global database created at the time of installation. In our case, it is ‘orcl’. Click on the OK button to proceed.

4. Excel is connected to the Oracle database now you can browse all the Oracle data on Microsoft Excel.

Read How to Check Oracle Database Version

In this section, we will learn how to connect Excel to the Oracle database without ODBC on the Windows operating system.

• Most connections between Microsoft Excel and Oracle Database are established using ODBC.
• This type of connection was established in previous versions of Microsoft Excel when there was no option to connect Oracle directly with Excel.
• In new versions, there is already an option to connect with the Oracle database, as you can see in the image below.

• If this option is unavailable, then you can connect with the database using OLED.
• To see a practical implementation of Excel connecting to an Oracle database with OLED, visit the first section of this tutorial, ” Connect Excel to an Oracle Database.”

Approach-2: With ODBC

In this section, we will learn how to connect Excel to the Oracle database on the Windows operating system.

• Offline-installed Excel software can only be connected to the Oracle database. Web-based Excel does not offer the option to connect with the database.
• The first step in the process is to create a new data source in the Windows Administrative Tools section of the operating system.

• Inside the “Windows Administrative Tool,” click on ODBC Data Sources (64-bit). From the prompt that appears, select the “User DSN” tab and click on the Add button.
• Another prompt will appear “Create New Data Source” in that prompt select “Oracle in OraDB19Home1“. This name may change in the future, but it will always start with the key ‘oracle’. Click on the finish button.

• Once you click the finish button in the previous option, you will see “Oracle Database Driver Configuration” window.
• Data Source Name: The name provided here will be reflected in Excel, and you must choose it there.
• Description: If you want to add a brief description of your database, you can write it here. The description can also be treated as instructions for other database users.
• TNS Service Name: ‘orcl’ is the global database configured while installing the Oracle database software. If you have changed it or created a new one, mention that here.
• User ID: It is optional to provide a user ID. You can mention the common username here.
• Click on the “Test Connection” button, provide the username and password, and click on the “OK” button. If a prompt appears saying “Test Successful,” that means you can move to the next step now. Click on the “OK” button in the “Oracle ODBC Driver Configuration” window.

• Open Microsoft Excel and click on the Data tab. From there, click on the Get Data dropdown and select ‘From Other Sources‘. Then click on the option “From ODBC.”

• Select the Data Source Name that we created in Oracle Odbc Driver Configuration.
• Once done, it will ask for the username and password. Enter the credentials, and you will see that a screen with a Navigation title will appear. Now, you can browse your data there. This is how we can connect Excel with the Oracle database.

Read How to Get List all Tables in Oracle Database

Approach-3: Using OLED

In this section, we will learn how Excel connects to an Oracle database using OLEDB in the Microsoft Windows operating system.

• Open Microsoft Excel and click on the Data tab. From there, click on the Get Data dropdown. Now click on ‘From Other Sources‘ and select From OLEDB.

• In the connection string prompt, enter the code below. In our case, ‘orcl’ is the name of our global database, so we have set source=orcl.

provider=OraOLEDB.Oracle.1;data source=orcl

• After entering the above code, click on the build button. A new prompt will appear: “Data Link Properties.” Enter the user name and password for the user you want to connect with. In our case, we are connecting with “c##sqlserverguides.”
• Once you have filled out all the required information (username and password), click on the “Test connection” button. If the Test connection succeeds, click on “OK” for all three screens.

• New prompt will appear immediately after clicking on ok button of “From OLE
  DB” window.
• Enter your username and password one last time and click on connect. It may take a few seconds, but then Excel will be connected to the Oracle database.

• Below is the window that will appear after the connection is successfully established. You will be able to access all the files, folders, and tables available inside the connected user.

Read: Oracle get database name

Excel Connection String to Oracle Database

In this section, we will learn how to establish an Excel connection string to an Oracle database.

• While connecting Excel to an Oracle database, you must have noticed the ‘Credential Connection String’ box, which might be optional sometimes.
• Excel Connection String holds all the information about the connecting database, such as the host, hostname, port, IP address, server type, and service name.
• We have to provide an Excel connection string when connecting Excel to an Oracle database using OLED. You can use the Excel connection string below to connect to an Oracle database.

provider=OraOLEDB.Oracle.1;data source=orcl

• You can configure the setting using the code below if you are an advanced user and want to connect via Oracle database on a server.

provider=OraOLEDB.Oracle.1;data source=
             ("
              DESCRIPTION = 
                 (ADDRESS = 
                   (PROTOCOL = TCP)
                   (HOST = localhost)
                   (PORT = 1521)
                 ) 
              (CONNECT_DATA = 
                  (SERVER = DEDICATED) 
                  (SERVICE_NAME = orcl))
              )"

Connect Excel to Oracle Database 12c

In this section, we will learn how to connect Excel to Oracle database 12c on the Windows operating system.

• Oracle has released various versions so far. Of these, 12c and 11g were the most popular and widely used versions.
• Current stable long term version is Oracle database 19c, and 21c is in innovation mode.
• Though 12c has been obsolete, many organisations are still using it.
• The steps to connect Excel to Oracle Database 12c are similar to those for Oracle Database 19c. So, you can follow the section “Connect Excel to Oracle Database with ODBC” of this tutorial.

Read: How to create table in Oracle

Can you link excel to Oracle Database

This section will answer the question “Can you link Excel to the Oracle database?“.

• Yes, we can link Excel to the Oracle database, and there are multiple ways to do that.
• In this tutorial, we have explained three significant ways to connect Excel to an Oracle database.
  • Connect excel to Oracle database
  • Excel Connect to Oracle Database with ODBC
  • Excel Connect to Oracle Database using OLED
• Visit each section to learn more about how to connect Excel with the Oracle database.

This tutorial taught us how to connect Excel to Oracle database.