Selecting from an excel spreadsheet into SQL Server table

Question

This question has been asked before but I tried giving full admin rights to the SQL Server user on C:\temp\ folder location (I am using Windows authentication into SQL Server Express).

So for the following code snippet:

Declare @strSQL as varchar(200)
declare @file as varchar(200) 
SET @file='C:\temp\file.xlsx'
SET @strSQL=N'SELECT * INTO #mytemptable FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database='+@file+';HDR=YES'', ''SELECT * FROM [Sheet1$]'');'
SET @strSQL=@strSQL+N'SELECT * FROM ##mytemptable'
PRINT @strSQL
Exec (@strSQL)

EXPANDED SQL STATEMENT

SELECT * 
INTO #mytemptable 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\temp\file.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]');
SELECT * FROM ##mytemptable

I get this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

• Kin Shah: Try running FileMon when the SQL is executed. This will tell you if you have required permissions or not as described blogs.msdn.com/b/spike/archive/2008/07/23/…
• Steve: you appear to be writing to #mytemptable then you try to select from ##mytemptable. I guess you want ##mytemptable in both places
• Mike Fal: Is there a reason why you're using OPENROWSET instead of a more appropriate tool like BCP, BULK INSERT, or SSIS?

Answer 1

Excel is 32-bit, if you are using 64-bit version of Windows it could be causing this problem. I have 32 bit drivers installed and get the same error as you. In order to install the office 64 bit drivers would have to uninstall the 32 bit drivers first.