Excel Import to SQL Server using Linked Servers

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-2016 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

Introduction

The basics of Excel data import to SQL Server using linked servers

Configuration steps for Excel data import to SQL Server using linked servers

Install Microsoft.ACE.OLEDB.12.0 driver

Grant rights to TEMP directory

Configure ACE OLE DB properties

Configure linked servers

How-To: Import Excel 2003 to SQL Server x86

How-To: Import Excel 2007 to SQL Server x86

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

Conclusion

See Also

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:

A change of SQL Server TEMP directory and a grant of full rights for all users to this directory.

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-2016 on the 32-bit or 64-bit platform.

To top

See Also

Reference

OPENQUERY (Transact-SQL)

How To

How to use Excel with SQL Server linked servers and distributed queries

Downloads

Microsoft Access Database Engine 2010 Redistributable

2007 Office System Driver: Data Connectivity Components