Excel to sql table error Msg 7350

Question

I'm using this code for import an excel file into a sql table

USE master
GO
sp_configure 'show advanced options', 1;

 RECONFIGURE;
 GO

  sp_configure 'Ad Hoc Distributed Queries', 1;

   RECONFIGURE;
   GO

insert into memberform (id)
    SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\\test.xls;HDR=YES', 'select * from [Sheet1$]')

But I'm getting error:

Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" >for linked server "(null)".

Is there something I should parametrize first in sql?

Answer 1

I found the solution, I just installed AccessDatabaseEngine_X64 bit. Cause My sql is 64 bit and my office 32 bit thats why it didnt worked.The problem is that you cant install Access Database Engine x64 on office 2007 32bit. You will need this instructions: https://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

Also in my code i should use ace insted of jet.Also because i'm using excel 2007 i need to change it to excel 12.0. Like the example below

SELECT * FROM OPENROWSET ('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0;Database=C:\\test.xlsx;HDR=YES', 'select * from [Sheet1$]')