Get Excel sheet into temp table using a script

Question

Im trying to get this excel sheet into a table, so I can apply select statements to it etc, to update tables with its info.

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
          'Excel 8.0;Database=C:\PB.xlsx',
          'SELECT * FROM [Sheet1$]')

Im running 64 bit machine. No idea what excel version it is.

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)".

When I change to Excel 12.0;

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.". 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)".

Solution 1

There are 5 possible causes for this error.

1. The jet engine must be installed on the server. Installing MS Office on the server sorts that out.
2. The path to the xls is relative to the server, not the workstation you're running the command from
3. The account that runs the SQL server service must have write access to the folder where the xls is. One possible solution is to changing the temp= and tmp= environment variables of the service startup account (eg. Administrator) to (for example) c:\temp, then enable Full Control on c:\temp to Everyone

4...

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

5....

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 0 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 0
GO 

Now I don't know why this works, especially considering that everyone else says they should be set to 1. For me however, setting them to zero, did the trick for the following SQL statement on SQL Server 2008R2 32bit and M$ Office 2007

Select * 
into [temp_table$]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\EXPENDITURE REPORT.xls;HDR=YES;IMEX=1',
'SELECT * FROM [EXPENDITURE SHEET$]')

Note: I purposely have used an example in which both the filename and the worksheet name have spaces to show that this can be done.

Solution 2

For xlsx files (Excel 2007-2010) you can use the ACE oledb provider instead of the JET

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

Solution 3

Just in case someone else stumbles upon this years later like I did, this is what worked for me:

We had a box that had SQL Server Express 2012 on a Windows 8 64 bit machine with no version of Office installed.

Here is part of my stored proc I setup:

INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=R:\Export Membership Database\Member_Export.xls;',
'SELECT * FROM [combined$]')

(the rest of the SELECT statement below this)

I received this error:

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

We changed the configuration:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Ran the stored proc again and then got this error:

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

Found we needed to install this:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en

After that, worked like a charm!

Comments

[IAmGroot]

I get this one line Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

[Joao Leal]

Forgot to mention that you need to have Office 2007+ installed on the server or this component: microsoft.com/en-us/download/details.aspx?id=23734

[Joao Leal]

Can you try Solution 3 from here: aspsnippets.com/Articles/…

[IAmGroot]

I used csv file in the end, But marking this as correct due to a good detailed, multi option answer.

[Tim Lehner]

As mentioned in the other answer, you can try Microsoft.ACE.OLEDB.12.0, even with Excel 8.0

[Hannover Fist]

plus 1 for using spaces in the names.