0

I am currently using

  • Office 2016 32-bit
  • SQL Server 2016

Steps taken:

  1. Installed Microsoft.ACE.OLEDB.12.0 provided in a post

  2. Ran command

    SP_CONFIGURE 'show advanced options', 1; 

RECONFIGURE;
GO  
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1; 

RECONFIGURE; 
GO

  3. Now I run this command:

    SELECT * 
INTO #InertTable 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0;Database=C:\Users\Samra\Documents\..\Supervisor Grandfathering.xls',  
                'SELECT * FROM [sheet1$]')

But I get an error

The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

I also tried:

  1. right-clicking database -> import
  2. Chose Microsoft Excel from Datasource
  3. specified file name Excel
  4. version -> Microsoft 2016

Now it says Microsoft.ACE.OLEDB.16.0 provider is not registered on local machine

I also went to https://www.microsoft.com/en-us/download/confirmation.aspx?id=54920 and downloaded it, and now when I run 64-bit version to install it says

You cannot install the 64-bit version of Microsoft Access Database Engine 2016 because you currently have 32-bit Office products installed. If you want to install 64-bit Microsoft Access Database Engine 2016, you will first need to remove the 32-bit installation of Office products....

For which i have to ask my admin and set it up later. Isn't there an easy way around all this? I just need to import data from an excel into a table

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Samra
  • 1,815
  • 4
  • 35
  • 71
  • If your file size is small, use online excel to SQL converter... [Sqlizer](https://sqlizer.io/#/) – Shakeer Mirza Jun 01 '18 at 04:38
  • Well after all this hassle we logged into the server where all configurations were correct instead of using my local machine. Now the task is done, but a solution for my local machine is only to uninstall 32-bit office and use 64-bit i guess? – Samra Jun 01 '18 at 04:40
  • Refer https://stackoverflow.com/questions/29567548/microsoft-ace-oledb-12-0-cannot-be-loaded-in-process-on-a-64-bit-sql-server – Krunal Shah Jun 01 '18 at 09:32

0 Answers0