用SQL SERVER对EXCEL数据进行处理
2013-04-11 11:21 Andy Ge 阅读(799) 评论(0) 编辑 收藏 举报GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE;
GO
SELECT * INTO wuli FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\wuli.xls', [Sheet1$])
如果是*.xlsx格式,需要另存为03格式*.xls。
可以把Excel的数据导入到数据库,然后用数据库进行条件查询,将查询结果拷贝并黏贴到新的EXCEL即可。
如果再导入过程中出现错误,如'server (null)....”类似的信息,重新启动一下SQLSERVER Service就好了。
例如上面的物理成绩表,需要单独保存每个学校统的指定字段数据到EXCEL,这时候,手工拷贝就很麻烦了,这时侯,也可以通过SQL来实现。
这种方式的基本原理其实是利用了BCP命令,将查询数据导出EXCEL,有两种方式可以执行这些BCP语句,
1.通过master..xp_cmdShell,这种方式存在一个问题,就是其参数类型为'sysname',但是只有255个字符长度,对于较长的查询,显然是不够的。
2.把这些BCP语句拷贝到批处理文件中,然后执行,BCP的参数没有长度的限制,所有可以执行较长的查询。
在执行'xp_cmdShell'之前,需要先打开开关语句如下:
go
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
go
RECONFIGURE;
采用xp_cmdShell执行,如下:
--(
-- id int identity,
-- name varchar(50)
--)
--insert into #tt
--select distinct [学校] from wz
--truncate table #tt
declare @total int
select @total = MAX(id) from #tt
declare @i int = 1,@schoolName varchar(50),@sql sysname = ''
declare @colNames varchar(max) = '*'
while(@i <=@total)
begin
select @schoolName = name from #tt where ID=@i
set @i += 1
set @sql = 'bcp "SELECT ' + @colNames + ' FROM demo..wz where [学校]=''' + @schoolName + '''" queryout C:\wk\wz' + @schoolName +'.xls -c -S . -U sa -P hx123456'
EXEC master..xp_cmdshell @sql
end
下面的例子演示了如果生成BCP语句,将这些语句拷贝到新建的批处理文件中,执行即可。
获取一个表的所有列:
select @colNames += '[' + name + '],' from sys.columns where object_id=object_id('wx') --and name like '%答案%'
print @colNames
select @total = MAX(id) from #tt
declare @i int = 1,@schoolName varchar(50),@sql varchar(max) = ''
declare @colNames varchar(max) = '[考生号],[姓名],[总分],[政治选择题25答案],[政治选择题26答案],[政治选择题27答案],[政治选择题28答案],[政治选择题29答案],[政治选择题30答案],[政治选择题31答案],[政治选择题32答案],[政治选择题33答案],[政治选择题34答案],[政治选择题35答案],[政治选择题36答案],[政治39(1)],[政治39(2)],[政治39(3)],[政治39(4)],[政治(选做A)42(1)],[政治(选做A)42(2)],[历史选择题13答案],[历史选择题14答案],[历史选择题15答案],[历史选择题16答案],[历史选择题17答案],[历史选择题18答案],[历史选择题19答案],[历史选择题20答案],[历史选择题21答案],[历史选择题22答案],[历史选择题23答案],[历史选择题24答案],[历史38(1)],[历史38(2)],[历史38(3)],[历史38(4)],[历史(选做A) 41(1)],[历史(选做A) 41(2)],[地理选择题1答案],[地理选择题2答案],[地理选择题3答案],[地理选择题4答案],[地理选择题5答案],[地理选择题6答案],[地理选择题7答案],[地理选择题8答案],[地理选择题9答案],[地理选择题10答案],[地理选择题11答案],[地理选择题12答案],[地理37(1)],[地理37(2)],[地理37(3)],[地理37(4)],[地理(选做A) 40(1)],[地理(选做A) 40(2)]'
while(@i <=@total)
begin
select @schoolName = name from #tt where ID=@i
set @i += 1
set @sql = 'bcp "SELECT ' + @colNames + ' FROM demo..wx where [学校]=''' + @schoolName + '''" queryout C:\wk\wz' + @schoolName +'.xls -c -S . -U sa -P 1234567890'
print @sql
--EXEC master..xp_cmdshell @sql
end
生成的BCP语句如下:
bcp "SELECT [考生号],[姓名],[总分],[政治选择题25答案],[政治选择题26答案],[政治选择题27答案],[政治选择题28答案],[政治选择题29答案],[政治选择题30答案],[政治选择题31答案],[政治选择题32答案],[政治选择题33答案],[政治选择题34答案],[政治选择题35答案],[政治选择题36答案],[政治39(1)],[政治39(2)],[政治39(3)],[政治39(4)],[政治(选做A)42(1)],[政治(选做A)42(2)],[历史选择题13答案],[历史选择题14答案],[历史选择题15答案],[历史选择题16答案],[历史选择题17答案],[历史选择题18答案],[历史选择题19答案],[历史选择题20答案],[历史选择题21答案],[历史选择题22答案],[历史选择题23答案],[历史选择题24答案],[历史38(1)],[历史38(2)],[历史38(3)],[历史38(4)],[历史(选做A) 41(1)],[历史(选做A) 41(2)],[地理选择题1答案],[地理选择题2答案],[地理选择题3答案],[地理选择题4答案],[地理选择题5答案],[地理选择题6答案],[地理选择题7答案],[地理选择题8答案],[地理选择题9答案],[地理选择题10答案],[地理选择题11答案],[地理选择题12答案],[地理37(1)],[地理37(2)],[地理37(3)],[地理37(4)],[地理(选做A) 40(1)],[地理(选做A) 40(2)] FROM demo..wx where [学校]='厦门市东山中学'" queryout C:\wk\wz厦门市东山中学.xls -c -S . -U sa -P hx123456
bcp "SELECT [考生号],[姓名],[总分],[政治选择题25答案],[政治选择题26答案],[政治选择题27答案],[政治选择题28答案],[政治选择题29答案],[政治选择题30答案],[政治选择题31答案],[政治选择题32答案],[政治选择题33答案],[政治选择题34答案],[政治选择题35答案],[政治选择题36答案],[政治39(1)],[政治39(2)],[政治39(3)],[政治39(4)],[政治(选做A)42(1)],[政治(选做A)42(2)],[历史选择题13答案],[历史选择题14答案],[历史选择题15答案],[历史选择题16答案],[历史选择题17答案],[历史选择题18答案],[历史选择题19答案],[历史选择题20答案],[历史选择题21答案],[历史选择题22答案],[历史选择题23答案],[历史选择题24答案],[历史38(1)],[历史38(2)],[历史38(3)],[历史38(4)],[历史(选做A) 41(1)],[历史(选做A) 41(2)],[地理选择题1答案],[地理选择题2答案],[地理选择题3答案],[地理选择题4答案],[地理选择题5答案],[地理选择题6答案],[地理选择题7答案],[地理选择题8答案],[地理选择题9答案],[地理选择题10答案],[地理选择题11答案],[地理选择题12答案],[地理37(1)],[地理37(2)],[地理37(3)],[地理37(4)],[地理(选做A) 40(1)],[地理(选做A) 40(2)] FROM demo..wx where [学校]='厦门市第二外国语中学'" queryout C:\wk\wz厦门市第二外国语中学.xls -c -S . -U sa -P hx123456
bcp "SELECT [考生号],[姓名],[总分],[政治选择题25答案],[政治选择题26答案],[政治选择题27答案],[政治选择题28答案],[政治选择题29答案],[政治选择题30答案],[政治选择题31答案],[政治选择题32答案],[政治选择题33答案],[政治选择题34答案],[政治选择题35答案],[政治选择题36答案],[政治39(1)],[政治39(2)],[政治39(3)],[政治39(4)],[政治(选做A)42(1)],[政治(选做A)42(2)],[历史选择题13答案],[历史选择题14答案],[历史选择题15答案],[历史选择题16答案],[历史选择题17答案],[历史选择题18答案],[历史选择题19答案],[历史选择题20答案],[历史选择题21答案],[历史选择题22答案],[历史选择题23答案],[历史选择题24答案],[历史38(1)],[历史38(2)],[历史38(3)],[历史38(4)],[历史(选做A) 41(1)],[历史(选做A) 41(2)],[地理选择题1答案],[地理选择题2答案],[地理选择题3答案],[地理选择题4答案],[地理选择题5答案],[地理选择题6答案],[地理选择题7答案],[地理选择题8答案],[地理选择题9答案],[地理选择题10答案],[地理选择题11答案],[地理选择题12答案],[地理37(1)],[地理37(2)],[地理37(3)],[地理37(4)],[地理(选做A) 40(1)],[地理(选做A) 40(2)] FROM demo..wx where [学校]='厦门市集美中学'" queryout C:\wk\wz厦门市集美中学.xls -c -S . -U sa -P hx123456
......................