sqlserver excel

最新推荐文章于 2024-04-01 16:36:14 发布
原创 最新推荐文章于 2024-04-01 16:36:14 发布 · 229 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库

本文详细介绍如何使用分布式查询技术将Microsoft Excel数据导入到SQL Server。包括配置步骤、安装驱动程序、设置权限等内容，适用于多种Excel文件格式及不同版本的SQL Server。

原文地址： 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

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

#StepSQL Server x86
for Excel 2003
files *.xls		SQL Server x86
for Excel 2007
files *.xlsx, etc.		SQL Server x64
for any Excel
version files
1Install Microsoft.ACE.OLEDB.12.0 drivernot neededx86x64
2Configure Ad Hoc Distributed Queriesyesyesyes
3Grant rigths to TEMP directoryyesyesnot needed
4Configure ACE OLE DB propertiesnot neededyesyes

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:

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

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 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$]

To top

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$]

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/2007/2010 to SQL Server 2005/2008/R2/2012 on 32-bit or 64-bit platform.

To top

See Also

Reference

OPENDATASOURCE (Transact-SQL)

OPENROWSET (Transact-SQL)

Connection strings for Excel

How To

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

Accessing Excel files on a x64 machine

Downloads

Microsoft Access Database Engine 2010 Redistributable

2007 Office System Driver: Data Connectivity Components

If you like this article, please, press your favourite social button below:

Excel从SQLServer获取数据
10-22
如何通过Excel来获取SQLServer数据 方便对数据库中的某些数据取数！
sqlserver直接读取excel
08-12
sqlserver直接读取excel，直接读取excel中的数据，并且对数据进行处理。。。
sql server 直接读取excel
07-04
sql server 直接读取excel ，不需要把excel导入数据库。直接进行查询或者插入临时表后操作，包含32位及64位
用sqlserver处理excel表格
bailin710258的专栏
09-18 2039
本来最近在研究微信公众平台的，老大临时交我个任务，把excel表格里的数据导入sql数据库，我想这so easy嘛。 没想都在上面消磨了两天... 把情况介绍下：在数据库中有如下这样结构的表 我只取关键的及列里面还有很多数据。 有一张id和name对照的excel文件和N张结构类似的excel表（没有id那一列）
SqlServer查询Excel中的数据
a572893208的博客
10-10 264
步骤如下： --1.开启远程查询支持 exec sp_configure 'show advanced options' ,1 reconfigure exec sp_configure 'Ad Hoc Distributed Queries',1 reconfigure --2.链接Excel Microsoft ACE 12.0 OLE DB Provider 读...
SQLSERVER EXCEL报表生成器.pdf
11-23
SQLSERVER EXCEL报表生成器 SQLSERVER EXCEL报表生成器是一款功能强大且灵活的报表生成工具，旨在帮助用户快速生成高质量的报表。下面是该工具的详细知识点： 一、环境需求 1.1 操作系统：报表生成器支持的操作...
NPOI SQLServer Excel
05-02
NPOI SQLServer Excel是一个关于使用NPOI库在.NET环境中与Excel进行数据交互，并结合SQLServer数据库进行数据导入导出的技术主题。NPOI是一个开源的.NET库，专门用于处理Microsoft Office文件，如Excel（XLS和XLSX...
SQLSERVER EXCEL报表生成器
06-19
用配置文件生成器生成配置文件，导入配置文件然后调用DLL类库到处SQL SERVER的数据到EXCEL表格。 注意：基于MS .Net Framework 4.0 实例代码： Dim r As New AMReport.AmReport Dim app As New Microsoft.Office....
将SQL server数据写入excel表
06-16
8. **项目结构**：根据文件名，该项目可能包含一个主程序文件（`将SQLserver数据写入excel表.cpp`）、一个窗体设计文件（`将SQLserver数据写入excel表.dfm`）和头文件（`将SQLserver数据写入excel表.h`），以及项目...
SQL Server 导入EXCEL数据
m0_37980851的博客
09-15 2061
一、导入本地的excel数据到本地数据库，流程如下： 1、--启动OpenDataSource exec sp_configure 'show advanced options',1 reconfigure exec sp_configure 'Ad Hoc Distributed Queries',1 RECONFIGURE 2、--查询excel的数据 SELECT * FROM Op...
SQLServer导出数据到Excel
07-22
将数据库中的表数据导出到Excel电子表格中。
在Excel里操作SQLServer2005数据库（请勿转载）
09-10
用Excel VB做成的数据库管理客户端，可在Excel里显示表结构，对数据库进行常规操作等！只要打开Excel即能操作数据库非常方便！
excel 导入到sqlserver
09-25
非常方便的excel导入到sql的工具，无需激活，永久使用，是你开发工作中必备的工具
sqlserver导出excel工具
08-10
sqlserver导出excel的小工具
SQLServer导入Excel方式
11-16
Excel数据导入到SQLserver数据库指定的表，可多页签，附sql语句
SqlServer导入Excel文件数据
热门推荐
qq_45880917的博客
06-30 1万+
SqlServer导入Excel文件数据
【SQL Server】2. 将数据导入导出到Excel表格当中
哈茶的博客
04-01 8707
该文章包含如何将数据库当中的数据导入导出到Excel表格当中:1,通过软件自带的方式，2,通过Pycharm(ODBC)的方式。
SQLServer导入Excel格式数据
落花流水
03-17 5161
sqlserver导入Excel格式数据
将SqlServer的数据导出到Excel中的各种方法
立雪三尺
04-19 5722
以下都只是介绍操作的原理，具体要求要在应用中具体分析改变。 1.       此方法常用在form或者Console Application中，使用时须用要添加Reference，具体做法：           右键点击项目添加“Add Reference”，在Tom组件下，选择“Microsoft Excel 14.0 Object Library”，然后在项目中使用
SQL SERVER excel功能
最新发布
02-27
### 如何在 SQL Server 中使用 Excel 功能或集成 #### 使用 SQL Server 导入 Excel 数据 为了将 Excel 表格中的数据导入到 SQL Server，可以采用多种方法之一。一种常见的做法是通过 SQL Server Management Studio (SSMS) 的导入向导来完成此操作[^1]。 当启动 SSMS 并连接至目标数据库实例之后，在对象资源管理器中右键点击所需的数据库节点并选择“任务”，接着选取“导入数据”。这会打开 SQL Server 导入和导出向导界面。在此过程中，需指定源文件路径以及所使用的 Excel 文件版本号；对于后者的选择应当依据实际待处理的 .xls 或者 .xlsx 文件格式而定。 一旦选择了正确的 Excel 版本，便能预览即将被迁移的数据表结构，并可自定义映射关系以便于适应目的端的架构需求。此外，还可以设置转换逻辑以满足特定业务规则的要求。最终步骤涉及确认配置参数无误后执行整个传输流程，从而实现从电子表格到关系型数据库系统的无缝衔接。 ```sql -- 创建用于存储来自Excel数据的目标表（假设） CREATE TABLE dbo.ImportedData ( ID INT, Name NVARCHAR(50), Value DECIMAL(18, 2) ); GO ``` 另一种方式则是利用 `OPENROWSET` 函数配合 Microsoft Jet OLE DB 提供程序直接查询外部 Excel 文档内的记录集： ```sql SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;IMEX=1;', 'SELECT * FROM [Sheet1$]') AS Import; ``` 上述命令展示了如何读取名为 Sheet1 的工作表内所有列的内容。请注意，这里提到的驱动程序名称 (`ACE.OLEDB`) 和版本号可能因环境差异有所不同，因此建议根据实际情况调整相应部分。 #### 实现双向交互 除了单方面地把 Excel 资料迁移到 SQL Server 外，亦可通过编写脚本来定期同步两套系统间的信息变更情况。借助 PowerShell 或其他编程语言编写的自动化工具能够有效地简化这一过程，确保双方始终保持最新状态下的互相对应关系。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值