Excel Import to SQL Server using Distributed Queries
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/2008/R2/2012 on the x86/x64
platform.
· Microsoft Excel 2003/2007/2010 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!
This may increase your value in your company.
Visit www.savetodb.com,
download and install SaveToDB Express Add-In for Microsoft Excel and SaveToDB
Framework for Microsoft SQL Server for
free.
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 using configuring in a database.
Table of Contents
· Introduction
· The basics of Excel Data Import to
SQL Server
· Configuration steps for Excel data
import to SQL Server
· Install Microsoft.ACE.OLEDB.12.0
driver
· Configure Ad Hoc Distributed Queries
· Grant rigths to TEMP directory
· Configure ACE OLE DB properties
· 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
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.
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 rigths 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/2010 files
to SQL Server Microsoft.ACE.OLEDB.12.0 driver should be installed.
To download the driver use the following link:
Don't worry about "Access" in the
name.
Warning! x64 driver can not be installed if
Microsoft Office 2007/2010 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 rigths 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.
Usually only 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 have 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)".
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 rigths to TEMP directory
icacls C:\Windows\ServiceProfiles\<SQL
Server Account>\AppData\Local\Temp /grant <User>:(R,W)
The most commonly used pathes:
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$]
How-To: Import Excel 2007 to SQL Server x86
Step 1. Install 32-bit Microsoft.ACE.OLEDB.12.0
driver
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 rigths to TEMP directory
icacls C:\Windows\ServiceProfiles\<SQL
Server Account>\AppData\Local\Temp /grant <User>:(R,W)
The most commonly used pathes:
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$]
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$]
Conclusion
Using the described techniques you can import data from Microsof Excel
2003/2007/2010 to SQL Server 2005/2008/R2/2012 on 32-bit or 64-bit platform.
