Sql-server – OPENROWSET can’t import an excel spreadsheet into SQL Server 2005 Express

excelimportsql serversql-server-2005

I am trying to run the following query to select data from an excel spreadsheet:

SELECT     FT_ID_SOCIETE_FF2C,FT_NOM,FT_ADR1,FT_ADR2,FT_ADR3,FT_CP,FT_VILLE,FT_TEL,FT_FAX,F10 FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\SQLFTP\LISTEFF3CFTP.xls;HDR=YES','select * from [sheet1$]');

SSMS is sending back the following 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)".

ProcessMonitor is telling me this :

Date & Time: 21/11/2011 18:41:55 
Event Class: File System Operation:
CreateFile Result: NOT A DIRECTORY 
Path: D:\SQLFTP\LISTEFF3CFTP.xls
TID: 3496 
Duration: 0.0000070 
Desired Access: Execute/Traverse,Synchronize 
Disposition: Open 
Options: Directory, Synchronous IO Non-Alert 
Attributes: n/a 
ShareMode: Read, Write 
AllocationSize: n/a
Impersonating: VM-CHALEUR-FIOU\Administrator

I have also installed the Office 2007 components and tried to use Microsoft.ACE.OLEDB.12.0 and got the same results from both SSMS and process monitor.

I am however able to import a CSV file from the same location so it is not a permissions issue.
I have tried running the same import from other SQL Server 2005 express servers and got the same result.
The Ad Hoc distributed queries option is set to 1 in sp_configure.

Anyone know what I'm doing wrong?

Best Answer

Ok well I found the solution or at least its one or both of the two

  1. Instead of running express with the Network Service account, I created a regular user. Then I gave that user rights to the folder with the excel spreadsheets.

  2. I had already installed AccessDatabaseEngine.exe but I hadn't restarted the computer. This time I restarted it.

This is the OPENROWSET SELECT statement I used

SELECT FT_ID_SOCIETE_FF2C,FT_NOM,FT_ADR1,FT_ADR2,FT_CP,FT_VILLE,FT_TEL,FT_FAXFROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\SQLFTP\LISTEFF3CFTP.xls;HDR=YES', 'select * from [Requêtechauffage fioul$]');

So maybe it was a permissions problem? Weird since I could import csv files. Maybe it was just having to restart the computer after having installed AccessDatabaseEngine.exe.

Related Question