ASP to CSV very smooth...but, no field titles

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ASP to CSV very smooth...but, no field titles

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-01-13 : 09:37:05

Hi. I'm using a nice script from learnasp.com to send the results of an SPROC call to a CSV file. I have made minor modifications to the script to handle SPROC parameters and to redirect users to the newly-created CSV. My problem is that the CSV output lists only the records and not the Field titles. Can anyone see what's missing in the code below? thx.

==============================================================

<%@ LANGUAGE="VBSCRIPT" %>



<html><head>
<TITLE>MLtest to CSV</TITLE>
</head><body bgcolor="#FFFFFF">

<%

'Collect the needed form fields

strTableChoice = cStr(Request.Form("TableChoice"))
strBeginRange = cStr(Request.Form("BeginRange"))
strEndRange = cStr(Request.Form("EndRange"))

%>

<%

whichname="/temp/mltest.csv"
myDSN="stats"
mySQL="sp_mltest_results_range '" & strTableChoice &"','" & strBeginRange &"', '" & strEndRange &"'"
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 "
response.write mySQL & " "
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 & " "
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! "
response.write "Converted file is at <a href='"
response.write whichname & "'>" & whichname & "</a>"
else
response.write "VBScript Errors Occured! "
response.write "Error Number=#" & err.number & " "
response.write "Error Desc. =" & err.description & " "
response.write "Help Path =" & err.helppath & " "
response.write "Native Error=" & err.nativeerror & " "
response.write "Error Source =" & err.source & " "
response.write "SQL State=#" & err.sqlstate & " "
end if

Response.redirect ("http://dbserver/temp/mltest.csv")

%>
</body></html>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-13 : 10:07:30

You got this script from http://www.learnasp.com/ and it DOESN'T use GetString or GetRows? Charles must've gotten hit on the head with a blunt object and lost his memory.

This will not only write out the column names, but will be MUCH faster than what he had earlier:

<%@ LANGUAGE="VBSCRIPT" %>



<html><head>
<TITLE>MLtest to CSV</TITLE>
</head><body bgcolor="#FFFFFF">

<%

'Collect the needed form fields

strTableChoice = cStr(Request.Form("TableChoice"))
strBeginRange = cStr(Request.Form("BeginRange"))
strEndRange = cStr(Request.Form("EndRange"))

whichname="/temp/mltest.csv"
myDSN="stats"
mySQL="sp_mltest_results_range '" & strTableChoice &"','" & strBeginRange &"', '" & strEndRange &"'"
showblank=""
shownull="<null>"
linestart=chr(34)
lineend=chr(34) & vbCRLF 'This is changed from the original
delimiter=chr(34) & "," & chr(34)
delimitersub=""

whichFN=server.mappath(whichname)

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 "
response.write mySQL & " "
connection.close
set connection=nothing
response.end
end if

data=linestart 'starts the data value with a delimiter
for each whatever in rstemp.fields 'for each field in the recordset
data=data & whatever.name & delimiter 'add the field name and delimit it
next

'trim the last delimiter and add the lineend
data=Left(data, Len(data)-Len(delimiter)) & lineend

'convert the recordset to a string using the delimiter, lineend, and shownull replacements
data=data & rstemp.GetString(,, delimiter, lineend & linestart, shownull)

rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing

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

filetemp.Write(Left(data, Len(data)-Len(linestart)) 'trims the last linestart value and writes the file
' response.write thisline & " "

filetemp.Close
set filetemp=nothing
set fstemp=nothing

If err.number=0 then
response.write "File was converted sucessfully! "
response.write "Converted file is at <a href='"
response.write whichname & "'>" & whichname & "</a>"
else
response.write "VBScript Errors Occured! "
response.write "Error Number=#" & err.number & " "
response.write "Error Desc. =" & err.description & " "
response.write "Help Path =" & err.helppath & " "
response.write "Native Error=" & err.nativeerror & " "
response.write "Error Source =" & err.source & " "
response.write "SQL State=#" & err.sqlstate & " "
end if

Response.redirect ("http://dbserver/temp/mltest.csv")

%>
</body></html>

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-01-13 : 10:28:25

Thanks, Rob. Works nicely. Quick ones:

1. The very first entry in the CSV lists the field value followed by " How can I get rid of that?

2. Your original code was missing a bracket at the end of filetemp.write. I added as below:

filetemp.Write(Left(data, Len(data)-Len(linestart)))

3. If I want to have a unique filename created each time the user runs this script, would I use VBS Response object GetTempName?

I should have mentioned that this example came from the Classic ASP section of archived materials, so this may explain Charles' use of the classic coding technique.

Thanks again for the help.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-01-13 : 11:31:27

Rob,

I figured it out. Needed another linestart before GetString, as below. Thanks again.

==========================================================

'convert the recordset to a string using the delimiter, lineend, and shownull replacements
data=linestart 'starts the data value with a delimiter
data=data & rstemp.GetString(,, delimiter, lineend & linestart, shownull)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-13 : 11:40:41

quote:
1. The very first entry in the CSV lists the field value followed by " How can I get rid of that?
Can you show me an example of the output? Just a couple of lines. As usual I can't quite see the problem in the code itself (it's there, it's just hiding)
quote:
2. Your original code was missing a bracket at the end of filetemp.write. I added as below:

filetemp.Write(Left(data, Len(data)-Len(linestart)))
Must. Have. Coffee. Before. Typing. Code.
quote:
3. If I want to have a unique filename created each time the user runs this script, would I use VBS Response object GetTempName?
Actually it's a method of the FileSystemObject, not the Reponse object, but yeah you could definitely use that.
quote:
I should have mentioned that this example came from the Classic ASP section of archived materials, so this may explain Charles' use of the classic coding technique.
You must've found the one article he didn't go back and patch, he was pretty good at linking his GetRows stuff to everything else that had a recordset loop in it. This is the equivalent of an article written by NR on the joys of cursor usage!

Must. Preview. Thread. Before. Posting.

Edited by - robvolk on 01/13/2003 11:42:17

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-01-13 : 16:13:02

But wait! I spoke too soon. Adding the second linestart just removed my first row of fields altogether in the CSV output! I can't figure out where the bug is, but I can see that the very first record displays with a " to the right of the value in the CSV output, like this:

101"

so it seems that the trimming of linestart may be the culprit, but, again, I can't see where to modify the code.

thx

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-13 : 16:23:43

Try this:

data=data & linestart & rstemp.GetString(,, delimiter, lineend & linestart, shownull)

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-01-13 : 16:30:44

Thx. It works.

So, before you GetString, you drop in another linestart, thus providing the missing " for the first cell in the first row after fields are collected. Got it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources