我写了个读excel的,可不知道怎么能读excel下多个sheet的情况
select *
from openrowset('MicroSoft.Jet.OleDB.4.0',
'Excel 8.0;HDR=yes;imex=1;Database=d:\test.xls',
sheet1$
)
select *
from openrowset('MicroSoft.Jet.OleDB.4.0',
'Excel 8.0;HDR=yes;imex=1;Database=d:\test.xls',
sheet1$
)
再循环读取.
exec sp_addlinkedserver @server='Excel_Source',
@srvproduct='Microsoft.Jet.OleDb.4.0 for Excel',
@provider='Microsoft.Jet.OleDb.4.0',
@datasrc='g:\txt\Bom.xls',
@location=null,
@provstr='Excel 5.0'
exec sp_addlinkedsrvlogin @rmtsrvname='Excel_Source',
@useself='false'create table t(TABLE_CAT varchar(128) null,TABLE_SCHEMA sysname null,
TABLE_NAME sysname null,TABLE_TYPE varchar(32) null,REMARKS varchar(254) null)insert into t(TABLE_CAT,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,REMARKS)
exec sp_tables_ex 'Excel_Source'declare cur cursor fast_forward for
select TABLE_NAME from t where right(TABLE_NAME,1)='$'declare @tbname sysname
open cur
fetch next from cur into @tbname
while @@fetch_status=0
begin
exec ('select * from [Excel_Source]...'+@tbname)
fetch next from cur into @tbname
end
close cur
deallocate curexec sp_dropserver @server='Excel_Source',@droplogins='droplogins'drop table t
EXEC sp_addlinkedserver 'Excel Source', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test2003.xls',
NULL,
'Excel 4.0'
EXEC sp_addlinkedsrvlogin 'Excel Source', 'false', 'sa', 'Admin', null
go
sp_tables_ex 'Excel Source' --获得sheet列表
go
SELECT * FROM [Excel Source]...sheet1$ --循环读取sheet
go
EXEC sp_dropserver N'Excel Source' , N'droplogins'
服务器: 消息 7415,水平 16,状态1,行1OLE
DB 服务提供商 'MicroSoft.Jet.OleDB.对4.0'的ad hook 访问被拒绝了。请使用链接 服务器这个服务提供商访问。
不懂
能帮忙详细解释一下吗