无法使用 openrowset 函数将数据从 excel 2003 导入数据库 [英] Cannot import data from excel 2003 to database using openrowset function

查看：33
本文介绍了无法使用 openrowset 函数将数据从 excel 2003 导入数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

这是我的笔记本电脑规格:

操作系统:Windows 7 - 64 位，数据库:SQL SERVER 2008 R2，微软Office:微软Office 2007，

我的问题是:当我运行程序将数据从 excel 2003 (xls) 导入数据库 (SQL Server 2008) 时，出现此错误:

OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 不能用于分布式查询，因为提供程序被配置为运行单线程单元模式.</span>

这是我的程序:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:	est.xls','SELECT * FROM [Sheet1$]')

我尝试修复:

安装 AccessDatabaseEngine.exe

在 SQL 下面运行:

EXEC sp_configure 'Ad Hoc 分布式查询', 1;走EXEC sp_configure '显示高级选项', 1;走EXEC sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1走EXEC sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1

，但我仍然收到错误消息.有人可以帮我吗?

解决方案

OPENROWSET 函数的 SQL 查询 :--

1) 打开 SQL Server Management Studio

2) 打开查询板并写入以下命令

3) 对于 Excel 97 – 2003 文件，即扩展名为 XLS 的文件使用

SELECT * INTO [dbo].[地址]FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:SQL Scriptsmsp.xls;IMEX=1;HRD=Yes','SELECT * FROM [Sheet1$]')

我.它将在当前选定的数据库中创建名为 Addresses 的表.

二.Microsoft.Jet.OLEDB.4.0 是用于转换的驱动程序

三.带有路径的 Excel 文件 - D:SQL Scriptsmsp.xls

四.包含 IMEX=1 属性，包含混合数据类型的列被视为字符串/文本数据类型.

v.HRD =Yes 属性表示excel文件的第一行由列标题名称组成

六.Sheet1 是要导入的工作表的名称

七.Excel 8.0 指定为97-2003格式的excel文件

4) 要使用过滤查询，用户也可以在此命令中使用 where 子句，例如

SELECT * INTO [dbo].[地址]FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:SQL Scriptsmsp.xls;IMEX=1;HRD=Yes','SELECT * FROM [Sheet1$]') where [column_name]='值'

5) 要复制预定义 SQL 表中的 Excel 文件，请使用 OPENROWSET 函数和插入命令，例如:-

创建表自定义 (Source_IP_ADD varchar(20),API_NAME varchar(50),COUNT_NO varchar(5),CLIENT_ID varchar(50),Date_OF_INVOKE varchar(50))INSERT INTO [dbo].[Custom] ( [Source_IP_ADD], [API_NAME], [COUNT_NO], [CLIENT_ID], [Date_OF_INVOKE])SELECT [Source_IP_ADDR], [API_NAME], [COUNT_NO], [CLIENT_ID], [Date_INVOK] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:SQL Scriptsmsp.xls;IMEX=1;HRD=Yes','SELECT * FROM [Sheet1$]')

6) 对于 Excel 2007 – 2010 文件，使用扩展名为 XLSX 的文件

选择 *INTO [dbo].[地址]FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0' ,'Excel 12.0;Database=D:SQL Scriptsmsp.xlsx;HDR=YES;IMEX=1','SELECT * FROM [Sheet1$]')

我.它将在当前选定的数据库中创建名为 Addresses 的表

二.Microsoft.ACE.OLEDB.12.0 是用于转换的驱动程序

三.带有路径的 Excel 文件 - D:SQL Scriptsmsp.xlsx

四.包含 IMEX=1 属性，包含混合数据类型的列被视为字符串/文本数据类型.

v.HRD =Yes 属性表示excel文件的第一行由列标题名称组成

六.Sheet1 是要导入的工作表的名称

七.Excel 12.0指定为2007-2010格式的excel文件

7) 要复制预定义 SQL 表中的 excel 文件，请使用 OPENROWSET 函数和插入命令，例如:-

创建表自定义 (Source_IP_ADD varchar(20),API_NAME varchar(50),COUNT_NO varchar(5),CLIENT_ID varchar(50),Date_OF_INVOKE varchar(50))INSERT INTO [dbo].[Custom] ( [Source_IP_ADD], [API_NAME], [COUNT_NO], [CLIENT_ID], [Date_OF_INVOKE])SELECT [Source_IP_ADDR], [API_NAME], [COUNT_NO], [CLIENT_ID], [Date_INVOK] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:SQL Scriptsmsp.xlsx;HDR=YES;IMEX=1' ,'SELECT * FROM [Sheet1$]')

This is my laptop spec :

OS : Windows 7 - 64bit , Database : SQL SERVER 2008 R2 , Microsoft Office : Microsoft Office 2007,

My problem is : When I run my procedure to import data from excel 2003 (xls) to database (SQL Server 2008) , I've got this error :

<span style="color:red">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.</span>

this is my procedure :

SELECT * FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:	est.xls', 
                          'SELECT * FROM [Sheet1$]')

I've try to fix with :

Install AccessDatabaseEngine.exe

Run below on SQL:

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
EXEC sp_configure 'show advanced options', 1;
GO
EXEC sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1

, but I still get the error. Can somebody help me?

解决方案

SQL Query for OPENROWSET function :--

1) Open SQL Server Management Studio

2) Open the query pad and write the following commands

3) For Excel 97 – 2003 file that is files with extension XLS use

SELECT * INTO [dbo].[Addresses]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:SQL Scriptsmsp.xls;IMEX=1;HRD=Yes','SELECT * FROM [Sheet1$]')

i. It will create table with name Addresses in the current selected database.

ii. Microsoft.Jet.OLEDB.4.0 is the driver use for conversion

iii. Excel file with path - D:SQL Scriptsmsp.xls

iv. IMEX=1 property included, columns that contain intermixed data types are treated as string/text data types.

v. HRD =Yes property means the top row of excel file consists of Column Header name

vi. Sheet1 is the name of the sheet you want to import

vii. Excel 8.0 specifies that it is 97 – 2003 format excel file

4) To use filter query the user can use the where clause also with this command like

SELECT * INTO [dbo].[Addresses]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:SQL Scriptsmsp.xls;IMEX=1;HRD=Yes','SELECT * FROM [Sheet1$]') where [column_name]=’value’

5) To copy the excel file in predefine SQL table use OPENROWSET function with insert command like:-

Create table Custom (Source_IP_ADD varchar(20),API_NAME varchar(50),COUNT_NO varchar(5),CLIENT_ID varchar(50),Date_OF_INVOKE varchar(50))

INSERT INTO [dbo].[Custom] ( [Source_IP_ADD], [API_NAME], [COUNT_NO], [CLIENT_ID], [Date_OF_INVOKE])

    SELECT [Source_IP_ADDR], [API_NAME], [COUNT_NO], [CLIENT_ID], [Date_INVOK] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:SQL Scriptsmsp.xls;IMEX=1;HRD=Yes','SELECT * FROM [Sheet1$]')

6) For Excel 2007 – 2010 file that is files with extension XLSX use

SELECT * INTO [dbo].[Addresses] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0' ,'Excel 12.0;Database=D:SQL Scriptsmsp.xlsx;HDR=YES;IMEX=1' ,'SELECT * FROM [Sheet1$]')

i. It will create table with name Addresses in the current selected database

ii. Microsoft.ACE.OLEDB.12.0 is the driver use for conversion

iii. Excel file with path - D:SQL Scriptsmsp.xlsx

iv. IMEX=1 property included, columns that contain intermixed data types are treated as string/text data types.

v. HRD =Yes property means the top row of excel file consists of Column Header name

vi. Sheet1 is the name of the sheet you want to import

vii. Excel 12.0 specifies that it is 2007 – 2010 format excel file

7) To copy the excel file in predefine SQL table use OPENROWSET function with insert command like:-

Create table Custom (Source_IP_ADD varchar(20),API_NAME varchar(50),COUNT_NO varchar(5),CLIENT_ID varchar(50),Date_OF_INVOKE varchar(50))

INSERT INTO [dbo].[Custom] ( [Source_IP_ADD], [API_NAME], [COUNT_NO], [CLIENT_ID], [Date_OF_INVOKE])
SELECT [Source_IP_ADDR], [API_NAME], [COUNT_NO], [CLIENT_ID], [Date_INVOK] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0' ,'Excel 12.0;Database=D:SQL Scriptsmsp.xlsx;HDR=YES;IMEX=1' ,'SELECT * FROM [Sheet1$]')

这篇关于无法使用 openrowset 函数将数据从 excel 2003 导入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆