原文地址: http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm
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.
Table of Contents
- Introduction
- The basics of Excel Data Import to SQL Server
- Configuration steps for Excel data import to SQL Server
- 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
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
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:
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/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:
- 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 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
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 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.
See Also
Reference
How To
How to use Excel with SQL Server linked servers and distributed queries
Accessing Excel files on a x64 machine
Downloads
If you like this article, please, press your favourite social button below:
相关推荐
NPOI EXCEL从SQLServer中的导入导出 具体用法可参考
用配置文件生成器生成配置文件,导入配置文件然后调用DLL类库到处SQL SERVER的数据到EXCEL表格。 注意:基于MS .Net Framework 4.0 实例代码: Dim r As New AMReport.AmReport Dim app As New Microsoft.Office....
SQLSERVER EXCEL报表生成器.pdf
sql server 直接读取excel ,不需要把excel导入数据库。直接进行查询或者插入临时表后操作,包含32位及64位
ASP.NET将EXCEL导入SQL SERVER数据库的代码 ASP.NET将EXCEL导入SQL SERVER数据库的代码
熟悉SQL SERVER 2000的数据库管理员都知道,其DTS可以进行数据的导入导出,其实,我们也可以使用Transact-SQL语句进行导入导出操作。...利用下述方法,可以十分容易地实现SQL SERVER、ACCESS、EXCEL数据转换
Web页面中sqlserver快速导入excel,生成csv格式 有的程序在Web页SQLSERVER导入excel的过程中,会出现下载时找不到样式的错误提示。本资源避免了以上问题,快速打开,快速导出。 c#.Net2005下运行成功。
用sql server读取和写入excel文件,读取和写入数据非常方便,大批量数据导入与导出非常快
Excel数据导入到SQLserver数据库指定的表,可多页签,附sql语句
设置SQLServer2008(64位),SQL直接读取Excel文件数据
Excel导入SQLserver源码_excel2sql
1.1 SQL Server Management Studio中导入 1.2 直接启动SQL Server导入和导出向导 1.3 SQLServer查询分析器使用代码导入数据 1.4 导入null值数据的说明
将Excel中的数据导入到SqlServer中的实现方法总结。还有一个简单的例子
SQL Server导出表到EXCEL文件的存储过程,整理sql如何把表导出
Excel导入SQLserver源码 Excel是一种非常灵活的电子表格软件,用它可以存储各种数据,本节将对如何将Excel导入SQL Server2000数据库进行详细介绍。 开发环境:NET2.0 开发工具:vs2005 开发语言:c# 数据库:server...
1.C++ Builder 2010 2.将SQL server数据写入excel表 3.已编译通过,内附带源码
sqlserver导出excel文件出错解决方案 0x800A0E7A 0x80040154 sqlserver导出excel
sqlserver导出excel的小工具
Excel根据表格,生成sql语句 ,生成建表语句,自行到数据库中执行, 需要excel启用宏,按规则填充表格后可生成表格目录及生成sqlserver语句。
SQL与ACCESS (EXCEL)之间的数据转换