creating a csv file in asp

Dim mystring as string

while not rs.EOF
  mystring = mystring & rs(0) & ", "
  mystring = mystring & vbCrLf
  rs.movenext
Wend

Then just write mystring to a file

I would recomment using the .toString method:

strReturn= objRS.GetString (StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)

For details see http://www.devguru.com/Technologies/ado/quickref/recordset_getstring.html

FtB

So:

<%
strReturn= objRS.GetString (2, , ",", , "Null")
objRS.Close
set objRS = Nothing
Response.AddHeader "Content-Disposition", "attachment; filename=YourFile.txt"
Response.Charset = "UTF-8"
Response.ContentType = "application/octet-stream"
Response.BinaryWrite strOutPut
Response.Flush
Response.Clear
%>

here is some general code to create a simple csv file from a recordset of data, using the file system object to create the csv file on your webserver

dim oFSO, oFile,oField
dim strOutput

'assuming you have already connected to your database and you have a connection object called oConn and you have already pulled a recordset of your data called oRS

set oFSO = server.createobject ("scripting.filesystemobject")
set oFile = oFSO.createtextfile("myfilepath",true)
do until oRS.eof
    for each oField in oRS.fields
        stroutput = stroutput & ofield.value & ","
    next
    'remove the last comma from the line before continuing
    stroutput = left(stroutput,len(stroutput)-1)
    ofile.writeline stroutput
    ors.movenext
loop

ofile.close
set oFile = nothing
set oFSO = nothing
'clean up your recordset and connection objects

You will want to make sure you have write permissions to the folder on the webserver where you are going to create this file.

hope this helps.
jk

The reason why I proposed the .getString() method is that you won't need to iterate through the recordset, nor will you have to create the FSO. This saves a lot of resources and doesn't require you to save a physical file on the server.

FtB

here's a function that will create a csv file.

You just need to pass it a sql query

sub outputcsv(sql)
      'response.write sql
      randomize
     nRandom = (100-1)*Rnd+1
     fileExcel =monthname(month(date)) &  cstr(year(date)) & "_" & CStr(nRandom) & ".csv"
     filePath= Server.mapPath("admin.asp")
       filepath=mid(filepath,1,len(filepath)-9)
       'response.write "filepath:" & filepath & "<br>"
        filename=filePath & "excelfiles\" & fileExcel
     'response.write filename
       Set fs = Server.CreateObject("Scripting.FileSystemObject")
     Set MyFile = fs.CreateTextFile(filename, True)

      Set rs = objconn.Execute(sql)

     strLine="<table><tr>" 'Initialize the variable for storing the filednames

     For each x in rs.fields
             strLine= strLine & "<td>" & x.name & "</td>"
     Next
             strline=strline&"</tr>"
       MyFile.writeline strLine

        Do while Not rs.EOF
     strLine="<tr>"
     for each x in rs.Fields
              cell=x.value
            strLine= strLine & "<td>" & cell & "</td>"
     next
             strline=strline & "</tr>"
     MyFile.writeline strLine
     rs.MoveNext
  Loop
      myfile.writeline "</table>"
    MyFile.Close
  Set MyFile=Nothing
  Set fs=Nothing

  link="<A HREF=excelfiles/" & fileExcel & " class=adminmenu target=_new>Click Here to Open Excel</a>"
   Response.write "<br>Excel File Created<br><br>" &  link


end sub

I tried all the solutions and could only get the one by jkwasson to work on my application. even with this I have encountered a problem I cannot seem to fix. The code runs then opens the .xls file but I find that each successive row seems to concatenate on the row before (eg) excel row 1 =table row 1, Excel row 2 = table row 1 + row 2. This continues until the final Excel row contains all the table rows consecutively. I cannot seem to fix the code to return only one Excel row per table row....

My code:

<%
Session("FileName")
FileName= "thisfile001.csv"
%>
<%
set oRS = Server.CreateObject("ADODB.Recordset")
oRS.ActiveConnection = MM_Honda_STRING
oRS.Source = "SELECT DISTINCT *  FROM dbo.countries"
oRS.CursorType = 0
oRS.CursorLocation = 2
oRS.LockType = 3
oRS.Open()
oRS_numRows = 0
%>
<%
dim oFSO, oFile,oField
dim strOutput

set oFSO = server.createobject ("scripting.filesystemobject")
set oFile = oFSO.createtextfile("D:\websites\tidyco\portals.tidyco\supplier\" & FileName,true)
do while not oRS.eof
    for each oField in oRS.fields
        strOutput = strOutput & oField.value & ","
    next
    'remove the last comma from the line before continuing
strOutput = left(strOutput,len(strOutput)-1)
    oFile.writeline strOutput
    oRS.movenext
Loop

oFile.close
set oFile = nothing
set oFSO = nothing
'clean up your recordset and connection objects
%>
<%
oRS.Close()
%>
<%
Response.Redirect(FileName)
%>

I finally saw the reason. each time it loops the recordset, oField.value variable is given a new value which is added to the stored variable strOutput which has the value of the previous transaction. All that is required is to clear this variable value to "" just before each loop takes place!  so modified code is:

<%
Session("FileName")
FileName= "thisfile001.csv"
%>
<%
set oRS = Server.CreateObject("ADODB.Recordset")
oRS.ActiveConnection = MM_Honda_STRING
oRS.Source = "SELECT DISTINCT *  FROM dbo.countries"
oRS.CursorType = 0
oRS.CursorLocation = 2
oRS.LockType = 3
oRS.Open()
oRS_numRows = 0
%>
<%
dim oFSO, oFile,oField
dim strOutput

set oFSO = server.createobject ("scripting.filesystemobject")
set oFile = oFSO.createtextfile("D:\websites\tidyco\portals.tidyco\supplier\" & FileName,true)
do while not oRS.eof
      strOutput = ""      'this is the new line which clears the value before getting the next record!
    for each oField in oRS.fields
        strOutput = strOutput & oField.value & ","
    next
    'remove the last comma from the line before continuing
strOutput = left(strOutput,len(strOutput)-1)
    oFile.writeline strOutput
    oRS.movenext
Loop

oFile.close
set oFile = nothing
set oFSO = nothing
'clean up your recordset and connection objects
%>
<%
oRS.Close()
%>
<%
Response.Redirect(FileName)
%>

re the solution of fritz_the_blank:
it seems to work great on Firefox, but not IE.

any reason why??

Thanks!