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

When I got these errors I was told by our DBA it was because that driver needs to be installed on the server. Is it installed on yours? — Taryn
– Taryn, Commented Aug 23, 2012 at 11:31
@bluefeet This is currently my laptop, and Ive not installed anything extra, so if its not in 2008 R2. then Nope. — IAmGroot
– IAmGroot, Commented Aug 23, 2012 at 11:35
Sometime ago I had a 7399 error due to 32/64 bit ODBC driver incompatibility. I was trying to read data from an Excel worksheet to a SQL Server 2008 R2 64 bits. The Microsoft-Jet.OleDB driver is 32 bits. Unfortunately I could not solve the issue and I had to install a 32 bit server to read excel files. — Yván Ecarri
– Yván Ecarri, Commented Aug 23, 2012 at 11:51
I was accidently runnng off the wrong server, so it was 32 bit, It doesnt work on my laptop at all. THe same problem exists. Ive gone for CSV file format instead, seems to be working. THanks guys. — IAmGroot
– IAmGroot, Commented Aug 23, 2012 at 12:02

Joao Leal · Accepted Answer · 2012-08-23 12:12:42Z

9

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$]');

answered Aug 23, 2012 at 12:12

Joao Leal

5,5521 gold badge15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

IAmGroot Over a year ago

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

Joao Leal Over a year ago

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 Over a year ago

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

Freddie bell · Accepted Answer · 2012-09-04 08:42:20Z

8

There are 5 possible causes for this error.

The jet engine must be installed on the server. Installing MS Office on the server sorts that out.
The path to the xls is relative to the server, not the workstation you're running the command from
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.

edited Sep 4, 2012 at 8:42

answered Sep 4, 2012 at 8:18

Freddie bell

1921 silver badge8 bronze badges

3 Comments

IAmGroot Over a year ago

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

Tim Lehner Over a year ago

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

Hannover Fist Over a year ago

plus 1 for using spaces in the names.

sqluser · Accepted Answer · 2016-04-07 04:45:34Z

1

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!

edited Apr 7, 2016 at 4:45

sqluser

5,7127 gold badges44 silver badges52 bronze badges

answered Apr 6, 2016 at 19:02

user6168630

111 bronze badge

1 Comment

Henrik Staun Poulsen Mar 12 at 13:45

link is now dead. what do I need to install?

Collectives™ on Stack Overflow

Get Excel sheet into temp table using a script

3 Answers 3

3 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related