DOWNLOAD RECORDSET RESULTS TO CSV FILE

Hi Experts,

First of all WHOA! What's happened to Experts Exchange?! This is going to take some getting used to!

Right, here's my problem. I need to be adble to download the results of a recordset to a CSV file from a backoffice suite which I am developing. Basically at the end of each day the users need to be able to log on, do a search for the days orders, view them on screen, download them to a csv file locally (preferably by clicking a button on-screen), and then log out.

I've done a bit of research and it appears that I will need some 3rd party software to accomplish this. Can you confirm that this is the case and give me some pointers?

I am developing the system in classic asp and am using a SQL Server running on a 2003 NT box.


any help would be greatfully received




PJORDANNA
pjordannaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TasneemCommented:
0
ap_sajithCommented:
Here is the script i wrote for generating excell reports..(un necessary sections removed of course!!)

<%

ReportPath=Server.Mappath("./Reports") & "\"

' ** FUNCTION TO RETRIEVE UNIQUE 32 BIT STRINGS **
Function genUID()
     Dim guid
    guid = server.createobject("scriptlet.typelib").guid
    guid=Left(guid,instr(guid,"}"))
    guid=replace(guid,"{","")
    guid=replace(guid,"}","")
    guid=replace(guid,"-","")
    genUID=guid
    set guid=nothing
end function

' ** FUNCTION TO STRIP HTML TAGS FROM A GIVEN TEXT **
' Arguments:
'  sHTML: Block of HTML text to strip (string)
Function StripHTMLTags(ByVal sHTML)
    Dim objRegExp, sOutput

    sHTML = Trim(sHTML & "") 'Prevent Null errors
    If Len(sHTML) > 0 Then
        Set objRegExp = New RegExp
        With objRegExp
            .IgnoreCase = True
            .Global = True
           .Pattern= "<[^>]+>"
            ' Replace all HTML tag matches with the empty string
            sOutput = .Replace(sHTML, "")
        End With
        Set objRegExp = Nothing
        ' Replace special characters with HTML equivalent
        sOutput = Server.HTMLEncode(sOutput)
        StripHTMLTags = sOutput
    Else
        StripHTMLTags = ""
    End If
End Function

Function getCleanString(byVal bString)
str_rs=bString
str_rs=Replace(unescape(str_rs),",", ";")
str_rs=Replace(str_rs,"<BR>"," ")
str_rs=StripHTMLTags(str_rs)
str_rs=Replace(str_rs,"&amp;","&")
str_rs=Replace(str_rs,"amp;","")
str_rs=Replace(str_rs,"&nbsp;"," ")
str_rs=Replace(str_rs,"&quot;",chr(34))
str_rs=Replace(str_rs,"&gt;",">")
str_rs=Replace(str_rs,"&lt;","<")
getCleanString=str_rs
End Function

Dim StrSQL,rsReport,oFSO,strFileName

strFileName=genUID & ".csv"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.OpenTextFile(ReportPath & strFileName,2,true,0)

oFile.WriteLine "No:,Title,Title1,Title2,Title3,Title4"

StrSQL="select a,b,c,d,e,f from table"

Set rsReport=objConn.Execute (strSQL)

While Not rsReport.EOF
      oFile.WriteLine rsReport(0) & "," & getCleanString(rsReport(1)) & "," & getCleanString(rsReport(2)) & "," & getCleanString(rsReport(3)) & "," & getCleanString(rsReport(4)) & "," & getCleanString(rsReport(5))
      rsReport.MoveNext
Wend
oFile.Close
' ** CLEANUP **
Set oFile=nothing
Set oFSO=nothing
Set objConn=nothing
Set rsReport=nothing

Response.Redirect "Reports/Download.asp?file=" & strFileName ' ** REDIRECT TO THE DOWNLOADS PAGE **

%>
0
ap_sajithCommented:
<!--download.asp-->

<%@Language="VBScript"%>
<%Option Explicit%>
<%Response.Buffer = True%>
<%
On Error Resume Next
Dim strPath

strPath = CStr(Request.QueryString("file"))
'-- basic error checking for the QueryString
If strPath = "" Then
      Response.Clear
      Response.Write("No file specified.")
      Response.End
ElseIf InStr(strPath, "..") > 0 Then
      Response.Clear
      Response.Write("Illegal folder location.")
      Response.End
      ElseIf Len(strPath) > 1024 Then
      Response.Clear
      Response.Write("Folder path too long.")
      Response.End
Else
      Call DownloadFile(strPath)
End If

Private Sub DownloadFile(file)
'--declare variables
Dim strAbsFile
Dim strFileExtension
Dim objFSO
Dim objFile
Dim objStream
Dim ReportPath
ReportPath=Server.Mappath("../uploads") & "\"
'-- set absolute file location
strAbsFile = ReportPath & file
'-- create FSO object to check if file exists and get properties
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
'-- check to see if the file exists
If objFSO.FileExists(strAbsFile) Then
      Set objFile = objFSO.GetFile(strAbsFile)
      '-- first clear the response, and then set the appropriate headers
      Response.Clear
      '-- the filename you give it will be the one that is shown
      '   to the users by default when they save
      Response.AddHeader "Content-Disposition", "attachment; filename=" & objFile.Name
      Response.AddHeader "Content-Length", objFile.Size
      Response.ContentType = "application/octet-stream"
      Set objStream = Server.CreateObject("ADODB.Stream")
      objStream.Open
      '-- set as binary
      objStream.Type = 1
      Response.CharSet = "UTF-8"
      '-- load into the stream the file
      objStream.LoadFromFile(strAbsFile)
      '-- send the stream in the response
      Response.BinaryWrite(objStream.Read)
      objStream.Close
      Set objStream = Nothing      
      Set objFile = Nothing
      objFSO.DeleteFile(strAbsFile)
Else  'objFSO.FileExists(strAbsFile)
      Response.Clear
      Response.Write("No such file exists.")
End If
Set objFSO = Nothing
End Sub
%>



Hope this helps..

Cheers!!
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

pjordannaAuthor Commented:
Hi Tasneem,

Thanks for the post. The info is great but it all seems to assume that we are downloading a specific file which resides on the server. I need to download the results of a recordset which contains sales data.

Basically the recordset will be presented as a table on screen (do while loop) and I need to download the data which goes to make up this table.

Is there a way to do this or do I need to create a file from the recordset in some way before downloading?



PJORDANNA
0
ap_sajithCommented:
My code exactly what you are looking for...Try it out.. let me know if u need any clarifications. I shall post my comments to your queries tomorrow morning as i am leaving now..

Cheers!!
0
pjordannaAuthor Commented:
Hi ap_sajith,

Sorry my post crossed with yours. I'm working through your code now...
0
pjordannaAuthor Commented:
Hi ap_sajith,

OK your code rocks. It does the job perfectly THANK YOU!

I have one quick question for you. How do you handle the build up of .csv files in the reports folder? Do you manually delete them, do you have a stored proceedure to do this or do you just leave them be?


Cheers,



PJORDANNA
0
TasneemCommented:
For creating csv file
<%
Dim RS, SQL, Conn
SQL = "Some query ...."
Set RS = Conn.Execute (SQL)

Dim F, Head
For Each F In RS.Fields
  Head = Head & ", " & F.Name
Next
Head = Mid(Head,3) & vbCrLf
Response.ContentType = "text/plain"
Response.Write Head
Response.Write RS.GetString(,,", ",vbCrLf,"")
' append the above values in a string variable strOutput
%>

and for saving in local is the following url
http://www.xefteri.com/articles/may082002/default.aspx

alternate if you dont want to create a temp file and then upload then.. use something of this sort.
' create Stream Object
   set ado_stream = Server.CreateObject("ADODB.Stream")
   ' open stream object and store text
   ado_stream.Type = 1  ' 1=adTypeBinary
   ado_stream.open
   ado_stream.Write strOutput
   ' save uploaded file
   ado_stream.SaveToFile "c:\tmp\upload1.doc",2  '
2=adSaveCreateOverWrite
   ado_stream.close

   ' destroy COM object  
   set ado_stream = Nothing
     Response.Write "Upload successful!"

Between ap_sajith is actually deleting the file that he created for the csv in his code. So that does not let any file remain on the server.
Regards
 
0
TasneemCommented:
hi try this
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20773644.html
this one does not require you to create a temp csv file
regards
tasneem
0
pjordannaAuthor Commented:
Hi Tasneem,

I'm running ap_sajith's code and the files are just building up on the server. Can you show me which part of his code handles the file deletion?



PJORDANNA
0
TasneemCommented:
 Set objFile = objFSO.GetFile(strAbsFile)
the above code gets the handle of the file
objFSO.DeleteFile(strAbsFile)
the above code deletes the file where strAbsFile is the absolute path of the file.
Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pjordannaAuthor Commented:
Guys,

I thought it was fairer to split the points on this one. Hope you don't mind. Thank you both. You have been a great help.



PJORDANNA
0
ap_sajithCommented:
No Probs.. Cheers!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.