Introduction

This article describes the complete steps for Microsoft Excel data import to SQL Server using distributed queries technique.

The article describes the steps for all modern platforms:

  • Microsoft SQL Server 2005-2014 on the x86/x64 platform.
  • Microsoft Excel 2003-2016 files like *.xls, *.xlsx, *.xlsm, *.xlsb.

Bonus

You may develop amazing Microsoft Excel applications for working with Microsoft SQL Server using database development skills only!

Visit www.savetodb.com, download and install SaveToDB Add-In for Microsoft Excel.

That's all!

Connect to tables, views, and stored procedures, edit the data and save it back to a database.
Add features to your Microsoft Excel applications step by step configuring apps via SQL.

Table of Contents

The Basics of Excel Data Import to SQL Server

To import data from Microsoft Excel to SQL Server OPENROWSET and OPENDATASOURCE functions with OLE DB data source can be used.

The basic format for the Microsoft.Jet.OLEDB.4.0 provider is:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=C:\excel-sql-server.xls', [Sheet1$])
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
  'Data Source=C:\excel-sql-server.xls;Extended Properties=Excel 8.0')...[Sheet1$]

The basic format for the Microsoft.ACE.OLEDB.12.0 provider is:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=C:\excel-sql-server.xlsx', [Sheet1$])
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
  'Data Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]

The Microsoft.Jet.OLEDB.4.0 provider is used with 32-bit SQL Server for Excel 2003 files.

The Microsoft.ACE.OLEDB.12.0 provider is used with 64-bit SQL Server for any Excel files or 32-bit SQL Server for Excel 2007 files.

Pay attention that "Excel 12.0" string is used, not "Excel 14.0" as some MSDN resources say.

To top

Configuration Steps for Excel Data Import to SQL Server

# Step SQL Server x86
for Excel 2003
files *.xls
SQL Server x86
for Excel 2007
files *.xlsx, etc.
SQL Server x64
for any Excel
version files
1 Install Microsoft.ACE.OLEDB.12.0 driver not needed x86 x64
2 Configure Ad Hoc Distributed Queries yes yes yes
3 Grant rights to TEMP directory yes yes not needed
4 Configure ACE OLE DB properties not needed yes yes

Install Microsoft.ACE.OLEDB.12.0 driver

To import Excel 2007-2016 files to SQL Server Microsoft.ACE.OLEDB.12.0 driver should be installed.

To download the driver use the following link:

Microsoft Access Database Engine 2010 Redistributable

Don't worry about "Access" in the name.

Warning! x64 driver can not be installed if Microsoft Office 2007-2016 x86 is already installed!

So there is no way to import Excel data to SQL Server x64 using OPENROWSET/OPENDATASOURCE functions on a machine with Microsoft Office x86!

The SQL Server Error Message if Microsoft.ACE.OLEDB.12.0 is not installed

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

Configure Ad Hoc Distributed Queries

To configure Ad Hoc Distributed Queries use the following code:

sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

The SQL Server Error Message if Ad Hoc Distributed Queries component is turned off

Msg 15281, Level 16, State 1, Line 1
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',
see "Surface Area Configuration" in SQL Server Books Online.

Grant rights to TEMP directory

This step is required only for 32-bit SQL Server with any OLE DB provider.

The main problem is that an OLE DB provider creates a temporary file during the query in the SQL Server temp directory using credentials of a user who run the query.

The default directory for SQL Server is a default directory for SQL Server service account.

If SQL Server is run under Network Service account the temp directory is like:

C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

If SQL Server is run under Local Service account the temp directory is like:

C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

Microsoft recommends two ways for the solution:

  1. A change of SQL Server TEMP directory and a grant of full rights for all users to this directory.
  2. Grant of read/write rights to the current SQL Server TEMP directory.

See details: PRB: "Unspecified error" Error 7399 Using OPENROWSET Against Jet Database

Usually, only a few accounts are used for import operations. So we can just add rights for these accounts.

For example, icacls utility can be used for the rights setup:

icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant vs:(R,W)

if SQL Server is started under Network Service and login "vs" is used to run the queries.

The SQL Server Error Message if a user has no rights for SQL Server TEMP directory

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

Configure ACE OLE DB properties

This step is required only if the Microsoft.ACE.OLEDB.12.0 provider is used.

Use the following T-SQL code:

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

The SQL Server Error Messages if OLE DB properties are not configured

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

To top

How-To: Import Excel 2003 to SQL Server x86

Step 1. Configure Ad Hoc Distributed Queries

sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Step 2. Grant rights to TEMP directory

icacls C:\Windows\ServiceProfiles\<SQL Server Account>\AppData\Local\Temp /grant <User>:(R,W)

The most commonly used paths:

C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

Use Microsoft.Jet.OLEDB.4.0 OPENROWSET format

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=C:\excel-sql-server.xls', [Sheet1$])

Use Microsoft.Jet.OLEDB.4.0 OPENDATASOURCE format

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
  'Data Source=C:\excel-sql-server.xls;Extended Properties=Excel 8.0')...[Sheet1$]

To top

How-To: Import Excel 2007 to SQL Server x86

Step 1. Install the 32-bit Microsoft.ACE.OLEDB.12.0 driver

Microsoft Access Database Engine 2010 Redistributable

Step 2. Configure Ad Hoc Distributed Queries

sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Step 3. Grant rights to TEMP directory

icacls C:\Windows\ServiceProfiles\<SQL Server Account>\AppData\Local\Temp /grant <User>:(R,W)

The most commonly used paths:

C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

Step 4. Configure OLE DB properties

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Use Microsoft.ACE.OLEDB.12.0 OPENROWSET format

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=C:\excel-sql-server.xlsx', [Sheet1$])

Use Microsoft.ACE.OLEDB.12.0 OPENDATASOURCE format

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
  'Data Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]

To top

How-To: Import Excel 2003/2007 to SQL Server x64

Step 1. Install 64-bit Microsoft.ACE.OLEDB.12.0 driver

Microsoft Access Database Engine 2010 Redistributable

Step 2. Configure Ad Hoc Distributed Queries

sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Step 3. Configure OLE DB properties

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Use Microsoft.ACE.OLEDB.12.0 OPENROWSET format

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=C:\excel-sql-server.xlsx', [Sheet1$])

Use Microsoft.ACE.OLEDB.12.0 OPENDATASOURCE format

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
  'Data Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]

To top

Conclusion

Using the described techniques you can import data from Microsof Excel 2003-2016 to SQL Server 2005-2014 on the 32-bit or 64-bit platform.

To top

Comments  

# Mirza Naher Abbas 2014-11-12 09:08
Hi guys...

One more thing I wud like to tell..
Many of you still have the problem after applying aal the thing above "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."

This is because u r not giving the exact source.
never give the location of "Desktop".
try to ignore the source drive having program files or Desktop.
Reply | Reply with quote | Quote
# Nailesh 2014-10-27 11:58
Hi guys,
i have read .xlsb file using sql query so know any solution so tell me.

thanks
nailesh
Reply | Reply with quote | Quote
# Faiz 2014-10-20 12:00
Thank you for the article.

Just wanted to add that the temp folder permissions are still required, just tested and verified that now.
Reply | Reply with quote | Quote
# Karvul Khan 2014-07-08 09:37
Thank you for this excellent article. Still, Temp folder permissions are definitely still required with x64 SQL on x64 OS using x64 ACE driver.

Another observation: One can receive the symptoms described for missing temp rights, but without the unspecified error (The portion Msg 7303 ... "(null)") when using invalid Range specs *and* the Excel file has Office 97 format.
Reply | Reply with quote | Quote
# Majer 2014-10-02 15:10
I've gone throught the temp permissions and it does not seem to resolve my error.

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I am running SQL Server 2008 R2 on windows server 2003 r2 using a domain service account for SQL Service. I applied the permissions to the folder "C:\Documents and Settings\svcaccount\SQLExec\Lo cal Settings\Temp" with no benifit. Anyone have a solution?
Reply | Reply with quote | Quote
# Sergey Vaselenko 2014-10-02 18:05
Try
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Reply | Reply with quote | Quote
# sharif 2013-10-13 11:48
agree with the comment above, excellent document and examples.
managed to get it to work at home, vista/office 2007/ sql2012
can’t at work, XP sp2/office 2007/ sql 2008

main problem seem to be with the permission

“The main problem is that an OLE DB provider creates a temporary file during the query in the SQL Server temp directory using credentials of a user who run the query.”

I am running both server and a user running the query under the same windows account (its windows admin and sql sys admin) everything on the one box.

also there isn’t such path C:\Windows\ServiceProfiles\, so when under network account can’t add permissions to
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\

any ideas will be appreciated

if email is used, then "_deletet_me_" has to be removed for it to work.
Reply | Reply with quote | Quote
# AJ 2013-10-11 23:17
Need some help here. I have no problem using the JET provider with an openrowset command to extract the contents of an XLS file. However, while using the ACE provider for an XLSX file, I get "OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"."

I have performed all suggestions as above. ACE is definitely installed as I can see it as a provider in SSMS. I'm confused since JET works fine. Any ideas?
Reply | Reply with quote | Quote
# SI 2015-11-26 15:24
I have the same issue. Any suggestions?
Reply | Reply with quote | Quote
# Monted 2013-09-28 06:29
Permissions to NetworkService temp folder not limited to 32-bit server. I needed to do this on my 2008 R2 64-bit server to make both the openrowsets and linked servers to XLSX function properly. Excellent resource. thx
Reply | Reply with quote | Quote
# Chris Lemmonds 2013-09-22 17:59
This is well-written article with great information. I just wanted to make everyone aware that on several occasions I've run into a horrible issue with opendata source using ACE for .xlsx files: the sql server and agent services terminally crash. Agent cannot restart the sql server service because it has been taken out too. Others who have encountered this issue note that it seems to only occur during heavy usage. My last stack dump showed 88% memory load when sql server crashed, so it may be a memory leak of some kind. Naturally, I now convert all xlsx to either xls or csv before importing. An example of the last statement that crashed the production server is as follows:
select * into ##ticket5119
from opendatasource
('Microsoft.ACE.OLEDB.12.0','D ata Source="\\192.168.4.92\Public\Codes.xlsx";
User ID=Admin;Password=;
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"'
)...[Sheet1$] x
Reply | Reply with quote | Quote
# Selvakumar 2013-02-18 14:35
Thanks a lot... Very helpfull. I was trying to read an Excel 2010 file from SQL Server 2012 for the past 4 days. Now after reading the solution here, the problem is resolve and i got relieved.
Reply | Reply with quote | Quote
# Vishal 2012-10-15 11:33
Thanks a lot,

"The SQL Server Error Message if a user have no rights for SQL Server TEMP directory." solved my problem for "OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error"."
Reply | Reply with quote | Quote
# Lubos Bednar 2012-06-27 19:18
Hi guys,

I have already solved this issue 2 times. In last time, running SQL Management Studio (or VS studio) "run as administrator" helped.

Regards,
Lubos
Reply | Reply with quote | Quote
# pcs 2016-09-14 19:18
Lubos ++ - I had to run ssis/datatools and ssms as admin to get this to work - thank you
Reply | Reply with quote | Quote

Use Excel as a DB front-end
with the SaveToDB add-in

Use Excel as a DB front-end with the SaveToDB add-in