 [SQLSERVER]excel导入sqlserver |
|
/*说明
1.如果是通sqlserver来获取sheetname，有可能会出现 sheet1$,sheet1$_,sheet2$,sheet3$的问题，这里把sheet1$_过滤掉 产生的原因，可能是通过sqlserver,access导出excel功能，可能会有上面的问题2.一些特殊字符会使得工作表多产两个''，例如 工作表名 a-0,-> 'a-0' 0800 -> '0800'3.有些excel的column会有几种可能 1>字符型 2>字符型 + 数字符 3>数字符 前两种是能过IMEX=1参数来混合类型为字符型, 数字符则通过查看列的类型 56为int,62为float*/
ALTER proc input_excel@file varchar(200) = '',@flag bit =0, --不指定工作表，如果为0则指定工作表@w varchar(50)= '', --工作表名@format varchar(60) ='%13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'as
declare @jet varchar(200)declare @sql nvarchar(500)declare @workarea varchar(50)
set nocount on
insert into file_relation(filename) values(@file) --先插入file_relation表，filenameid为文件id
if exists(select * from tempdb.dbo.sysobjects where name = '##t' and xtype = 'U')begin drop table ##tend
--create table #temp (name sysname)declare @temp table(name sysname)
if @flag = 1begin declare @srv_name sysname,@st nvarchar(4000) select @srv_name=cast(newid() as char(36)) ,@st='Excel 8.0;IMEX=1;HDR=NO;DATABASE='+@file exec sp_addlinkedserver @server=@srv_name,@srvproduct='' ,@provider='MICROSOFT.JET.OLEDB.4.0',@provstr=@st create table #t(CAT sysname null,SCHEM sysname null,NAME sysname,TYPE varchar(32),REMARKS varchar(254)) insert #t exec sp_tables_ex @srv_name exec sp_dropserver @srv_name,'droplogins' insert into column_relation(fileid,filecolumn) select dbo.return_fileid(@file),substring(name,1,charindex('$',name)-1)+'$' as name from #t where type = 'TABLE' group by substring(name,1,charindex('$',name)-1)+'$' --插入column_relation表,filecolumnid 为列id insert into @temp select substring(name,1,charindex('$',name)-1)+'$' as name from #t where type = 'TABLE' group by substring(name,1,charindex('$',name)-1)+'$' --1.说明endelsebegin insert into column_relation(fileid,filecolumn) values(dbo.return_fileid(@file),@w) --插入column_relation表,filecolumnid 为列id insert into @temp values(@w)end--得到excel工作表 放于#temp表中
declare y_cur_worktablename cursor for select name from @tempopen y_cur_worktablename
fetch next from y_cur_worktablename into @workareawhile @@FETCH_STATUS = 0begin
set @jet = '''Excel 8.0;IMEX=1;HDR=NO;DATABASE=' + @file + '''' if (left(@workarea,1) = '''') or (left(@workarea,1) like '[0-9]')--2.说明 begin set @workarea = replace(@workarea,'''','') set @sql = 'select * into ##t from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'','+@jet+',['''+ @workarea +'''])' end else begin set @sql = 'select * into ##t from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'','+@jet+',['+ @workarea +'])' end exec sp_executesql @sql --以上是将excel插入##t表中 ,同时也统一类型为字符型 declare @a int declare @name varchar(100) declare @s varchar(300) select @a = id from tempdb.dbo.sysobjects where name = '##t' declare y_cur cursor for select name from tempdb.dbo.syscolumns where id = @a open y_cur fetch next from y_cur into @name while @@FETCH_STATUS =0 begin declare @i tinyint select @i = xusertype from tempdb.dbo.syscolumns where id = @a and name = @name if @i != 56 and @i != 62 --3.说明 begin --该列非数字列 set @s = 'select '+cast(dbo.return_fileid(@file) as varchar(10))+',' + cast(dbo.return_columnid(@workarea) as varchar(10))+ ','+@name+' from ##t where ' + @name + ' is not null and ' +@name + ' like ''' + @format + '''' end else begin set @s = 'select '+cast(dbo.return_fileid(@file) as varchar(10))+',' + cast(dbo.return_columnid(@workarea) as varchar(10)) + ',cast('+@name+' as bigint) from ##t where ' + @name + ' is not null and ' +@name + ' between 13000000000 and 13999999999' end
insert into content(filenameid,filecolumnid,body) exec(@s) FETCH NEXT FROM y_cur INTO @name end CLOSE y_cur DEALLOCATE y_cur drop table ##t
FETCH NEXT FROM y_cur_worktablename INTO @workareaend
CLOSE y_cur_worktablenameDEALLOCATE y_cur_worktablename
set nocount off
要装mdac2.8,修改2005-4-15 | |
|
|
|
