Solved

DOWNLOAD RECORDSET RESULTS TO CSV FILE

Posted on 2003-10-22
13
918 Views
Last Modified: 2008-01-16
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
0
Comment
Question by:pjordanna
  • 5
  • 4
  • 4
13 Comments
 
LVL 4

Expert Comment

by:Tasneem
Comment Utility
0
 
LVL 21

Assisted Solution

by:ap_sajith
ap_sajith earned 250 total points
Comment Utility
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
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
<!--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
 

Author Comment

by:pjordanna
Comment Utility
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
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
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
 

Author Comment

by:pjordanna
Comment Utility
Hi ap_sajith,

Sorry my post crossed with yours. I'm working through your code now...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:pjordanna
Comment Utility
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
 
LVL 4

Expert Comment

by:Tasneem
Comment Utility
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
 
LVL 4

Expert Comment

by:Tasneem
Comment Utility
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
 

Author Comment

by:pjordanna
Comment Utility
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
 
LVL 4

Accepted Solution

by:
Tasneem earned 250 total points
Comment Utility
 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
 

Author Comment

by:pjordanna
Comment Utility
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
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
No Probs.. Cheers!!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now