有什么办法可以写出选择sql查询，它将从excel工作表中获取值并在sql窗口中显示结果 [英] Is there any way to write select sql query which will take the value from excel sheet and display the result in sql window

问题描述

我有一张Excel工作表(名称为:-LTATestData.xls).它包含一些具有相应值的列.我想选择从Excel工作表读取列值并显示结果的sql查询.

I have one excel sheet(name as:-LTATestData.xls). It contain some column with corresponding some value. I want select sql query which read the column value from excel sheet and display the result.

我试图在google中搜索，但出现以下查询，这可能会解决我的目的

I have tried to search in google, i get below query which may solve my purpose

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
            'Excel 10.0;Database=C:\work\LTATestData.xls',
            'SELECT * FROM [Sheet1$]') 

但是，当我运行此查询时，我收到以下错误消息:-"OLE DB提供程序'Microsoft.Jet.OLEDB.4.0'无法用于分布式查询，因为该提供程序配置为以单线程单元模式运行."

But when I run this query I am getting below error message:- "OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode."

请让我知道如何执行此操作.预先感谢.

Please let me know how to do this. Thanks in advance.

推荐答案

主要原因可能是由于64位SQL Server和32位Microsoft Office安装之间不匹配.因此，与其将SQL Server版本降级，不如升级到64位Microsoft Office.

The main cause for this might be because of a mismatch between a 64-bit SQL Server and a 32-bit Microsoft Office installation. Therefore rather upgrade to a 64-bit Microsoft Office than downgrade a sql server version.

或者，如果您的版本是最新的，这里有几篇文章(请同时阅读评论部分)

Alternatively, here a couple of posts if your versions are all up to date (Please read the comments sections as well)

以下帖子可能有助于解决问题:

The following posts might be helpful solving the problem: HOW TO: FIX ERROR - "the 'microsoft.ace.oledb.12.0' provider is not registered on the local machine"

AND

查看全文