|
Database -- Convert to Comma-Delimited
File by Charles Carroll
This page demonstrates the capabilities how to
write an ASCII comma-delimited file from a SQL statement.
filename=/learn/test/dbconvert.asp
<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>
 |  |  |
|
There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too.
a $5 tip buys him lunch at McDonalds,
a $20 tip buys his kid Hitoshi a new computer game,
a $39 tip buys his daughter Michiko a few nice outfits.
See our donor list.
| |
 | |  |
|
|
|
|