|
Displaying Source Code(s)
|
|
Database -- Convert to Comma-Delimited File
--------------------------------------------------------------------------------
Description : This page demonstrates the capabilities how to
write an ASCII comma-delimited file from a SQL statement.
<html><head>
<TITLE>dbconvert.asp</TITLE>
</head><body bgcolor="#FFFFFF">
<%
whichname="/upload/tests/authors.txt"
myDSN="DSN=Student;uid=student;pwd=magic"
mySQL="select * from authors where au_id<100"
showblank=""
shownull="<null>"
linestart=Chr(34)
lineend=Chr(34)
delimiter=Chr(34) & "," & Chr(34)
delimitersub=""
whichFN=Server.MapPath(whichname)
Set fstemp = Server.CreateObject("Scripting.FileSystemObject")
Set filetemp = fstemp.CreateTextFile(whichFN, True)
' true = file can be over-written if it exists
' false = file CANNOT be over-written if it exists
Set conntemp=Server.CreateObject("adodb.connection")
conntemp.open myDSN
Set rstemp=conntemp.execute(mySQL)
' this code detects if data is empty
If rstemp.eof Then
Response.Write "No data to convert for SQL statement<BR>
Response.Write mySQL & "<BR>
connection.close
Set connection=Nothing
Response.End
End If
Do UNTIL rstemp.eof
thisline=linestart
For Each whatever In rstemp.fields
thisfield=whatever.value
If IsNull(thisfield) Then
thisfield=shownull
End If
If Trim(thisfield)="" Then
thisfield=showblank
End If
thisfield=Replace(thisfield,delimiter,delimitersub)
thisline=thisline & thisfield & delimiter
Next
tempLen=Len(thisline)
tempLenDelim=Len(delimiter)
thisline=Mid(thisline,1,tempLEN-tempLenDelim) & lineend
filetemp.WriteLine(thisline)
' response.write thisline & "<BR>
rstemp.movenext
Loop
filetemp.Close
Set filetemp=Nothing
Set fstemp=Nothing
rstemp.close
Set rstemp=Nothing
conntemp.close
Set conntemp=Nothing
If Err.number=0 Then
Response.Write "File was converted sucessfully!<BR>
Response.Write "Converted file is at <a href='"
Response.Write whichname & "'>" & whichname & "</a>"
Else
Response.Write "VBScript Errors Occured!<BR>
Response.Write "Error Number=#<b>" & Err.number & "</b><BR>
Response.Write "Error Desc. =<b>" & Err.description & "</b><BR>
Response.Write "Help Path =<b>" & err.helppath & "</b><BR>
Response.Write "Native Error=<b>" & err.nativeerror & "</b><BR>
Response.Write "Error Source =<b>" & Err.source & "</b><BR>
Response.Write "SQL State=#<b>" & err.sqlstate & "</b><BR>
End If
%>
</body></html> |
|
|
123
Eng