无法使用openrowset函数将数据从excel 2003导入数据库 [英] Cannot import data from excel 2003 to database using openrowset function
问题描述
这是我的笔记本电脑规格:
OS:Windows 7-64位， 数据库:SQL SERVER 2008 R2， Microsoft Office:Microsoft Office 2007，
我的问题是:当我运行将数据从excel 2003(xls)导入数据库(SQL Server 2008)时，出现此错误:
<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>
这是我的程序:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\test.xls',
'SELECT * FROM [Sheet1$]')
我尝试使用:
进行修复安装AccessDatabaseEngine.exe
在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
，但仍然出现错误.有人可以帮我吗?
SQL查询OPENROWSET函数:-
1)打开SQL Server Management Studio
2)打开查询板并编写以下命令
3)对于扩展名为XLS的Excel 97 – 2003文件使用
SELECT * INTO [dbo].[Addresses]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\SQL Scripts\msp.xls;IMEX=1;HRD=Yes','SELECT * FROM [Sheet1$]')
i.它将在当前选定的数据库中创建名称为Addresses的表.
ii. Microsoft.Jet.OLEDB.4.0是用于转换的驱动程序
iii.带有路径的Excel文件-D:\ SQL Scripts \ msp.xls
iv.包含IMEX = 1属性，将包含混合数据类型的列视为字符串/文本数据类型.
v. HRD = Yes属性表示excel文件的第一行由列标题名称组成
vi. Sheet1是您要导入的工作表的名称
vii. Excel 8.0指定它是97 – 2003格式的excel文件
4)要使用过滤器查询，用户也可以在此命令中使用where子句，例如
SELECT * INTO [dbo].[Addresses]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\SQL Scripts\msp.xls;IMEX=1;HRD=Yes','SELECT * FROM [Sheet1$]') where [column_name]=’value’
5)要在预定义的SQL表中复制excel文件，请使用带有插入命令的OPENROWSET函数，例如:-
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 Scripts\msp.xls;IMEX=1;HRD=Yes','SELECT * FROM [Sheet1$]')
6)对于扩展名为XLSX的Excel 2007 – 2010文件使用
选择* INTO [dbo].[地址] 从OPENROWSET('Microsoft.ACE.OLEDB.12.0'，'Excel 12.0; Database = D:\ SQL Scripts \ msp.xlsx; HDR = YES; IMEX = 1'，'SELECT * FROM [Sheet1 $]')
i.它将在当前所选数据库中创建名称为Addresses的表
ii. Microsoft.ACE.OLEDB.12.0是用于转换的驱动程序
iii.带有路径的Excel文件-D:\ SQL Scripts \ msp.xlsx
iv.包含IMEX = 1属性，将包含混合数据类型的列视为字符串/文本数据类型.
v. HRD = Yes属性表示excel文件的第一行由列标题名称组成
vi. Sheet1是您要导入的工作表的名称
vii. Excel 12.0指定它是2007 – 2010格式的excel文件
7)要在预定义的SQL表中复制excel文件，请使用带有插入命令的OPENROWSET函数，例如:-
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 Scripts\msp.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:\test.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 Scripts\msp.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 Scripts\msp.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 Scripts\msp.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 Scripts\msp.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 Scripts\msp.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 Scripts\msp.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 Scripts\msp.xlsx;HDR=YES;IMEX=1' ,'SELECT * FROM [Sheet1$]')
这篇关于无法使用openrowset函数将数据从excel 2003导入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
