27,579
社区成员
发帖
与我相关
我的任务
分享SQL SERVER 定时更新 EXCEL 文档
现在有一个项目需要我每小时 向我自己本机上的一个共享文件夹中的一个excel进行更新。要求不能创建新的文档，只能更新Sheet3$中的数据。
我遇到的困难是：
1. 尝试制作SSIS直接向其中插入新的数据。结果是旧的数据不覆盖，无限叠加。
2. 使用Openrowset直接操作excel。但是excel文件本身不支持delete和truncate操作。如果是单调递增的数据库还好，做一下except检查和update更新就好。如果是大幅度波动的文件，会产生大量过期数据。
3. 尝试使用update为null值。结果发现excel不会自动把null值作为空行处理。但是如果你把所有行全部update成空，再打开excel文件，选中一下A1 保存关闭，excel里的table就干净了。非常诡异
请各位老哥指点，我该怎么完成我的需求，也就是定期完美更新excel里的一个工作表。
（步骤为，清空这个表内的数据，保留所有column，导入全新的数据，数据格式不发生改变）
如果真的要删除这个文件也行 但是生成的excel文件名为B，表格名为Sheet3，这个不能发生改变。
我觉的这个需求大家是不是还挺经常遇到的？大家是怎么处理呢？
不考虑使用java或者.net语言进行操作，尽量仅使用sqlserver。如果需要使用powershell请上详细步骤，这个东西用的太少。
非常感谢
附：
这是我现在使用的语句，请参考路径于数据库名，表名。驱动使用ACE12.0 工作环境不支持JET4.0（发生不可预期的错误）
我遇到的困难是：
1. 尝试制作SSIS直接向其中插入新的数据。结果是旧的数据不覆盖，无限叠加。
2. 使用Openrowset直接操作excel。但是excel文件本身不支持delete和truncate操作。如果是单调递增的数据库还好，做一下except检查和update更新就好。如果是大幅度波动的文件，会产生大量过期数据。
3. 尝试使用update为null值。结果发现excel不会自动把null值作为空行处理。但是如果你把所有行全部update成空，再打开excel文件，选中一下A1 保存关闭，excel里的table就干净了。非常诡异
请各位老哥指点，我该怎么完成我的需求，也就是定期完美更新excel里的一个工作表。
（步骤为，清空这个表内的数据，保留所有column，导入全新的数据，数据格式不发生改变）
如果真的要删除这个文件也行 但是生成的excel文件名为B，表格名为Sheet3，这个不能发生改变。
我觉的这个需求大家是不是还挺经常遇到的？大家是怎么处理呢？
不考虑使用java或者.net语言进行操作，尽量仅使用sqlserver。如果需要使用powershell请上详细步骤，这个东西用的太少。
非常感谢
附：
这是我现在使用的语句，请参考路径于数据库名，表名。驱动使用ACE12.0 工作环境不支持JET4.0（发生不可预期的错误）
insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\fu\Desktop\B.xlsx',sheet3$)
select * from expire_pool
except
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\fu\Desktop\B.xlsx','select * from [sheet3$]')
go
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\fu\Desktop\B.xlsx','select * from [Sheet3$]')
--我没有继续往下写，实际上，上方可以加入update语句，以studentid作为主键，进行其他栏目的更新检查，这个没有问题
...全文
请发表友善的回复…
发表回复
繁花尽流年 2017-09-19
- 打赏
- 举报
这个要用脚本任务自己在编辑器里写脚本创建，也可以写脚本删除excel现有数据。最简单的方法，直接备份一个空模板每次从其他目录复制过来。这个不需要写脚本，文件流里就能操作
[/quote]用这个方法完美解决了 非常感谢[/quote]
解决了就结帖吧
我不叫康师傅 2017-09-19
- 打赏
- 举报
这个要用脚本任务自己在编辑器里写脚本创建，也可以写脚本删除excel现有数据。最简单的方法，直接备份一个空模板每次从其他目录复制过来。这个不需要写脚本，文件流里就能操作
[/quote]用这个方法完美解决了 非常感谢
繁花尽流年 2017-09-14
- 打赏
- 举报
文件不大老哥，可是具体该怎么做呢，方便的话能提供下代码吗[/quote]
生成数据就没啥好说的了，通过SSIS里的文件流就可以操作，移除，创建，复制excel你可以打开看一眼。
我不叫康师傅 2017-09-14
- 打赏
- 举报
文件不大老哥，可是具体该怎么做呢，方便的话能提供下代码吗
繁花尽流年 2017-09-14
- 打赏
- 举报
文件不大的话，可以考虑移除源文件到备份文件夹，把全量更新后的数据重新生成一个同名excel放回去
我不叫康师傅 2017-09-14
- 打赏
- 举报
我有考虑过反向解决这个问题，也就是用excel直接连接SQLserver 的table。
问题是excel这个倒霉孩子，自己不支持定时刷新table。使用vba的话，你把excel关了vba就失效，你把excel开着别的程序也不能load。很烦。。。。
如果有大神知道反向解决怎么定时刷新table，还请赐教
OwenZeng_DBA 2017-09-14
- 打赏
- 举报
会写程序吗，如果会的话，写程序完成更合适一些吧
听雨停了 2017-09-14
- 打赏
- 举报
/*--数据导出EXCEL
导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
如果文件不存在,将自动创建文件
如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
*/
/*--调用示例
p_exporttb @sqlstr='select * from 地区资料'
,@path='c:\',@fname='aa.xls',@sheetname='地区资料'
--*/
create proc p_exporttb
@sqlstr varchar(8000),--查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000),--文件存放目录
@fname nvarchar(250),--文件名
@sheetname varchar(250)=''--要创建的工作表名,默认为文件名
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
--参数检测
if isnull(@fname,'')=''set @fname='temp2012.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')
--检查文件是否已经存在
if right(@path,1)<>'\' set @path=@path+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
--数据库创建语句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'+@sql+'";DBQ='+@sql
else
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
+';DATABASE='+@sql+'"'
--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr
exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr
--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)
select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
,@sql=@sql+',['+a.name+'] '
+case
when b.name like '%char'
then case when a.length>255 then 'memo'
else 'text('+cast(a.length as varchar)+')' end
when b.name like '%int' or b.name='bit' then 'int'
when b.name like '%datetime' then 'datetime'
when b.name like '%money' then 'money'
when b.name like '%text' then 'memo'
else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)
if @@rowcount=0 return
select @sql='create table ['+@sheetname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr
exec @err=sp_oadestroy @obj
--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@sheetname+'$])'
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')
set @sql='drop table ['+@tbname+']'
exec(@sql)
return
lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
go
--为了执行这个存储过程，你得先打开以下两个开关：
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO
--调用实例
p_exporttb @sqlstr='select * from [Demo_A]'
,@path='c:\',@fname='Export2xls_ByProc.xls',@sheetname='员工名称'
繁花尽流年 2017-09-14
- 打赏
- 举报
这个要用脚本任务自己在编辑器里写脚本创建，也可以写脚本删除excel现有数据。最简单的方法，直接备份一个空模板每次从其他目录复制过来。这个不需要写脚本，文件流里就能操作
我不叫康师傅 2017-09-14
- 打赏
- 举报
[quote=引用 9 楼 zengertao 的回复:]
老哥这个我是知道的，可是这个目标必须是已经存在的，假如我用file system task吧上一次的文件删掉，这里会报错，指定的路径不存在。这里不会自动创建excel文档的 我已经试了很多次了
繁花尽流年 2017-09-14
- 打赏
- 举报
文件不大老哥，可是具体该怎么做呢，方便的话能提供下代码吗[/quote]
生成数据就没啥好说的了，通过SSIS里的文件流就可以操作，移除，创建，复制excel你可以打开看一眼。
[/quote]这个方法删除文件成功了，但是我没找到创建文件，请问一下想创建一个新的空excel，控制流要配置成什么形状呢？
（需要一个空excel是因为，导出功能需要指定一个现有的excel，不能凭空创建。
当然，采用BCP创建确实是可行的，但会有两个问题，第一没有列名，第二，想保持数据格式只能采用text，如果使用xls有一些特殊数据会被转换从日期格式，这个是excel一直以来的问题，比如某个家庭编号是MAY516，excel自动识别成5月什么什么，非常讨厌）
再次感谢您的帮助。[/quote]
创建文件直接把你sql里查到的结果集输出到excel目标里不就有了嘛[/quote]
数据流里做的[/quote]
可是这个地方不能生成excel，只能选择现有的excel呀[/quote]
我不叫康师傅 2017-09-14
- 打赏
- 举报
文件不大老哥，可是具体该怎么做呢，方便的话能提供下代码吗[/quote]
生成数据就没啥好说的了，通过SSIS里的文件流就可以操作，移除，创建，复制excel你可以打开看一眼。
[/quote]这个方法删除文件成功了，但是我没找到创建文件，请问一下想创建一个新的空excel，控制流要配置成什么形状呢？
（需要一个空excel是因为，导出功能需要指定一个现有的excel，不能凭空创建。
当然，采用BCP创建确实是可行的，但会有两个问题，第一没有列名，第二，想保持数据格式只能采用text，如果使用xls有一些特殊数据会被转换从日期格式，这个是excel一直以来的问题，比如某个家庭编号是MAY516，excel自动识别成5月什么什么，非常讨厌）
再次感谢您的帮助。[/quote]
创建文件直接把你sql里查到的结果集输出到excel目标里不就有了嘛[/quote]
数据流里做的[/quote]
可是这个地方不能生成excel，只能选择现有的excel呀
繁花尽流年 2017-09-14
- 打赏
- 举报
文件不大老哥，可是具体该怎么做呢，方便的话能提供下代码吗[/quote]
生成数据就没啥好说的了，通过SSIS里的文件流就可以操作，移除，创建，复制excel你可以打开看一眼。
[/quote]
这个方法删除文件成功了，但是我没找到创建文件，请问一下想创建一个新的空excel，控制流要配置成什么形状呢？
（需要一个空excel是因为，导出功能需要指定一个现有的excel，不能凭空创建。
当然，采用BCP创建确实是可行的，但会有两个问题，第一没有列名，第二，想保持数据格式只能采用text，如果使用xls有一些特殊数据会被转换从日期格式，这个是excel一直以来的问题，比如某个家庭编号是MAY516，excel自动识别成5月什么什么，非常讨厌）
再次感谢您的帮助。[/quote]
创建文件直接把你sql里查到的结果集输出到excel目标里不就有了嘛[/quote]
数据流里做的
[/quote]
这个方法删除文件成功了，但是我没找到创建文件，请问一下想创建一个新的空excel，控制流要配置成什么形状呢？
（需要一个空excel是因为，导出功能需要指定一个现有的excel，不能凭空创建。
当然，采用BCP创建确实是可行的，但会有两个问题，第一没有列名，第二，想保持数据格式只能采用text，如果使用xls有一些特殊数据会被转换从日期格式，这个是excel一直以来的问题，比如某个家庭编号是MAY516，excel自动识别成5月什么什么，非常讨厌）
再次感谢您的帮助。[/quote]
创建文件直接把你sql里查到的结果集输出到excel目标里不就有了嘛[/quote]
数据流里做的繁花尽流年 2017-09-14
- 打赏
- 举报
文件不大老哥，可是具体该怎么做呢，方便的话能提供下代码吗[/quote]
生成数据就没啥好说的了，通过SSIS里的文件流就可以操作，移除，创建，复制excel你可以打开看一眼。
[/quote]
这个方法删除文件成功了，但是我没找到创建文件，请问一下想创建一个新的空excel，控制流要配置成什么形状呢？
（需要一个空excel是因为，导出功能需要指定一个现有的excel，不能凭空创建。
当然，采用BCP创建确实是可行的，但会有两个问题，第一没有列名，第二，想保持数据格式只能采用text，如果使用xls有一些特殊数据会被转换从日期格式，这个是excel一直以来的问题，比如某个家庭编号是MAY516，excel自动识别成5月什么什么，非常讨厌）
再次感谢您的帮助。[/quote]
创建文件直接把你sql里查到的结果集输出到excel目标里不就有了嘛
[/quote]
这个方法删除文件成功了，但是我没找到创建文件，请问一下想创建一个新的空excel，控制流要配置成什么形状呢？
（需要一个空excel是因为，导出功能需要指定一个现有的excel，不能凭空创建。
当然，采用BCP创建确实是可行的，但会有两个问题，第一没有列名，第二，想保持数据格式只能采用text，如果使用xls有一些特殊数据会被转换从日期格式，这个是excel一直以来的问题，比如某个家庭编号是MAY516，excel自动识别成5月什么什么，非常讨厌）
再次感谢您的帮助。[/quote]
创建文件直接把你sql里查到的结果集输出到excel目标里不就有了嘛我不叫康师傅 2017-09-14
- 打赏
- 举报
文件不大老哥，可是具体该怎么做呢，方便的话能提供下代码吗[/quote]
生成数据就没啥好说的了，通过SSIS里的文件流就可以操作，移除，创建，复制excel你可以打开看一眼。
[/quote]
这个方法删除文件成功了，但是我没找到创建文件，请问一下想创建一个新的空excel，控制流要配置成什么形状呢？
（需要一个空excel是因为，导出功能需要指定一个现有的excel，不能凭空创建。
当然，采用BCP创建确实是可行的，但会有两个问题，第一没有列名，第二，想保持数据格式只能采用text，如果使用xls有一些特殊数据会被转换从日期格式，这个是excel一直以来的问题，比如某个家庭编号是MAY516，excel自动识别成5月什么什么，非常讨厌）
再次感谢您的帮助。
[/quote]
这个方法删除文件成功了，但是我没找到创建文件，请问一下想创建一个新的空excel，控制流要配置成什么形状呢？
（需要一个空excel是因为，导出功能需要指定一个现有的excel，不能凭空创建。
当然，采用BCP创建确实是可行的，但会有两个问题，第一没有列名，第二，想保持数据格式只能采用text，如果使用xls有一些特殊数据会被转换从日期格式，这个是excel一直以来的问题，比如某个家庭编号是MAY516，excel自动识别成5月什么什么，非常讨厌）
再次感谢您的帮助。