问题描述
我想使用sql server 2008将Excel工作表数据导入表中
让我觉得这很容易...所以
1.我用临时表中的列标题创建了一个名为testfile.xls的Excel工作表
2.保存并关闭此xls
3.尝试运行以下内容:
USE [MainAdmin];
GO
从OPENROWSET(``Microsoft.Jet.OLEDB.4.0''，``Excel 8.0; Database = E:\ RAJ Infotech \ testfile.xls; HDR = YES''，``select * from [Sheet1 $]'')AS A; SELECT * FROM MainAdmin.dbo.Table_1
GO
E:\ Raj Infotech \ testfile.xls是我保存test.xls的位置，Table_1是我首先填充的表，MainAdmin是数据库名.
当我运行此命令时，出现以下错误:
链接服务器(null)"的OLE DB提供程序"Microsoft.Jet.OLEDB.4.0"返回消息"Microsoft Jet数据库引擎找不到对象"Sheet1 $".请确保该对象存在并且您拼写了该对象名称和正确的路径名称.".
消息7350，第16级，状态2，第1行
无法从链接服务器(null)"的OLE DB提供程序"Microsoft.Jet.OLEDB.4.0"获取列信息.
在运行这些导出"查询中的任何一个之前，我先运行以下命令:
EXEC sp_configure``显示高级选项''，1;
GO
重新配置；
GO
EXEC sp_configure``临时分布式查询''，1;
GO
重新配置；
GO
产生的结果:
配置选项显示高级选项"从0更改为1.运行RECONFIGURE语句进行安装.
配置选项"Ad Hoc分布式查询"从0更改为1.运行RECONFIGURE语句进行安装.
我以为我已经成功启用了Ad Hoc分布式查询-正确吗?
I want to import excel sheet data in to table using sql server 2008
Made me think this would be quite easy... so
1. I created a Excel Sheet named testfile.xls with the column headings from my temp table
2. Saved and closed this xls
3. Tried to run the following:
USE [MainAdmin];
GO
Insert into Table_1(Id,FirstName,LastName) SELECT A.[Id], A.[FirstName], A.[LastName] FROM OPENROWSET (''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=E:\RAJ Infotech\testfile.xls;HDR=YES'', ''select * from [Sheet1$]'') AS A ; SELECT * FROM MainAdmin.dbo.Table_1
GO
Where E:\Raj Infotech\testfile.xls is where I saved test.xls, Table_1 is the table I have populated in the firstplace and MainAdmin is the database name.
When I run this the following error crops up:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object ''Sheet1$''. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Before running either of these ''export'' queries I run following :
EXEC sp_configure ''show advanced options'', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ''Ad Hoc Distributed Queries'', 1;
GO
RECONFIGURE;
GO
Which produced results:
Configuration option ''show advanced options'' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ''Ad Hoc Distributed Queries'' changed from 0 to 1. Run the RECONFIGURE statement to install.
I assumed from that I had successfully enabled Ad Hoc Distributed Queries - Is that correct?
推荐答案
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test\xltestt.xls;" & _
"Extended Properties=Excel 8.0"
'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=<server>;Database=<database>;" & _
"UID=<user>;PWD=<password>].XLImport9 " & _
"FROM [Customers
这篇关于如何使用SQL Server 2008将Excel工作表数据导入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！