%
'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 & "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 "Report Date | " & date() & " |
Report Title | " & sTitle & " |
Report Description | " & sDescription & " |
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 "