--
将文件夹下的所有EXCEL导入SQL数据库中,每一文件为一单独的表,表名为文件名
-- By MadGoat 2006-11-23
create table #t (fname varchar ( 260 ),depth int ,isf bit )
insert into #t exec master..xp_dirtree ' c:\test\ ' , 1 , 1
declare tb cursor for select fn = ' c:\test\ ' + fname from #t
where isf = 1 and fname like ' %.xls ' -- 取.xls文件(EXCEL)
declare @fn varchar ( 8000 )
declare @fname varchar ( 8000 )
declare @end int -- 截取的结束位置
declare @fnamelen int -- 文件名称长度
open tb
fetch next from tb into @fn
while @@fetch_status = 0
begin
set @end = len ( @fn ) - PATINDEX ( ' %\% ' , reverse ( @fn ))
set @fnamelen = len ( @fn ) - 5 - @end
set @fname = SUBSTRING ( @fn , @end + 2 , @fnamelen )
set @fn = ' SELECT * into ' + @fname + ' FROM OPENDATASOURCE( '' MICROSOFT.JET.OLEDB.4.0 '' ,
'' Excel 5.0;DATABASE= ' + @fn + ''' )[Sheet1$] ' -- 关键是这句
exec ( @fn )
fetch next from tb into @fn
end
close tb
deallocate tb
drop table #t
-- By MadGoat 2006-11-23
create table #t (fname varchar ( 260 ),depth int ,isf bit )
insert into #t exec master..xp_dirtree ' c:\test\ ' , 1 , 1
declare tb cursor for select fn = ' c:\test\ ' + fname from #t
where isf = 1 and fname like ' %.xls ' -- 取.xls文件(EXCEL)
declare @fn varchar ( 8000 )
declare @fname varchar ( 8000 )
declare @end int -- 截取的结束位置
declare @fnamelen int -- 文件名称长度
open tb
fetch next from tb into @fn
while @@fetch_status = 0
begin
set @end = len ( @fn ) - PATINDEX ( ' %\% ' , reverse ( @fn ))
set @fnamelen = len ( @fn ) - 5 - @end
set @fname = SUBSTRING ( @fn , @end + 2 , @fnamelen )
set @fn = ' SELECT * into ' + @fname + ' FROM OPENDATASOURCE( '' MICROSOFT.JET.OLEDB.4.0 '' ,
'' Excel 5.0;DATABASE= ' + @fn + ''' )[Sheet1$] ' -- 关键是这句
exec ( @fn )
fetch next from tb into @fn
end
close tb
deallocate tb
drop table #t