how to use ASP to export a recordset to csv, to be opened in excel

rkhlee
rkhlee used Ask the Experts™
on
i'm trying to run a SQL command on my DB to get a recordset, which in turn would be viewed in ms excel; however, i don't just want a asp file to be opened in excel. i want it to be a csv file.

so i want to export to csv, then make excel open and load that newly created csv file.

thanks in advance...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The code at the bottom shows you how to create an excel file that you can view in your browser or in excel.  It's easy to modify to dynamically create it from your recordset.  So that part is done.  The second part, create a csv file, well why not use the FileScripting Object to create a text file, and then write out your data as comma separated values?


<html>
<title>CodeAve.com(Create Excel from User Input)</title>
<body bgcolor="#FFFFFF">
<%
'Check to see if title has been entered or not
u_title=request.form("u_title")
if u_title = "" then
%>
<!-- Input form area - This will only display when no Title has been entered -->
<form method="POST" action="<%= request.servervariables("script_name") %>">
Document Title<br>
<input type="text" name="u_title" size="35">
<br><br>
Cell 1
<br>
<textarea rows="2" name="u_cell1" cols="35"></textarea>
<br><br>
Cell 2
<br>
<textarea rows="2" name="u_cell2" cols="35"></textarea>
<input type="submit" value="Submit" ></p>
</form>
</body>
</html>
<%
else

' If there is a user inputted title
' get all of the user inputed values
u_title=request.form("u_title")
u_cell1=request.form("u_cell1")
u_cell2=request.form("u_cell2")

' Varible created fo excel file name. Speces are changed to underscores
' and later the current date is added in attempts to create a unique file
' Users are not prevented from entering characters !@#$%^&*()+= that are
' invlaid file names in this example
g_filename=replace(u_title," ","_")


set fso = createobject("scripting.filesystemobject")
'   create the text (xls) file to the server adding the -mmddyyyy after the g_title value
Set act = fso.CreateTextFile(server.mappath(""&g_filename & "-"& month(date())& day(date())& year(date()) &".xls"), true)

'   write all of the user input to the text (xls) document    
'   The .xls extension can just as easily be .asp or .inc whatever best suits your needs
'   Providing that you remove the info contained in the header and remove the xml
'   reference in the html tag that starts the page/excel file.  It is to add gridlines and
'   a title to the excel worksheet
act.WriteLine "<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">"
act.WriteLine "<head>"
act.WriteLine "<!--[if gte mso 9]><xml>"
act.WriteLine "<x:ExcelWorkbook>"
act.WriteLine "<x:ExcelWorksheets>"
act.WriteLine "<x:ExcelWorksheet>"
act.WriteLine "<x:Name>"& u_title &"</x:Name>"
act.WriteLine "<x:WorksheetOptions>"
act.WriteLine "<x:Print>"
act.WriteLine "<x:ValidPrinterInfo/>"
act.WriteLine "</x:Print>"
act.WriteLine "</x:WorksheetOptions>"
act.WriteLine "</x:ExcelWorksheet>"
act.WriteLine "</x:ExcelWorksheets>"
act.WriteLine "</x:ExcelWorkbook>"
act.WriteLine "</xml>"
act.WriteLine "<![endif]--> "
act.WriteLine "</head>"
act.WriteLine "<body>"
act.WriteLine "<table>"
act.WriteLine "<tr>"
act.WriteLine "<td>"
act.WriteLine u_cell1
act.WriteLine "</td>"
act.WriteLine "<td>"
act.WriteLine u_cell2
act.WriteLine "</td>"
act.WriteLine "</tr>"
act.WriteLine "</table>"
act.WriteLine "</body>"
act.WriteLine "</html>"
' close the document
act.close
%>
Your excel has been successfully create and can be viewed by clicking
<a href="<%= g_filename &"-"& month(date())& day(date())& year(date())   %>.xls" target="_blank">here</a>
<%
end if
%>
</body>
</html>
Commented:
This example should help:

CSVTest.asp
===========

<%
Response.AddHeader "Content-Disposition", "attachment; filename=try.csv"
Response.ContentType = "application/vnd.ms-excel"
%>
"1","2","3"
"4","7","3"

Commented:
No comment has been added lately and it seems that this question have been abandoned. So it's time to clean up this TA.

I will leave a recommendation in the Cleanup topic area that this question or invite a Moderator to close this question if there's no reply from you after seven days.

In the absence of responses, I will recommend the following:

To accept the comment and points awarded to  normanpaterson


** PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER **


Just trying to help for the cleanup...
gladxml

Commented:
normanpaterson's code creates an excel file, not a .csv, which was requested. My answer is really the simplest way to achieve the result, and does not require using the FileSystemObject to create a file on the server.

Commented:
drittich,

Sorry comment noted...

To accept the comment and points awarded to  drittich..

Regards,
gladxml

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial