2

The Problem:

I have been trying to get a simple app/stored procedure/sql query/anything to pull in data from excel spreadsheets to MS SQL Server 2012 on my 64-bit Windows 7 machine.

My IT department has installed Microsoft Office 2010, which is incompatible with the Microsoft Access OLEDB driver (called Microsoft.Ace.OLEDB.12.0), which I've installed independently.

When attempting to install the 64 bit OLEDB driver I get the following error from the installer:

img

My attempt to pull in data through the existing 32-bit driver using SQL OPENROWSET queries:

 SELECT * FROM OPENROWSET('Provider=Microsoft.ACE.OLEDB.12.0', 
   'Extended Properties=Excel 12.0;
   Data Source=c:\path\to\excel\spreadsheet.xlsx',
   [Sheet1$]);

or

sp_configure 'Ad Hoc Distributed Queries',1
go
reconfigure with override
go SELECT * FROM OPENROWSET ('Microsoft.Ace.OLEDB.12.0','Excel 12.0; Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=c:\path\to\excel\spreadsheet.xlsx; Extended Properties=''EXCEL 12.0;HDR=NO;IMEX=1','SELECT * FROM [Sheet1$]') AS exl

result in the following error:

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

OR: 

 The OLE DB provider "Provider=Microsoft.ACE.OLEDB.12.0" has not been registered.

QUESTION:

  1. What other drivers out there exist (if any) for me to use in lieu of the Microsoft.Ace.OLEDB.12.0 driver that are also compatible with 32-bit Office install?

  2. (If there is no answer to no. 1) Can anyone tell me for sure whether uninstalling 32-bit office will allow me to install and use the 64 bit driver? (Want to make sure I'm not losing MS Office & my time).

Thanks all!

Dbloch
  • 2,326
  • 1
  • 13
  • 15
Rachael
  • 1,965
  • 4
  • 29
  • 55
  • Not sure if you know C#, but EPPlus is what I use to push data into SQL from Excel. – Dbloch Mar 24 '15 at 16:53
  • I do, but trying to keep it to sql or c++ at most complexity. – Rachael Mar 24 '15 at 17:02
  • 1
    See if this helps you, about halfway down the page. http://sqlwithmanoj.com/tag/microsoft-ace-oledb-12-0/ – Dbloch Mar 24 '15 at 18:14
  • @Dbloch, that states the user must uninstall office 32 bit and reinstall 64 bit. That's what I'm going to do. Thanks :) That was the most informative site i've read thus far. – Rachael Mar 24 '15 at 19:16

1 Answers1

0

See if this helps you, about halfway down the page.

http://sqlwithmanoj.com/tag/microsoft-ace-oledb-12-0/

Dbloch
  • 2,326
  • 1
  • 13
  • 15
  • @Dbloch--I just removed this as the answer because after uninstalling all 32 bit office remnants, I was still unable to get the 64 bit driver working. Still unsure why, still trying. – Rachael Apr 01 '15 at 15:59
  • 1
    Look are both of these links http://stackoverflow.com/questions/7116019/hand-install-of-64-bit-ms-access-odbc-drivers-when-32-bit-office-is-present http://knowledge.autodesk.com/support/autocad-civil-3d/troubleshooting/caas/sfdcarticles/sfdcarticles/How-to-install-64-bit-Microsoft-Database-Drivers-alongside-32-bit-Microsoft-Office.html As I have run across this same problem about 6 months ago and it was painful to fix. – Dbloch Apr 01 '15 at 16:09
  • 1
    Link only answers are frowned upon, please include the relevant bits in the answer. – Maslow Mar 29 '18 at 13:09