将 Excel 2010 导入 SQL Server [英] Import Excel 2010 into SQL Server

问题描述

我使用 Excel 收集 &配置数据，然后导入SQL Server 2012存储.

I use Excel to collect & configure data, then import it into SQL Server 2012 for storage.

到目前为止，我一直在使用 SQL Server Import &导出向导，但是不断手动设置它很痛苦.由于我使用的是 Express，它当然不允许我保存甚至查看传输数据的实际命令.

So far I've been using the SQL Server Import & Export Wizard, but it is a pain to manually set it up constantly. Since I'm using Express, of course it won't allow me to save, or even view, the actual commands to transfer the data.

我尝试设置链接服务器，根据 如何使用 Excel 与 SQL Server 链接服务器和分布式查询，但得到以下错误:

I tried to set up a linked server, per How to use Excel with SQL Server linked servers and distributed queries, but get the following error:

链接服务器已创建，但连接测试失败.您要保留链接服务器吗?

执行 Transact-SQL 语句或批处理时发生异常.(Microsoft.SqlServer.ConnectionInfo)

无法为链接服务器FLTST"初始化 OLE DB 提供程序Microsoft.Jet.OLEDB.4.0"的数据源对象.
链接服务器FLTST"的 OLE DB 提供程序Microsoft.Jet.OLEDB.4.0"返回消息未指定错误".(Microsoft SQL Server，错误:7303)

我认为可能是 Excel 版本号的问题，因为网页是 2005 年的，所以我尝试了:

I thought perhaps the Excel version number was the problem, since the web page is from 2005, so I tried with:

• Excel 8.0 (Excel 2002) 如页面所示
• 向导似乎使用了 Excel 12.0 (Excel 2007)
• 我实际拥有的 Excel 14.0 (Excel 2010)

所有这些都给了我相同的结果.

All of those gave me identical results.

接下来我尝试了分布式查询，如导入excel所示文件到 SQL Server Express，(再次使用提供程序字符串的不同变体)

Next I tried the distributed query as shown at Import excel file to SQL Server Express, (again with different variations of the provider string)

USE ExTest

SELECT * INTO TstTbl FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 14.0;Database=c:\ExTest.xlsm', [Contacts])

go

这给了我以下错误:

链接服务器(null)"的 OLE DB 提供程序Microsoft.Jet.OLEDB.4.0"返回消息未指定错误".
消息 7303，级别 16，状态 1，第 3 行
无法为链接服务器(null)"初始化 OLE DB 提供程序Microsoft.Jet.OLEDB.4.0"的数据源对象.

而不是去 SQL Server &提取数据，我应该留在 Excel 中吗?推过去?

Instead of going to SQL Server & pulling the data in, should I stay in Excel & push it over?

我做错了什么?

PS:请不要告诉我将其转换为 csv 文件！我正在尝试更少步骤，而不是更多！

PS: Please don't tell me to convert it to a csv file! I'm trying to do fewer steps, not more!

推荐答案

遇到与您在问题中遇到的类似问题，我对此进行了一些研究.我的问题还没有完全解决，但我想我可以让你更进一步.虽然这个问题很老，但也许还有其他人需要帮助.

Having similar issues as you have in your question I have done some research on this. My issue is not yet fully resolved but I think I might get you one step further. Although the question is old there is perhaps someone else who needs the help.

通过运行:

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=P:\Path\File.xlsx','SELECT * FROM [Sheet1$]');
GO

我收到以下错误消息:

消息 15281，级别 16，状态 1，第 19 行SQL Server 阻止了对组件Ad Hoc Distributed Queries"的 STATEMENT 'OpenRowset/OpenDatasource' 的访问，因为该组件已作为此服务器的安全配置的一部分关闭.系统管理员可以使用 sp_configure 启用Ad Hoc Distributed Queries".有关启用Ad Hoc Distributed Queries"的详细信息，请在 SQL Server Books Online 中搜索Ad Hoc Distributed Queries".

Msg 15281, Level 16, State 1, Line 19 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', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

为了解决这个问题，我运行以下命令:

To resolve that I run the following:

sp_configure 'show advanced options', 1  
RECONFIGURE  
GO  
sp_configure 'ad hoc distributed queries', 1  
RECONFIGURE  
GO 

但我收到一条新的错误消息:

But I get a new error mesasge:

消息 7302，级别 16，状态 1，第 19 行无法为链接服务器(null)"创建 OLE DB 提供程序Microsoft.ACE.OLEDB.12.0"的实例.

Msg 7302, Level 16, State 1, Line 19 Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

为了纠正我运行:

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

但我得到了这个错误:

消息 7438，级别 16，状态 1，第 19 行32 位 OLE DB 提供程序Microsoft.ACE.OLEDB.12.0"无法在 64 位 SQL Server 上加载.

Msg 7438, Level 16, State 1, Line 19 The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

就我而言，我已经要求 IT 部门在服务器上安装 64 位版本的 excel，我希望这应该是从 excel 导入时的技术问题的结束.

In my case I have asked the IT department to install a 64 bit version of excel on the server and I hope that should be the end of the technical problems when importing from excel.

为了之后清理，我禁用了我刚刚启用的设置:

To clean up afterwards I disable the settings I just enabled:

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 0
GO

sp_configure 'ad hoc distributed queries', 0
RECONFIGURE  
GO

sp_configure 'show advanced options', 0  
RECONFIGURE  
GO  

这篇关于将 Excel 2010 导入 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！