Solved

DOWNLOAD RECORDSET RESULTS TO CSV FILE

Posted on 2003-10-22
13
922 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
13 Comments
 
LVL 4

Expert Comment

by:Tasneem
ID: 9597908
0
 
LVL 21

Assisted Solution

by:ap_sajith
ap_sajith earned 250 total points
ID: 9597945
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
ID: 9597947
<!--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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:pjordanna
ID: 9597974
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
ID: 9598063
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
ID: 9598079
Hi ap_sajith,

Sorry my post crossed with yours. I'm working through your code now...
0
 

Author Comment

by:pjordanna
ID: 9598214
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
ID: 9598277
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
ID: 9598316
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
ID: 9598343
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
ID: 9598374
 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
ID: 9599092
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
ID: 9604792
No Probs.. Cheers!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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 informatio…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 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