Connect Excel to Oracle Database

In this oracle tutorial, we will learn how to connect excel to an oracle database. also, we will cover these topics.

• Connect Excel to Oracle Database
• Connect Excel to Oracle Database 12c
• Excel Connect to Oracle Database without ODBC
• Excel Connect to Oracle Database with ODBC
• Excel Connect to Oracle Database using OLED
• Excel Connection String to Oracle Database
• Can you link 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 built-in feature of connecting excel to oracle database and we show show you how to use it.
• Once the connection is established, you will be able to access all the files in the oracle database of that common particular user.
• In our case, c##sqlserverguides is the name of our common user name. Please note that it is mandatory to c## as suffix while creating a user name.
• There are three things required to connect excel with the oracle database.
  • Server or SSID
  • Username
  • Password
• Default ssid is “orcl” which is also the Global Database name. In case you have changed it or created a new one then please mention that one.
• Open Microsoft Excel and click on the Data tab, from there click on the the From Database dropdown and select ‘From Oracle Database‘.

• If you encounter this error then simply click on the on the OK button. The error won’t create problem while connecting excel with the Oracle database.

• Enter the name of server or ssid here. 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.

• Excel is connected to oracle database now you can browse of all the oracle data on the microsoft excel.

Read How to Check Oracle Database Version

Connect Excel to Oracle Database 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 won’t give option to connect with the database.
• First step in the process is we have to create a new data source on “Windows Administrative Tools” on windows operating system.

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

• Once you clicked on the finish button in the previous option, you will see “Oracle Database Driver Configuration” window.
• Data Source Name: Name provided here will reflect in excel and you have to choose it over there.
• Description: If you want to add a little description about your database you can write it here. Description can also be treated as instruction for other users of the database.
• TNS Service Name: ‘orcl’ is the global database configured while installing the Oracle databse software. Incase you have changed it or created a new one then mention that here.
• User ID: It is optional to provide user id. You can mention the common username here.
• Click on the “Test Connection” button and provide the username and password and click on the ok button. If a prompt appear “Test Successful” that means you can move to next step now. Click on the Ok button on the “Oracle ODBC Driver Configuration” window.

• Open Microsoft Excel and click on the Data tab, from there click on the the Get Data dropdown and select ‘From Other Sources‘, 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 credential and you will see that a screen with Navigation title will appear and now you can browse your data there. This is how we can connct excel with oracle database.

Read How to Get List all Tables in Oracle Database

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 out of these 12c and 11g were 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 still many organisations are still using it.
• Steps to connect excel to oracle database 12c is similar to oracle database 19c. So you can follow section section “Connect Excel to Oracle Database with ODBC” of this tutorial.

Read: How to create table in Oracle

Excel Connect to Oracle Database without ODBC

In this section, we will learn how to connect excel connect to the oracle database without ODBC on the windows operating system.

• Most of the connection between microsoft excel and oracle database are established using ODBC.
• This type of connections are established in previous versions of microsoft excel when there was no option for from connecting oracle directly with excel.
• In new versions there is already option to connect with oracle database as you can see in the below image.

• In case this option is not available then using oled you can connect with the database.
• to see practical implementation of excel connect to oracle database with oled visit the first section of this tutorial ” Connect Excel to Oracle Database”.

Also, check: How to Fetch Data from Oracle Database in Python

Excel Connect to Oracle Database using OLED

In this section, we will learn how excel connects to an oracle database using OLED in the Microsoft Windows operating system.

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

• In the connection string prompt enter the below code. 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 promot 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 filled all the required information (username & password) click on “Test connection” button. If Test connection succeeded click on ok for all the three screens.

• New prompt will appear immedietly after clicking on ok button of “From OLE
  DB” window.
• Enter username and password one last time and click on connect. It may take few seconds then excel will be connected to oracle database.

• Below is the window that will appear on the successful establishment of the connection. You will be able to access all the files, folder 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 oracle database you must have noticed ‘Credential Connection String’ box that might be optional in some cases.
• Excel Connection String holds all the information about the connecting database like host, hostname, port, IP address, server type and service name.
• while connecting excel to oracle database using oled we have to provide excel connection string that you can us the below excel connection string to connect with oracle databse.

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

• If you are advance user and want to connect via oracle database on server than you can configure the setting using the below code.

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

Can you link excel to Oracle Database

In this section, we will answer the question “can you link excel to the oracle database“.

• Yes, we can link excel to oracle database and there are multiple ways of doing that.
• In this tutorial, we have explained 3 mojor ways that are widely used to connect excel to 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 oracle database.

In this tutorial, we have learned how to connect excel to an oracle database. Also, we have covered these topics.

• Connect Excel to Oracle Database
• Connect Excel to Oracle Database 12c
• Excel Connect to Oracle Database without ODBC
• Excel Connect to Oracle Database using OLED
• Excel Connection String to Oracle Database
• Can you link excel to Oracle Database