I cannot connect Excel to Oracle although I have installed the driver. (using Excel's PowerQuery)

Andres Silva

I've been trying for a while now trying to connect Excel to Oracle via PowerPivot, unfortunately without success.

I am currently getting this error: An error happened while reading data from the provider. 'Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.'.

So far I have done the following:

1. Installed Oracle Client x64

I installed Oracle Client x64 from https://www.oracle.com/database/technologies/112010-win64soft.html . I have used file . During installation, I selectedwin64_11gR2_client.zipInstantClient

2. Installed Oracle's ODBC Instant Client x64

I went to https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html and downloaded instantclient-basic-windows.x64-19.6.0.0.0dbru.zipand instantclient-odbc-windows.x64-19.6.0.0.0dbru.zip. I "installed" them by extracting them into the same folder and running odbc_install.

Then, add the directory to the path environment variable, then create a new directory called TNS_ADMIN and add the same.

3. Installed Oracle's ODBC Instant Client x32

Ultimately, I did the same with the x32 files, but didn't add that directory to my environment variables.

I verified the correct installation by finding the driver here:

x64 driver:

enter image description here

x32 driver:

x32 driver

Troubleshooting Help

It is frustrating that besides the error message I can't find any way to troubleshoot this issue. Any ideas on what could it be? Any ideas on how to troubleshoot?

Thanks!

user9950041

With 32 bit Oracle Instant client

Option 1 with ODBC drivers

Option 2 without ODBC drivers and ODAC client

Option 1: After installing 32 bit odbc drivers try to add only 32 bit instant client to path or if you add both clients to system path make sure 32 bit client is above 64 bit client

enter image description here

If the order is set correctly in system path you can verify from command prompt with where command

C:\WINDOWS\system32>where sqlplus
D:\instantclient_19_6_x86\sqlplus.exe
D:\instantclient_19_6_x64\sqlplus.exe

or with commandpath

C:\WINDOWS\system32>path
PATH=D:\instantclient_19_6_x86;D:\instantclient_19_6_x64;xxxxx

If set correctly You can connect via odbcFile--->From Other Sources --> From ODBC

Option 2: Enables you to connect to database via ODAC Client without ODBC driversFile--->From Database --> From Oracle Database

Download ODAC 32-bit from Oracle ODP.NET_Managed_ODAC122cR1.zip .

Unzip the folder and elevated command promptinstall from

D:\ODP.NET_Managed_ODAC122cR1>install_odpm.bat D:\instantclient_19_6_x86 x86 true

Here, I set the target directory to the 32-bit instant client, i.e.D:\instantclient_19_6_x86

EDIT: Added tns name entries in tnsnames.ora and tns_admin system variables i,eTNS_ADMIN= D:\instantclient_19_6_x86\network\admin

Related

How to connect Excel to Oracle using PowerQuery

Andres Silva I am trying to connect Excel to a company's Oracle database. It should be fairly simple according to the instructions here : https://www.wikihow.com/Connect-Excel-to-an-Oracle-Database . However, so far this has been impossible and quite frustrati

How to connect Excel to Oracle using PowerQuery

Andres Silva I am trying to connect Excel to a company's Oracle database. It should be fairly simple according to the instructions here : https://www.wikihow.com/Connect-Excel-to-an-Oracle-Database . However, so far this has been impossible and quite frustrati

How to connect Excel to Oracle using PowerQuery

Andres Silva I am trying to connect Excel to a company's Oracle database. It should be fairly simple according to the instructions here : https://www.wikihow.com/Connect-Excel-to-an-Oracle-Database . However, so far this has been impossible and quite frustrati

How to connect Excel to Oracle using PowerQuery

Andres Silva I am trying to connect Excel to a company's Oracle database. It should be fairly simple according to the instructions here : https://www.wikihow.com/Connect-Excel-to-an-Oracle-Database . However, so far this has been impossible and quite frustrati

How to connect Excel to Oracle using PowerQuery

Andres Silva I am trying to connect Excel to a company's Oracle database. It should be fairly simple according to the instructions here : https://www.wikihow.com/Connect-Excel-to-an-Oracle-Database . However, so far this has been impossible and quite frustrati

I have been using comprehension and excel formulas

username I have been using the following formula to compare strings and display all matches. It works perfectly, but I'm trying to improve my overall understanding. =IF(ISNA(VLOOKUP($B8,N$1:N$1048576,1,0)),"",1) As far as I can tell, this will find all the st

I have been using comprehension and excel formulas

username I have been using the following formula to compare strings and display all matches. It works perfectly, but I'm trying to improve my overall understanding. =IF(ISNA(VLOOKUP($B8,N$1:N$1048576,1,0)),"",1) As far as I can tell, this will find all the st

Can't use docker-compose although I have it installed

Jerome I installed Docker-machine and Docker-compose in my container via Dockerfile: RUN curl -L https://github.com/docker/machine/releases/download/v0.9.0/docker-machine-`uname -s`-`uname -m` >/usr/local/bin/docker-machine && \ chmod +x /usr/local/bin/docker-