1

I have been trying for a while now, to connect Excel to Oracle through PowerPivot, but unfortunately to no 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.'.

I have so far done the following:

1. Installed Oracle Client x64

I installed Oracle Client x64 from https://www.oracle.com/database/technologies/112010-win64soft.html. I used file win64_11gR2_client.zip. In the installation proccess I went for the option InstantClient

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 both instantclient-basic-windows.x64-19.6.0.0.0dbru.zip and instantclient-odbc-windows.x64-19.6.0.0.0dbru.zip. I 'installed' them by unziping them in the same folder, and running odbc_install.

Then, I added the directory to my path environment variables and then created a new one called TNS_ADMIN and added the same directory.

3. Installed Oracle's ODBC Instant Client x32

Finally I did the same for the x32 files, but I did not add that directory to my environment variables.

I validated the correct installation by finding the drivers here:

x64 Drivers:

enter image description here

x32 Drivers:

x32 drivers

Help in Troubleshooting

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!

Andres Silva
  • 874
  • 1
  • 7
  • 26
  • 1
    Are you running 64-bit Excel? – Tim Williams Jul 03 '20 at 23:20
  • 1
    The bit architecture (32-bit vs. 64-bit) of the odbc drivers AND the oracle client must match that of your calling application - in this case, Excel. Beware that even on a lot of 64-bit windows, the MS Office suite is still 32-bit. (When _will_ MS finally drive a stake in the heart of 32-bit apps?). Also, if you've installed both 32-bit and 64-bit, make sure the one you want is FIRST in the system PATH. – EdStevens Jul 04 '20 at 01:00
  • 1
    Installing more than one Oracle client (i.e. one each for 32-bit and 64-bit) is usually a bad idea. Remove **all** of them (see https://stackoverflow.com/questions/8450726/how-to-uninstall-completely-remove-oracle-11g-client) and install one client 32-bit or 64-bit according to your Excel bit size. In case you need to install both, 32-bit and 64-bit follow this instruction: https://stackoverflow.com/questions/24104210/badimageformatexception-this-will-occur-when-running-in-64-bit-mode-with-the-32#24120100 – Wernfried Domscheit Jul 04 '20 at 09:26
  • @EdStevens, MS Office is also available at 64-bit. The user has to check which version is installed on his machine. You can install both 32-bit and 64-bit without recurrently modifying your PATH, see my comment above. – Wernfried Domscheit Jul 04 '20 at 09:28
  • @WernfriedDomscheit - thanks for the links. Just one point of clarification - I never said that Office was not available in 64-bit. Only that a lot of places still install 32-bit in spite of having a 64-bit OS. Seeing as how 32-bit hardware is now museum pieces, I think it long past time for the termination of 32-bit software - OS _and_ applications. – EdStevens Jul 04 '20 at 14:07

1 Answers1

0

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 command path

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

If set correctly You can connect via odbc File--->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 from elevated command prompt install

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

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

Edit: Add tns name entry in tnsnames.ora and tns_admin system variable i,e TNS_ADMIN= D:\instantclient_19_6_x86\network\admin

  • "ODBC" is not "ODP.NET Managed Driver" I don't think Excel can use the ODP.NET Managed Driver, so it is useless in this case. Apart from that ODP.NET Managed Driver does not require any further client installation, i.e. Oracle Instant Client. – Wernfried Domscheit Jul 04 '20 at 09:25
  • Not sure where did I mention "ODBC" is "ODP.NET Managed Driver"?Excel has two options for Oracle database connectivity via ODBC driver and ODAC client please [refer](https://support.microsoft.com/en-us/office/import-data-from-external-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a?redirectSourcePath=%252farticle%252fd7fbd231-a705-4eb7-83b3-a66bfb678395&ui=en-US&rs=en-US&ad=US) to that link –  Jul 05 '20 at 21:40
  • ODAC means"Oracle Data Access Components" which is just a general term for various Oracle drivers. You explained how to install the ODP.NET Managed Driver however, this you cannot use in Excel, so your answer is rather confusing. – Wernfried Domscheit Jul 06 '20 at 06:19
  • The link provided by Microsoft is [ODTwithODAC](https://www.oracle.com/database/technologies/dotnet-utilsoft-downloads.html) and guess what it installs ODP.NET and bloated Oracle instant client instead of that I'm installing ODP.NET managed driver on existing instant client.I tested all scenarios bottom line whether it's ODBC or ODAC Excel still needs instant client(even it works with 64 bit instant client with ODAC). –  Jul 06 '20 at 06:41
  • I guess you mix the ODP.NET (unmanaged driver) and the ODP.NET Managed Driver. The ODP.NET **Managed Driver** is a stand-alone DLL and does not contain or rely on any further Oracle Client installation, you cannot use it in Excel (at least so far I have never seen such a working example). This corresponds to your statement "Excel still needs instant client". Maybe have a look at [this list](https://stackoverflow.com/questions/34803106/how-to-connect-to-oracle-11-database-from-net/34805999#34805999), to see what I mean. – Wernfried Domscheit Jul 06 '20 at 07:22
  • That's true for .net applications that does not rely on instant client with ODP.NET Managed driver but again Excel is not .net application and it does not function without instant client(blame it on Microsoft). From the list you provided you're assuming about programming and here I thought we are discussing about connecting from Excel(from menu).What's your proposed solution If I wanted to [connect](https://prnt.sc/tckk7w) to Oracle Database –  Jul 06 '20 at 08:06
  • Correct solution would be: download the ODAC (i.e. not the ODP.NET Managed) and install it (i.e. `install.bat odp.net4` or `install.bat odp.net2` - don't know which one is used by PowerQuery rather than `install_odpm.bat`) – Wernfried Domscheit Jul 06 '20 at 10:11
  • Thanks everyone! You were right.. My Excel was 32bits instead of 64 (to verify yours check [this instructions](https://plumsolutions.com.au/32-bit-vs-64-bit-excel/#:~:text=Select%20the%20File%20tab%20from,top%20line%20of%20the%20window.&text=Select%20the%20File%20tab%20from%20the%20Excel%20ribbon%2C%20and%20then,appear%20under%20About%20Microsoft%20Excel.)). So I followed your advice and 1st) installed Oracle Client x32, and 2nd) added Oracle's ODBC Instant Client x32's directory to the `path` environment variable and moved it to the top. – Andres Silva Jul 07 '20 at 14:33