In this post, you will learn to connect to Oracle database from Excel 2016 using OLE DB.
How to Connect to Oracle from Excel?
1. Connect to Oracle Database using OLE DB in Excel. Click on Data tab then click on Get Data Menu > From Other Sources > From OLEDB. As shown in below image:
2. A connection string window will appear. Then provide the following connection string into it. Change the {orcl} to your Oracle Database SID. Then click on OK.
provider=OraOLEDB.Oracle.1;data source=orcl
Or as below, change the bold values with your connection values.
provider=OraOLEDB.Oracle.1;data source="(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))"
3. A credential window will appear. Provide the username and password and click on connect button. As shown in below image.
4. After that, you will connect to Oracle database and Navigator window will appear, which will show you all the schemas you have access to it.
Still can not connect to Oracle from Excel?
If you are still not able to connect to Oracle Database from Excel by providing connection string mentioned above. Then you can try the manual process to build the connection string. Follow these steps.
1. Click on Build button on Connection string window.
2. Then Data Link Properties window will open, as shown below. Select Oracle Provider for OLE DB and click on next.
3. On the connection tab, provide the data source name (Oracle SID) and username and password and click on OK. A connection string will build and will display at connection string window. Now click on OK to continue.
Note: You must have Oracle client installed on your system and Tnsnames.ora should have connection strings configured.
I can connect Excel with Oracle database, But please help me to know how can I manipulate oracle database data from excel. Like I want to insert data from excel to oracle db when I save the excel file.