用SQL SERVER对EXCEL数据进行处理

EXEC sp_configure 'show advanced options', 1;  
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'之前，需要先打开开关语句如下：

 

EXEC sp_configure 'show advanced options', 1;
go
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
go
RECONFIGURE;

采用xp_cmdShell执行，如下：

--create table #tt
--(
--    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语句，将这些语句拷贝到新建的批处理文件中，执行即可。

获取一个表的所有列：

declare @colNames varchar(max) = ''
select @colNames += '[' + name + '],' from sys.columns where object_id=object_id('wx') --and name like '%答案%'

print @colNames

 

declare @total int 
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
......................