CSV是Comma Separated Value(逗号分隔值)的英文缩写,通常都是以英文的逗号(,)做为分隔符的纯文本文件。 为什么要用CSV格式的文件导入导入呢?因为EXCEL文件有太多格式,比如字体大小、颜色等等,这些格式基本上是无用的,导入数据库反而占用太多空间。当然,程序处理最方便的是XML文件(什么是XML文件,请查阅这里),但是XML对普通用户不太友好,而用EXCEL编辑CSV文件,相信是最合适的选择了。原创文章,转载请注明来自风易博客:http://www.1982y.net.cn 。
基于这些理由,客户又有这样的需求,那么我就写了一个asp批量导入导出CSV文件的例子,特发出来与大家共享。
上传文件,我用的是无组件的“无惧上传”,使用方法请大家自行Google。上传处理页代码如下:
ASP/Visual Basic代码
- '---------------------------------------上传CSV文件
- Sub uploadfile()
- set upload=new upfile_class ''建立上傳物件
- upload.GetData(Maxsize) '取得上傳資料,限制最大上傳($Maxsize) MB
- if upload.err > 0 then '有错误发生
- select case upload.err
- case 1
- okway.showErr "请选择您要上传的文件。/n如果您已经选择了文件,请确认您选择的是否空文件!","JS:history.back();try{ document.xfiles.upload.disabled=false;} catch(e) {};"
- response.end()
- case 2
- okway.showErr "你上传的文件体积超出了最大限制("& Maxsize/1024/1024 & "MB)","JS:history.back();try{ document.xfiles.upload.disabled=false;} catch(e) {};"
- response.End()
- end select
- End If
- Dim FileExt : FileExt = "csv" '限定上传的文件格式
- Dim Filename : Filename = "csvTemp/geili2011.csv" '限定上传的文件名称
- for each formName in upload.file '列出所有上傳了的文件
- set ofile=upload.file(formName) '生成一個文件物件
- if ofile.filesize<1 then
- okway.showErr "请选择您要上传的文件。/n如果您已经选择了文件,请确认您选择的是否空文件!","JS:history.back();try{ document.xfiles.upload.disabled=false;} catch(e) {};"
- Exit For
- end if
- if ofile.filesize>(Maxsize) then
- okway.showErr "你上传的文件体积超出了最大限制("& Maxsize/1024/1024 & "MB)","JS:history.back();try{ document.xfiles.upload.disabled=false;} catch(e) {};"
- Exit For
- end If
- If LCase(ofile.FileExt) <> FileExt Then
- okway.showErr "抱歉,只能上传"&FileExt&"格式的文件","JS:history.back();try{ document.xfiles.upload.disabled=false;} catch(e) {};"
- Exit For
- End If
- '保存上传文件
- ofile.SaveToFile Server.mappath(Filename)
- set ofile=Nothing
- Next
- set upload=Nothing
- session("geili2011") = Filename '保存文件名以待后续处理
- Okway.ShowSuc "恭喜您,文件上传成功,请点击[确定]继续",Okway.ScriptName
- End Sub
然后分析上传的CSV文件是否符合格式:
ASP/Visual Basic代码
- '---------------------------------------分析CSV文件
- Function checkfile()
- Dim Fso,myFile,i
- Dim Str,objs,tmpstr,column,css
- column = "姓名,电话,公司名称,Email,博客或公司网址,预购产品,预购日期"
- Set Fso = Server.CreateObject("Scripting.FileSystemObject")
- If Not Fso.FileExists(Server.MapPath(session("geili2011"))) Then
- session("geili2011") = ""
- okway.ShowSuc "错误1:抱歉,CSV文件不存在,请重新上传。",Okway.ScriptName
- Exit Function
- End If
- Set myFile = Fso.OpenTextFile(Server.MapPath(session("geili2011")),1,True)
- i = 0
- tmpstr = "<br /><div style='padding-left:5px;height:40px;line-height:40px;font-weight:bold'>*请确认导入的数据是否正确</div><table width=""100%"" border=""1"" cellspacing=""0"" cellpadding=""0"" style=""border:1px;"">"&VbNewline
- While Not myFile.AtEndOfStream
- Str = myFile.ReadLine
- objs = Split(Str,",")
- If UBound(objs) <> 6 Then
- session("geili2011") = ""
- Okway.ShowSuc "错误1:抱歉,你传入的CSV格式不正确,请按格式重新编辑后再上传。",Okway.ScriptName
- Exit Function
- End If
- css = " style=""padding-left:5px;"""
- If i = 0 Then
- If LCase(Replace(Str,",","")) <> LCase(Replace(column,",","")) Then
- session("geili2011") = ""
- Okway.ShowSuc "错误2:抱歉,你传入的CSV格式不正确,请按格式重新编辑后再上传。",Okway.ScriptName
- Exit Function
- End If
- css = " style=""padding-left:5px;font-weight:bold"""
- End If
- tmpstr = tmpstr & "<tr height=""25"">"&VbNewline
- tmpstr = tmpstr & "<td "&css&">"&objs(0)&" </td>"& "<td "&css&">"&objs(1)&" </td>"& "<td "&css&">"&objs(2)&" </td>"& "<td "&css&">"&objs(3)&" </td>"& "<td "&css&">"&objs(4)&" </td>"& "<td "&css&">"&objs(5)&" </td>"& "<td "&css&">"&objs(6)&" </td>"
- tmpstr = tmpstr & "</tr>"&VbNewline
- i = i + 1
- Wend
- tmpstr = tmpstr & "<tr height=""50"">"&VbNewline
- tmpstr = tmpstr & "<td colspan=""7"" align=""center""><input type=""button"" value=""确认并导入"" οnclick=""window.location.href = '"&Okway.ScriptName&"?act=insert'"" /> <input type=""button"" value=""返回重新上传"" οnclick=""window.location.href = '"&Okway.ScriptName&"?act=back'"" /></td>"
- tmpstr = tmpstr & "</tr>"&VbNewline
- tmpstr = tmpstr & "</table>"&VbNewline
- myFile.Close
- Set myFile = Nothing
- Set Fso = Nothing
- checkfile = tmpstr
- End Function
最后,可根据需要把CSV文件导入数据库:
ASP/Visual Basic代码
- '---------------------------------------把CSV文件写入数据库
- Sub insertDb()
- Dim Fso,myFile,i,sqlcmd,addTime
- Set Fso = Server.CreateObject("Scripting.FileSystemObject")
- If Not Fso.FileExists(Server.MapPath(session("geili2011"))) Then
- session("geili2011") = ""
- Okway.ShowSuc "错误2:CSV文件不存在,请重新上传。",Okway.ScriptName
- Exit Sub
- End If
- Set myFile = Fso.OpenTextFile(Server.MapPath(session("geili2011")),1,True)
- i = 0
- While Not myFile.AtEndOfStream
- Str = myFile.ReadLine
- objs = Split(Str,",")
- If i <> 0 Then
- addTime = objs(6)
- If addTime = "" Or Not IsDate(addTime) Then addTime = Now()
- sqlcmd = "insert into [Ok_Geili2011](TxtName,TxtTel,TxtCompanyCn,TxtEmail,TxtWebsite,TxtProduct,addTime) values('"&Okway.safelysql(objs(0))&"','"&Okway.safelysql(objs(1))&"','"&Okway.safelysql(objs(2))&"','"&Okway.safelysql(objs(3))&"','"&Okway.safelysql(objs(4))&"','"&Okway.safelysql(objs(5))&"','"&addTime&"')"
- conn.Execute(sqlcmd)
- End If
- i = i + 1
- Wend
- session("geili2011") = ""
- Okway.ShowSuc "恭喜您,文件导入成功,请点击[确定]查看","geili2011_list.asp"
- End Sub
当用户发现自己上传的CSV文件内容有误时,可以点击反回按钮修改,返回的时候清除保存的文件名:
ASP/Visual Basic代码
- '---------------------------------------CSV文件内容不正确,返回重新上传
- Sub backUrl()
- session("geili2011") = ""
- Okway.ShowSuc "",Okway.ScriptName
- End Sub
至此,上传部分已经完成了,下面写如何导出,导出就比较简单了,直接用文件流写入浏览器即可。
我们知道CSV文件是以英文逗号分隔的,为了避免数据库字段中有英文逗号导致导出的内容出错,所以需要把英文逗号过滤一下,代码如下:
ASP/Visual Basic代码
- Function fixCSV(str)
- If str = "" Or IsNull(str) Then Exit Function
- str = Replace(str,",",",")
- fixCSV = str
- End Function
然后直接读出数据库内容写入浏览器即可:
ASP/Visual Basic代码
- If request("act")="download" Then
- Response.Clear()
- Response.AddHeader "Content-Disposition","attachment;filename=geili2011list.csv;" '这里自定义导出的文件名
- Response.ContentType = "application/octet-stream"
- Response.Write "姓名,电话,公司名称,Email,博客或公司网址,预购产品,预购日期"
- Response.Write(vbcrlf)
- sql ="select * from [Ok_Geili2011] order by id desc"
- Set rs = conn.execute(sql)
- Do While Not rs.eof
- echo fixCSV(rs("TxtName")) & "," & fixCSV(rs("TxtTel")) & "," &fixCSV(rs("TxtCompanyCn")) & "," &fixCSV(rs("TxtEmail")) & "," &fixCSV(rs("TxtWebsite")) & "," &fixCSV(rs("TxtProduct")) & "," & fixCSV(rs("addTime"))
- echo(vbcrlf)
- rs.movenext
- Loop
- rs.close : Set rs = Nothing
- Response.End
- End If