Create CSV

[doorbreaker]

Hi I need to create a CSV. At the moment, it works perfectly but the user has to submit the form. How do I get it to create the CSV when the page is called. I am going to use a scheduled task eventually.

This is what currently works:

<%
on error resume next
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.cursorlocation=3
objconn.Open "driver={sql server};server=xxx.xxx.xxx.xxx;database=mydb;uid=myuid;pwd=mypwd"
if err.number then
	response.write "Could not connect to database server:<BR>"
	response.write err.Description
	response.end
end if
%>
			
	<%
		
		strSQL = "select * from table"
						
		Set rst = objconn.Execute(strSQL)
				
		if request("download")>"" then
			'response.addheader "content-type","application/vnd.ms-excel"
			response.addheader "content-type","application/ms-excel"
			response.addheader "content-encoding","none"
			response.addheader "content-disposition","inline; filename=generatedcsv.csv"
			response.write "Category	Type	FieldC	FieldD
	FieldE	FieldF	FieldG	FieldH	FieldI	FieldJ	FieldK	SKU	Description	Promotion	Image	LinkToProduct	Price	DeliveryCost	DeliveryTime	Availability	Warranty	Condition	OfferType	Bid" & vbcrlf
			while not rst.eof 
			
			UniqueCode = rst("productID")
			Name = rst("title")
			Description = rst("ShortDesc")
			Description = replace(Description,",","&#44;")
			LinkToProduct = "[URL unfurl="true"]http://www.mysite.com/product.asp?pid="[/URL] & UniqueCode
			Image = "[URL unfurl="true"]http://www.mysite.com/productimages/"[/URL] & rst("imgmain")
			Price = rst("Price")
			Price = formatnumber(Price,2)


					response.write "Gadgets		MySite	"& Name &"							"& UniqueCode &"	"& Description &"		"& Image &"	"& LinkToProduct &"	"& Price &"	""Free	1-3 Days	Yes	Yes
	New		" & vbcrlf
				iItems = iItems + rst("qty")
				iValue = iValue + cdbl(rst("value"))
				iCount = iCount + rst("count")
				rst.movenext							
			wend
			
			rst.close
			set rst=nothing
			objconn.close
			set objconn=nothing
			response.end
		
		end if 
	%>
	
<form method="post" name="frm">

<TABLE align="center">
<TR>
	<TD align="center"><IMG SRC="images/logo.gif" BORDER="0" ALT=""></TD>
</TR>
<TR>
	<TD class="black12" align="center">Product CSV Download Page<br><br></TD>
</TR>
<TR>
	<TD class="black12" align="center"><input type="hidden" name="download" value="">
<input type="button" class="actbut" value="Download CSV" onclick="frm.download.value='Y';frm.submit();"></TD>
</TR>
</TABLE>




</form>

Thanks for any help.

Chris

 

[Sheco]

You might consider converting this to a plain .VBS file...

That way, you could execute it just like a normal script or batch file.

You could use the FileSystemObject to create a text file and then fill the TextStream in a similar way that you use the Response.Write in your ASP.

 

[steven290]

just place a link to this page with this querystring

?download=t

change this part of the

if request("download")="t" then

 

[doorbreaker]

To convert it to a VBS file do I just drop the code in a page and call it nameoffile.vbs?

Could I just strip out the form bit and then run it?

Thanks

 

[doorbreaker]

Steven290,

if i put ?download=t in the URL then it still asks if I want to open it or save it.

Is there a way to make it always save it automatically without the prompt?

Thanks

Chris

 

[Sheco]

To convert it to a VBS you'd need to create an instance of the FileSystemObject and then use that to create a text file.

Then you'd write to the text file instead of to the Response object.

 

[steven290]

save to the webserver or save to the local computer, if to local cpu then you can't stop prompt