Introduction

This article describes the complete steps for Microsoft Excel data import to SQL Server using linked servers 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 Using Linked Servers

To import data from Microsoft Excel 2003 files to 32-bit SQL Server the Microsoft.Jet.OLEDB.4.0 provider can be used. Use the T-SQL code like this to add a linked server to Excel 2003 workbook:

EXEC sp_addlinkedserver
    @server = 'ExcelServer1',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @datasrc = 'C:\Test\excel-sql-server.xls',
    @provstr = 'Excel 8.0;IMEX=1;HDR=YES;'

To import data from Microsoft Excel 2007 to 32-bit SQL Server or from any Microsoft Excel files to 64-bit SQL Server the Microsoft.ACE.OLEDB.12.0 provider should be used. Use the T-SQL code like this:

EXEC sp_addlinkedserver
    @server = 'ExcelServer2',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\Test\excel-sql-server.xlsx',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'

IMEX=1 defines to import all Excel column data including data of mixed types.

HDR=YES defines that Excel data contain column headers.

The way to modify a linked server is to drop and create it again. Use the T-SQL code like this:

EXEC sp_dropserver
    @server = N'ExcelServer1',
    @droplogins='droplogins'

There are two ways to use linked server data. The first way is like this:

SELECT * FROM ExcelServer1...[Sheet1$]

and the second one is the use of the OPENQUERY function:

SELECT * FROM OPENQUERY(ExcelServer1, 'SELECT * FROM [Sheet1$]')

The use of the OPENQUERY function is more flexible because queries can contain Excel ranges unlike the entire sheet in the first case.

To top

Configuration Steps for Excel Data Import to SQL Server Using Linked Servers

# 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 Grant rights to TEMP directory yes yes not needed
3 Configure ACE OLE DB properties not needed yes yes
4 Configure linked servers yes yes yes

Install Microsoft.ACE.OLEDB.12.0 driver

To import Excel 2007-2016 files to SQL Server the 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 cannot 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 Linked Servers technique on a machine with Microsoft Office x86!

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

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelServer2" returned message "The Microsoft Access database engine cannot open or write to the file ''.
It is already opened exclusively by another user, or you need permission to view and write its data.".
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 "ExcelServer2".

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 the Network Service account the temp directory is like:

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

If SQL Server is run under the 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 the 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 "ExcelServer1" 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 "ExcelServer1".

or the message for Microsoft.ACE.OLEDB.12.0 provider:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelServer2" 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 "ExcelServer2".

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

Configure linked servers

The configuring of linked servers is discussed in the Basics topic.

Use the T-SQL code like this for Excel 2003 linked servers:

EXEC sp_addlinkedserver
    @server = 'ExcelServer1',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @datasrc = 'C:\Test\excel-sql-server.xls',
    @provstr = 'Excel 8.0;IMEX=1;HDR=YES;'

Use the T-SQL code like this for Excel 2007 linked servers or on SQL Server x64:

EXEC sp_addlinkedserver
    @server = 'ExcelServer2',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\Test\excel-sql-server.xlsx',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'

To top

How-To: Import Excel 2003 to SQL Server x86

Step 1. 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 2. Configure linked server using Microsoft.Jet.OLEDB.4.0 provider

EXEC sp_addlinkedserver
    @server = 'ExcelServer1',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @datasrc = 'C:\Test\excel-sql-server.xls',
    @provstr = 'Excel 8.0;IMEX=1;HDR=YES;'

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. 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 3. Configure ACE 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

Step 4. Configure linked server using Microsoft.ACE.OLEDB.12.0 provider

EXEC sp_addlinkedserver
    @server = 'ExcelServer2',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\Test\excel-sql-server.xlsx',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'

To top

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

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

Microsoft Access Database Engine 2010 Redistributable

Step 2. Configure ACE 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

Step 3. Configure linked server using Microsoft.ACE.OLEDB.12.0 provider

EXEC sp_addlinkedserver
    @server = 'ExcelServer2',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\Test\excel-sql-server.xlsx',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'

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  

# KimO 2016-05-27 10:51
Great :-)
Reply | Reply with quote | Quote
# yogi 2016-01-08 16:31
i am using sql account. how to grant rights to the temp directory
Reply | Reply with quote | Quote
# Subir Das 2015-12-01 10:51
Openrowset method is working for specific location, when I copy the same file to other location, it failed with below error message
----------------------------------------------
Msg 7399, Level 16, State 1, Line 3
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 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Reply | Reply with quote | Quote
# Ken 2016-04-20 01:58
Did you ever find an answer to this solution?
Reply | Reply with quote | Quote
# LBlack 2015-11-06 18:01
Thank you, Thank You, Thank You!!!!!
Reply | Reply with quote | Quote
# Marc 2015-06-24 16:01
I'm confused here still. Heres my environment.

SQL Server 2008 R2 installed on Windows Server 2008 R2. SSMS installed, these commands work there.

When trying to run the same commands from another computer (my dev box), I can't do it, even though I've tried the suggestions here and elsewhere. I have SSMS 2014 installed here, but had the same issues with 2012 and 2008 R2.

When I run this command in SSMS on my dev box, does it send the entire command to the SQL server? Which security context does it run in? I really want to be able to develop these kinds of stored procs on my dev box, right now this is the only reason I ever RDP to my server.
Reply | Reply with quote | Quote
# NAsko 2015-05-20 08:12
Thank you very much for the information about the permissions
I din't managed with folder TEMP , but it works for temp folder of NETWONK user, thank you.
Reply | Reply with quote | Quote
# Zak 2015-03-31 09:30
I have done everything but still get the following error:

The test connection to the linked server failed.

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_Source" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_Source". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
Reply | Reply with quote | Quote
# Daniel Kram 2015-01-19 17:39
Wonderful! Thank you.
Reply | Reply with quote | Quote
# Freddy 2014-08-14 00:36
Same issue for non-admins on MS Server 2012 x64
Give full control to:
C:\Users\MSSQLSERVER\AppData\Local\Temp
Reply | Reply with quote | Quote
# Johnny 2016-08-03 14:26
You are an angel. This should be elevated to the setup steps in the actual article.
Reply | Reply with quote | Quote
# Allront 2015-08-24 14:54
THANKS A LOT FOR THAT HINT!! GREAT!
I've been trying to solve this problem for a monthes!
Reply | Reply with quote | Quote
# Stefan 2014-07-23 06:11
Brilliant!! SSIS is just the worst bit of software and this is the only reason I was using it for.
You rock!
Reply | Reply with quote | Quote
# Max 2014-02-07 02:40
Thank you for this post, very useful.

I juste wanted to add that you can install x64 access drivers, even if Microsoft Office x86 is installed. You just have to install the drivers in passive mode, through the command prompt, with the following command :

c:>AccessDatabaseEngine_x64.exe /passive

I tried it out and experienced no trouble, it's working this way since several month.
Reply | Reply with quote | Quote
# Shri 2015-09-09 08:28
Thanks Max,

I was facing problem to install "AccessDatabaseEngine_x64.exe" because 32bit office was already installed on my machine.

"c:>AccessDatabaseEngine_x64.exe /passive" solved my problem.
Reply | Reply with quote | Quote
# berna 2014-04-13 03:22
thank you thank you thank you
Reply | Reply with quote | Quote
# Sergey Vaselenko 2014-02-07 11:45
Thank you very much for this tip.

This allows using 64-bit drivers in x64 and AnyCPU executables on 64-bit Windows even Microsoft Office x86 installed. Thanks!
Reply | Reply with quote | Quote
# Graham 2013-08-29 17:47
I have been working on this problem off and on for almost a year. THANK YOU THANK YOU THANK YOU THANK YOU !!!!
Reply | Reply with quote | Quote
# Tim 2012-05-19 21:01
Great advice - worked for me in one environment. On another, I still had problems. It turns out that on Windows 7/SQLExpress 2012, you need to grant rights to the temp directory of the SQL service acount (for defaultinstall) located at C:\Users\MSSQL$SQLEXPRESS\AppData\Local\Temp.
Again thanks - a great page.
Reply | Reply with quote | Quote
# James P. 2016-02-03 09:04
Thanks Tim, the temp folder location you mentioned solve my problem. My service account is "NT Service\MSSQLSERVER" and I tried setting the permission as the article suggest to C:\users\MSSQLServer\AppData\L ocal\Temp but it's still not working, desperate, finally I tried "Everyone":(R,W) and it worked. Now I just leave it as it is.
Reply | Reply with quote | Quote
# Staszek 2014-10-14 07:11
Thank you very much for the information about the permissions for the folder TEMP . I'm that helped , 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