none
Getting Error "The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered". when importing excel file.

    Question


  • Hi,

    I am using below sql command to import the excel file (created in excel 2007). 
     
    SELECt * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\File1.xlsx', 'SELECT * FROM [Sheet1$]');

    This command is working fine on the machine which have the office 2007. But, I am getting following error message when I run this command on the server on which the office 2007 is not installed:

    Msg 7403, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

    Is there is need to install the office 2007 on the server. or there is any alternative for this.


    Thanks.
    Monday, November 10, 2008 7:38 AM

Answers


  • You can download and install only the ACE provider from the following link, this supports office 2007 files both xls and xlsx file.

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en




    Monday, November 10, 2008 9:48 AM

All replies

  • You are using file extension .xlsx which requires office 2007 to open the file. use file with .xls extension. Also you need to change the connection string to use, to open in lower version of office
    Provider=Microsoft.Jet.OLEDB.4.0

    instead of

    Provider=Microsoft.ACE.OLEDB.12.0 - this is for office 2007.

    Refer this link for more info

    http://support.microsoft.com/kb/247412
    Monday, November 10, 2008 7:57 AM
  • Thanks for reply.

    The file extension can be both .xlsx or .xls. so I need to provide the functionality for importing both type of extension. 
    For importing .xlsx file is there need to install Office2007 or is there any alternative?
    Monday, November 10, 2008 8:32 AM

  • You can download and install only the ACE provider from the following link, this supports office 2007 files both xls and xlsx file.

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en




    Monday, November 10, 2008 9:48 AM
  •  

    I downloaded and installed the ACE providers from the link above on my server -  It shows up as being installed in the Add Remove Programs list, but it does not show up in the ODBC Data Source Administrator list or in the SSMS Server > Server Objects > Linked Servers > Providers folder.

     

    And when I run this query:

     

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;Database=c:\test_office7.xlsx', 'SELECT * FROM [Sheet1$]');

     

    I still get this result:

     

    Msg 7403, Level 16, State 1, Line 1

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

     

    I am running Server 2003 Enterprise x64 SP2 and SQL Server 2005 64bit.  Does 64bit have anything to do with it? 

     

    I know there is no JET 64 bit driver - is it the same situation with this ACE / Office 7 driver?

    Monday, November 24, 2008 5:38 PM

  • Microsoft hasn't nor do they have any immediate plans to release a x64 version of ACE; If you're trying to write an application targeted for an x64 platform you'll have to come up with another solution, otherwise simply set your platform target to x86 in the build properties of your project and you should be fine.

     MarkWB wrote:

     

    I downloaded and installed the ACE providers from the link above on my server -  It shows up as being installed in the Add Remove Programs list, but it does not show up in the ODBC Data Source Administrator list or in the SSMS Server > Server Objects > Linked Servers > Providers folder.

     

    And when I run this query:

     

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;Database=c:\test_office7.xlsx', 'SELECT * FROM [Sheet1$]');

     

    I still get this result:

     

    Msg 7403, Level 16, State 1, Line 1

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

     

    I am running Server 2003 Enterprise x64 SP2 and SQL Server 2005 64bit.  Does 64bit have anything to do with it? 

     

    I know there is no JET 64 bit driver - is it the same situation with this ACE / Office 7 driver?



    Tuesday, November 25, 2008 12:54 PM
  • When Excel Driver gets installed on a 64-bit server, the driver does not appear in the ODBC connection, so sql server will not be able to get a provider for excel. Then the openrowset/openquery for ACE/JET in sql server will return an error. It is unrealistic to buy another 32-bit server and install another sql just for loading excel files. I still don't understand why microsoft doesn't provide a 64-bit driver for excel.
    Friday, August 07, 2009 12:16 AM
  • It solved my probelm... thanks a ton.
    Wednesday, January 13, 2010 3:05 AM
  • If you have the same problem in SQL SERVER 2008 in x64 you can visit

    Cargar un Archivo de Excel en SQL Server 2008 x64 : Do click

    Juan Pablo Manrique

    http://letrasandnumeros.com/

    Saturday, March 27, 2010 6:24 PM
  • Rupesh, We are facing the same problem.We installed AccessDatabaseEngine but still no change.Could you please post how you fixed it?.

    Thanks

     

    Friday, July 30, 2010 3:16 PM
  • If you are using Vb.Net then goto the project properties, in that have option ANY CPU , select X86 from the list and run the program , it will solved your problem
    Thursday, February 24, 2011 5:36 AM
  • All well and good if you are without any need to support 32 bit.  Yet, I am a bit confused by this (reference to aaj23's comment that MS has no intention of supporting 32 bit [Office]).  If we load a 64 bit driver as the user suggested (http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en) and we attempt to connect a 64 bit accdb/xlsx - so, this would mean that 64 bit office is actually a 32 bit office (single thread apartment)?   Can anyone understand why this is confusing?  (subtle tongue in cheek)

    Additionally, having hit this 32 bit / 64 bit barrier, time and time again, and given the nature of the beast, I have had to learn "many" new methods (emphasis on the word "many") to cope with the situation and I am here now because I have just told a customer that his project of two years, has gone out of scope and I can no longer continue to work on the issue as he knows it - instead he must drag himself into the 21st Century. 

    Rupesh says "it solved my problem".  I wish I knew what "IT" is as that is actually the answer I am looking for.  One that solves the problem.

    Meanwhile, I have resorted to 1) dumping the files from SQL to text (CSV), 2) importing them (which is actually just the hard way - SSIS is the easy way but if you do it the hard way first and then save it, SQL writes the SSIS script for me the first time around -  see Kalman's solution above) and then 3) export the data back to Access using a job schedule from SQL (yep, Access not Excel - it is a bit faster).  Then 4) repeat the cycle.  (OKAY, sounds like a washing machine or a dish-washer doesn't it)   In my mind, what aaj23 has said is probably true... though I doubt you'll get an official comment that MS has dumped 32 bit support altogether.  It's there... but it is subtle.  You want a fix, become a BI programmer and use SSIS.  Or get a time machine and hurry on back to the 20th century. 

    Aaah, if only we had a time machine.  We could invest in the future we know is coming and then retire in it.  Then we could tell all the customers they were victims of scope creep and curl up in front of the TV and wait for the next customer to call so we could say the same thing to them.


    R, J
    Sunday, June 19, 2011 3:35 PM
  • All well and good if you are without any need to support 32 bit.  Yet, I am a bit confused by this (reference to aaj23's comment that MS has no intention of supporting 32 bit [Office]).  If we load a 64 bit driver as the user suggested (http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en) and we attempt to connect a 64 bit accdb/xlsx - so, this would mean that 64 bit office is actually a 32 bit office (single thread apartment)?   Can anyone understand why this is confusing?  (subtle tongue in cheek)

    Additionally, having hit this 32 bit / 64 bit barrier, time and time again, and given the nature of the beast, I have had to learn "many" new methods (emphasis on the word "many") to cope with the situation and I am here now because I have just told a customer that his project of two years, has gone out of scope and I can no longer continue to work on the issue as he knows it - instead he must drag himself into the 21st Century. 

    Rupesh says "it solved my problem".  I wish I knew what "IT" is as that is actually the answer I am looking for.  One that solves the problem.

    Meanwhile, I have resorted to 1) dumping the files from SQL to text (CSV), 2) importing them (which is actually just the hard way - SSIS is the easy way but if you do it the hard way first and then save it, SQL writes the SSIS script for me the first time around -  see Kalman's solution above) and then 3) export the data back to Access using a job schedule from SQL (yep, Access not Excel - it is a bit faster).  Then 4) repeat the cycle.  (OKAY, sounds like a washing machine or a dish-washer doesn't it)   In my mind, what aaj23 has said is probably true... though I doubt you'll get an official comment that MS has dumped 32 bit support altogether.  It's there... but it is subtle.  You want a fix, become a BI programmer and use SSIS.  Or get a time machine and hurry on back to the 20th century. 

    Aaah, if only we had a time machine.  We could invest in the future we know is coming and then retire in it.  Then we could tell all the customers they were victims of scope creep and curl up in front of the TV and wait for the next customer to call so we could say the same thing to them.


    R, J

    You misquoted me, I did not say that they would not support 32 bit office, I said they did not support a 64 bit version of ACE...big difference.  The ACE ODBC driver is 32 bit and as a result if you're trying to target 64 bit, you're going to run into issues.  My suggestion, was to target x86 instead of x64.  That said, in so doing, you will have to run your program in WoW64 when on an x64 version of windows, but if you need the ACE ODBC driver you're kinda outta luck from making your app natively 64 bit.  Then again, if you want your app on both x86 and x64 from the same version it's a better option anyway.  The only reason I make my apps natively 64 bit is when I plan to create the same app in 32 bit, unless of course I know my user base will only be on x64 editions of windows...which doesn't happen often. 

    Of course this comment thread will come back to bite me in the tail because Microsoft I believe did in fact now release a 64 bit version of ACE...or so I've read recently.  I guess 3 years is long enough to say that back then I was at least partially correct...lol.


    If at first you don't succeed, skydiving is definitely not for you!
    Wednesday, July 20, 2011 2:23 PM
  • Hi, I had the same problem and downloading the Office 2007 drivers from the link below solved my problems too. If you download it and it does not show in the options, try to uninstall and install again.

    Also, I used the import wizard directly from the SQL Server Management Studio and it worked perfectly, I did not use an script.

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

    Good Luck!

    Thursday, October 06, 2011 8:33 PM
  • Hi,

    The mentioned error occurs when Microsoft Office 2007 is not installed on the machine where SQL Server 2008 Integration Services is installed. In order to resolve this issue you need to install 2007 Office System Driver: Data Connectivity Components on the server where SSIS is running. For more information refer the following article titled “Troubleshooting OLE DB Provider Microsoft.ACE.OLEDB.12.0 is not registered Error when importing data from an Excel 2007 file to SQL Server 2008

    Hope that Helps!

    Cheers,
    Ashish Kumar Mehta

    Please click the Mark as Answer button if a post solves your problem and Vote as Helpful if a Post is Helpful!

    • Proposed as answer by Kalman Toth Tuesday, January 10, 2012 7:01 PM
    Tuesday, January 10, 2012 4:15 AM
  • I'm hesitant to add additional features to a Microsoft SQL Server running on Windows Server.   The platform is most secure when third party and even second party products (such as Access from the Office Suite) are not installed on the server.  I would say that it may be feasible to install a copy of Access on a development "server" (I use the word server as a SQL and BI developer will more likely be working on a server and not a client machine) but it still raises security issues.   As a rule, our company allows Office products to run on a machine when the client does not have Administrator permissions on the machine.  If the user has Administrator rights, Office products are not allowed to run.

    What I would be in favor of seeing would be an upgrade of the SSIS package on SQL 2008 that includes a more secure Access tool that would prevent one from having to install an Office product on a SQL server.

    The mentioned error occurs when Microsoft Office 2007 is not installed on the machine where
     SQL Server 2008 Integration Services is installed. In order to resolve this issue you need to 
    install 2007 Office System Driver: Data Connectivity Components on the server where SSIS is
     running. For more information refer the following article titled “Troubleshooting OLE DB
     Provider Microsoft.ACE.OLEDB.12.0 is not registered Error when importing data from an Excel 2007
     file to SQL Server 2008”
    

    R, J
    Thursday, January 12, 2012 2:47 PM
  • AAJ23,

    Probably good that you made such a bold statement back in 2008.  Possibly you were most correct at the time and someone read it and said, "why would we not want to support this?"   I hadn't noticed the 2008 date.


    R, J

    • Edited by Crakdkorn Thursday, January 12, 2012 2:51 PM
    Thursday, January 12, 2012 2:50 PM
  • I get lucky every once and a while ;)
    If at first you don't succeed, skydiving is definitely not for you!
    Thursday, January 12, 2012 6:07 PM