关于简单格式大容量数据导出的web实现

    目前业界关于数据导出的方法很多，也各有各自的特点，但是具体到每家公司的具体情况和实际的应用环境，采用的方法都有一定的限制，前面我的《bcp生成excel文件优化方案》也实现了一种思路，但是后来给公司的系统部否定了，给出的原因是由于在存储过程中调用了master..xp_cmdshell,而启用这个存储过程是很危险的，存在系统级别的漏洞，因此直接咔嚓掉了。当时巨郁闷无比，自己费了半天劲弄的东西被别人一口否决，相信大家都有这样的体会，我就看着他们的脑袋像“巨无霸”。
    难道就没有别的办法了吗，应该不会，“问题总有第三种解决办法”我坚信只是自己没有找到而已。果然，一次偶然机会看到我们的web服务器上开启了WScript.Shell，而我们可以用这个来进行压缩文件，难道我们就不能用它执行bcp命令吗。
   插一句，关于bcp的好处其实大家都很清楚，这也是微软一直对它很难割舍的原因，就算现在我们有了SSIS但是bcp的方式一直还是在保存着，关于具体的好处，不清楚的大家可以自己搜索一下。
   接下来我们面临着一系列的问题，哎，生命不熄，捉虫不止啊，我们的价值就体现在钻别人空子中。具体说下我们的服务器配置和面临的问题。
   我们有两台web服务器web1和web2，一台数据库服务器db1，现在想在web2上执行对db1上的数据的导出，而且web2上没有安装sql(那是肯定的了)
   1.web服务器上没有bcp.exe，执行不了命令
   2.bcp导出的科学计数法问题
   3.bcp的1024字符限制的问题
   4.文件后缀名的问题

 

   接下来分别详细说明各个问题的解决办法。
   一.检查db1的数据库版本，拷贝bcp.exe和bcp.rll两个文件到web2的一个目录，注意目录格式
      假设根目录为d:\bcp,则：
      bcp.exe
      Resources目录---1033目录--bcp.rll
      这样就可以在web2上执行bcp命令了注意这里是以sql2000为例，2005可能不同

     补充说明：因为bcp所需的额外dll在安装系统的时候已经安装了如odbcbcp.dll已经安装到system32下了

   二、科学计数法问题
      如果在输出的数据中存在多位的数字，默认会按照科学计数法显示或者类似如“00441”这样的用户名，则会显示为“441”。
     解决方法1:在有可能显示多位数字的列名前特殊处理，如userid可能有类似00441的值，则修改查询的sql语句为select ''+userid,username from registersys，注意这里的两个单引号之间是一个tab键，不是空格。
    解决方法2:在有可能显示多位数字的列名前特殊处理，如userid可能有类似00441的值，则修改查询的sql语句为select '=""'+userid+'""',username from registersys，    
   三、为了方便调用，我写了一个asp2csv的函数，下面是代码

 

 1<%
 2
 3sub asp2csv(strSQL,columnName,fileName,isTable)
 4    dim Shell,RetCode,cmd,ExcuteSQL
 5    
 6    '处理物理表名，避免重复
 7     fileName=fileName&CStr(Month(Now()))+CStr(Day(Now()))+Cstr(Hour(Now()))+Cstr
 8
 9(Minute(Now()))+Cstr(Second(Now()))
    
    columnName=replace(columnName,",","','")
    columnName="'"& columnName &"'"
    if isTable="1" then
       ExcuteSQL="select "&columnName&" union all select * from "&strSQL
    elseif isTable="" or isTable="0" then
       ExcuteSQL="select "&columnName&" union all "&strSQL
    end if
       
    Set Shell = Server.CreateObject("WScript.Shell")
    
    cmd="bcp """&ExcuteSQL&""" queryout ""d:\querydata\"&fileName&".csv"" -

S""172.16.4.62"" -U""账号"" -P""密码""  -c -t"","""
     

    RetCode = Shell.Run(cmd,1, True)
    if RetCode=1 then
       response.write "数据导出异常，请稍后在试！"
    else
       response.write "<A HREF=""/querydata/"&fileName&".csv"" target=""_blank"">打

开Excel文件 </a>"
    end if
    if isTable="1" then
       conn.execute("truncate table "&strSQL)
       conn.execute("drop table "&strSQL)
       if err.number<>0 then
          response.write "处理物理表出错，请联系系统管理员处理"
       end if    
    end if     
    Set Shell= nothing
end sub
%>

 

 

1.asp2csv @a @b @c @d参数说明：
  @a:查询的sql语句或者要查询的物理临时表名
  @b:输出的列名称，注意要和sql顺序相同
  @c:输出的文件名称，不写后缀名
  @d: 是否为物理临时表

2.关于其他的几点说明
   1.1为什么要有@b参数（列标题的问题）：
     因为bcp命令直接输出查询结果，没有列标题，但是我们需要在excel中显示列标题，所以我们采用select 列名1,列名2 union all + SQL语句的方式解决列标题的问题。

   1.2时间列等非字符类型列的问题
    如果显示列为datetime类型，会报错，需要对其写法进行处理。
    select Convert(varchar(50),register_date,120),username from registersys

   1.3关于bcp的限制
    因为bcp的默认缓冲区不能大于1024字节，对于比较复杂的sql查询推荐使用先插入到物理临时表（#类似的临时表有问题），传入参数为表名。在asp2csv函数内部进行处理，处理完毕后自动删除此物理表，为避免对tmpdb有日志记录，先truncate然后再drop，避免对系统有大的影响。

   1.4关于超长sql的处理问题：
    对于比较长的sql请见下面的示例：

 1 sql="select -------"
 2     sql=sql&" from table1 inner join table2 ---"
 3     sql=sql&"-----"
 4     修改代为为：
 5     frontsql=""
 6     endsql=""
 7     excutesql=""
 8     sql="select -------"
 9     frontsql=sql
     sql=sql&" from table1 inner join table2 ---"
     sql=sql&"-----"
     endsql=sql
     endsql=mid(endsql,len(frontsql)+1,len(SQL))
     excutesql=frontsql&" into dbo.物理临时表 "&endsql
     最后调用函数执行导出
     conn.execute(excutesql)
     asp2csv "dbo.物理临时表","字段名","文件名","1"

 

 

 四、文件后缀名的问题
    偶然发现了一个很有意思的问题，web2上安装了office组件，在web2上导出csv文件的时候，正常能生成csv文件，但是下载的时候会自动把文件后缀名修改为xls，这样打开文件就把数据全显示在一起了。因为我们在asp2csv中使用的分隔符为“，”而excel默认的分割符为制表符，这样就导致了数据的格式没了。
    如果我们把asp2csv中bcp那里的-t,部分删除掉，如下
    cmd="bcp """&ExcuteSQL&""" queryout ""d:\querydata\"&fileName&".csv"" -S""172.16.4.62"" -U""账号"" -P""密码""  -c "
    但是我们在处理科学技术法的时候就不能采用第一种方法了，因为tab键和制表符混在了一起，就只能采用第二种方法了。但是这样我们采用制表符作为分割符的csv文件就有问题了，所以要把后缀名修改为xls.
    至此，我们就全部完成了工作，现在给出最后的asp2csv.

<%

sub asp2csv(strSQL,columnName,fileName,isTable)
    dim Shell,RetCode,cmd,ExcuteSQL
    
    '处理物理表名，避免重复
     fileName=fileName&CStr(Month(Now()))+CStr(Day(Now()))+Cstr(Hour(Now()))+Cstr

(Minute(Now()))+Cstr(Second(Now()))
    
    columnName=replace(columnName,",","','")
    columnName="'"& columnName &"'"
    if isTable="1" then
       ExcuteSQL="select "&columnName&" union all select * from "&strSQL
    elseif isTable="" or isTable="0" then
       ExcuteSQL="select "&columnName&" union all "&strSQL
    end if
       
    Set Shell = Server.CreateObject("WScript.Shell")
    
    cmd="bcp """&ExcuteSQL&""" queryout ""d:\querydata\"&fileName&".xls"" -

S""172.16.4.62"" -U""账号"" -P""密码""  -c "
     

    RetCode = Shell.Run(cmd,1, True)
    if RetCode=1 then
       response.write "数据导出异常，请稍后在试！"
    else
       response.write "<A HREF=""/querydata/"&fileName&".xls"" target=""_blank"">打

开Excel文件 </a>"
    end if
    if isTable="1" then
       conn.execute("truncate table "&strSQL)
       conn.execute("drop table "&strSQL)
       if err.number<>0 then
          response.write "处理物理表出错，请联系系统管理员处理"
       end if    
    end if     
    Set Shell= nothing
end sub
%>

最后看下测试结果

测试点：内部服务器

功能点：教学资料预定及下发查询(数据量：51762)
                        优化前                  优化后
仅查询                   47s                     47s
查询+导出（新执行计划）  5分钟或查询不出         60s
查询+导出                5分钟                   37s

试验点：正式服务器(文件大小12M)
仅查询                    39s                   28s
查询+导出                 104s                  35s

     总结：本方法实现了在web服务器上调用bcp从数据库服务器上导出大数据量数据到web服务器上的方法，不需要对数据库服务器做额外的设置，也不需要开启xp_cmdshell等扩展存储过程。是一种所需代价比较小的高效数据导出方案。