<% 'CreateCSV.asp 'Kick out CSV in form of Query that is set in on URL. 'Create This is you CSV page. Then redirect to it. 'First make sure that all data we need is here. on error resume next dim sConn, sQuery dim errorstring dim Path dim DaysToLeaveFiles DaysToLeaveFiles = 14 Path = request.servervariables("PATH_TRANSLATED") Path = mid(path,1,instrrev(path,"\")) + "CSVTempFiles" 'response.write Path 'Make sure the path is looking OK. 'response.write path sConn = request("Conn") sQuery = request("Query") sTitle = request("Title") sDescription = request("Description") 'Make sure data comes in OK. 'response.write sConn & "
" 'response.write sQuery & "
" if sConn = "" then errorstring = "No Connection String specified." end if if sQuery = "" then errorstring = errorstring & "No Query String specified." end if if errorstring <> "" then %> Input Errors Have Occured!
<%=errorstring%> <% else 'Set up the Title and Description if sTitle = "" then sTitle = "Report you Requested on " & date() if sDescription = "" then sDescription = "Excel Converted Data produced for you on " & date() & " by the DOIT Atomic CSV Maker." 'Looks like we have what we need from the URL. Go for it. set conn = server.createobject("ADODB.Connection") set rs = server.createobject("ADODB.Recordset") conn.open sConn rs.open sQuery, conn 'Check the recordset for field names, if there are none, try to move to the next recordset. if rs.field(0).name = "" then set rs = rs.nextrecordset end if 'If we've gotten here, we are in GREAT shape. 'Create the FILESYSTEM OBJECT. set FSO = createobject("Scripting.FileSystemObject") 'Here is the Code to remove files older the X days. Dim fso, f, f1, fc, s Set fso = CreateObject("Scripting.FileSystemObject") Set f = fso.GetFolder(folderspec) Set fc = f.Files For Each f1 in fc if f1.dateCreated <= now() - DaysToLeaveFiles then f1.delete true end if Next 'Create a new file named YYYYMMDDHHNNSS.csv 'Build the filename dim filename filename = datepart("yyyy", now()) & datepart("m", now()) & datepart("d",now()) & datepart("h", now()) & datepart("n", now()) & datepart("s",now()) 'Make Sure FileName is worky. 'response.write filename Const ForReading = 1, ForWriting = 2 CSVFileName = Path & "\" & FileName & ".csv" 'response.write CSVFileName set F = FSO.CreateTextFile(CSVFileName) 'In order to create the CSV, we have to walk through the recordset's fields. for i = 0 to rs.fields.count - 1 'Build tmpStr tmpStr = tmpStr & rs(i).name & "," next 'At end of loop remove last char from the tmpStr. tmpStr = mid(len(tmpstr)-1,tmpstr) 'Write the line out to file. 'response.write "

Header

" & tmpStr & "

" F.WriteLine tmpStr tmpStr = "" 'Now go through the data records. do until rs.eof for i = o to rs.fields.count - 1 tmpStr = tmpStr & rs(i).value & "," next tmpStr = mid(len(tmpstr)-1,tmpstr) 'response.write tmpStr & "
" f.writeline tmpStr tmpStr = "" rs.movenext loop 'Okay we have written out the whole recordset to the file. 'Close the file. f.close HTMFileName = Path & "\" & FileName & ".htm" 'response.write HTMFileName set F = FSO.CreateTextFile(HTMFileName) 'Now get ready to create the HTML file... dim Body f.writeline "" f.writeline "" f.writeline "" f.writeline "" f.writeline "" & sTitle & "" f.writeline "" f.writeline "

Here is a link to the Excel formatted data you have requested.

" f.writeline "

Press Control-D on your keyboard to bookmark this page.

" f.writeline "
" f.writeline "" f.writeline "" f.writeline "" f.writeline "
Report Date" & date() & "
Report Title" & sTitle & "
Report Description" & sDescription & "
" f.writeline "

This data will stay here for 2 weeks from today. After that time the data file and this page will be deleted.

" 'Un rem this line if your users are unafraid of SQL. 'body = body & "

You Asked to see: " & sQuery & "

" f.writeline "

Click here to open your new Excel Spreadsheet

" f.writeline "" f.close response.redirect("CSVTempFiles/" & filename & ".htm") end if %>