• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

How to write from database to excel using ASP?

seems to be easy but i cant run this properly, anyone please help me!

<%
Dim sTable
Dim sDSN
Dim sFields


sFields = "collectorid, attempts, calls, contacts, promise, amount" 'List of fields comma delimited
sTable = "dummyallcoll" 'Name of your table or View

Set DBConn = Server.Createobject("ADODB.Connection")
 
dbconn.commandtimeout = 0
   
DBConn.connectionString = "File Name=" & onRequestConnection()
DBconn.open

Set RS = Server.CreateObject("ADODB.Recordset")

RS.Open "select "& sFields &" from "& sTable,DBConn

Response.ContentType = "application/csv"
Response.AddHeader "Content-Disposition", "filename=mydata.csv;"
' lets print the fields on top


for i = 0 to RS.Fields.Count-1
   if i = (RS.Fields.Count - 1) then
      Response.Write RS.Fields(i).Name
   else
      Response.Write RS.Fields(i).Name & " "
   end if
next

Response.write vbNewLine

while not RS.EOF
   for u=0 to RS.Fields.Count - 1
      if u = (RS.Fields.Count - 1) then
         Response.Write RS.Fields(u).Value
      else
         Response.Write RS.Fields(u).Value & " "
      end if
   next

   response.write vbNewLine
   rs.MoveNext
wend

Response.write vbNewLine

Set RS = Nothing
Set DB = Nothing

%>

this function writes to excel alright, but it doesnt recognize vbNewLine to write to next row, thereby writing all the data in one row........

if there's someone who knows how to write database to excel without using comma delimited format, please help me. thanks.
0
simply_jessie
Asked:
simply_jessie
  • 9
  • 8
  • 3
1 Solution
 
apolloisCommented:
Hi simply_jessie,

>>>Response.write vbNewLine

Response.write "<BR>" & vbNewLine

Best Regards,
>apollois<
0
 
Michel SakrCommented:
why not to write directly in html table formats?!
Excel accepts html.. so use <tr> 's as row delimters and td's as cell delimiters..

regards
0
 
apolloisCommented:
simply_jessie,

>>>this function writes to excel alright, but it doesnt recognize vbNewLine to
>>>write to next row, thereby writing all the data in one row........

Is this your only problem?


Best Regards,
>apollois<
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Michel SakrCommented:
0
 
apolloisCommented:
simply_jessie,

>>>if there's someone who knows how to write database to excel without using
>>>comma delimited format, please help me. thanks.

Here's a great example:
http://www.asp101.com/samples/xl.asp

Let me know if you have any more follow-up questions.

Best Regards,
>apollois<
0
 
apolloisCommented:
simply_jessie,


Using Excel Web queries is a great way to get web data into an Excel spreadsheet.  The Web page is a standard HTML table.  Excel queries the web page and loads the data into the worksheet.  The Excel user can build other sheet and graphs based on this.  The data can be easily refreshed from the web server anytime.  Here are some good references on this:

How to Programmatically Perform a Web Query
http://support.microsoft.com/default.aspx?scid=KB;en-us;q213730

HOW TO: Use a Web Data Source for a PivotTable in Excel 2000
http://support.microsoft.com/default.aspx?scid=KB;en-us;q211908

Understanding Web Queries
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office06012000.asp



Best Regards,
>apollois<
0
 
simply_jessieAuthor Commented:
Apollois, thanks for all ur replies, i just want to write it in Excel without having to display the excel application in my browser, im trying to look at all ur examples, il get back to you after ive seen it. thanks for ur help.
0
 
simply_jessieAuthor Commented:
Apollois, ive already seen all ur examples and almost all them opens up an excel application in the browser. what i need is to just write to excel file without opening excel in browser. thanks a lot.
0
 
simply_jessieAuthor Commented:
My code already writes to excel, i just need a code dat will write to a new row.......because response.write vbNewLine doesnt work.
0
 
apolloisCommented:
simply_jessie,

>>>response.write vbNewLine doesnt work.

You must have missed my first post.  Instead of vbNewline, use "<BR>"

response.write "<BR>" & vbNewLine

The "<BR>" will cause a break in the HTML as displayed in your browser.
By adding vbNewLine, the HTML code will start on a new line.

Does this do what you want?

Best Regards,
>apollois<
0
 
apolloisCommented:
simply_jessie,

>>>Response.Write RS.Fields(u).Value & " "

If you're trying to output a CSV  (comma separated value) file, shouldn't this be:

Response.Write RS.Fields(u).Value & ", "


Best Regards,
>apollois<
0
 
simply_jessieAuthor Commented:
Thanks, it worked.....lol....il accept ur answer, i just hope u can help me how to write in excel without making it as comma delimited....

usually, response.contenttype "application/vnd.ms-excel" invokes excel application into browser.

do u have any idea how? thanks
0
 
apolloisCommented:
simply_jessie,

>>> opens up an excel application in the browser. what i need is to just write
>>>to excel file without opening excel in browser

To force the "SAVE AS" dialog, use the following:

Response.ContentType = "application/x-msdownload"
response.AddHeader "content-disposition", "attachment; filename=YourFileName.xls"

YOu can change "YourFileName.xls" to whatever you want.

Best Regards,
>apollois<
0
 
simply_jessieAuthor Commented:
thanks, dat worked too.....lastly.....and i hope u dnt mind, how to save it without making it comma delimited?

uve been a great help! thanks a lot
0
 
apolloisCommented:
simply_jessie,

>>>how to save it without making it comma delimited?

Just output as a HTML table, but with the XLS extension.  Excel will open and convert.

Does this make sense?

Best Regards,
>apollois<
0
 
apolloisCommented:
HOW TO BUILD HTML TABLE FROM DB


You will first output a heading row containing the names of the fields in the Recordset object. Because you do not know the names, you will just iterate through the Fields collection of the Recordset to obtain the field names. In this code, you obtain the field names by using "objRS.Fields(i).Name":

==================================================================
     ' -- Output the Field Names as the first row in the table
     Response.Write "<TR BGCOLOR=""#CCCCCC"">"
     For i = 0 to objRS.Fields.Count - 1
          Response.Write "<TH>" & objRS.Fields(i).Name & "</TH>"
     Next
     Response.write "</TR>"

================================================================

You then "walk" through the Recordset outputting the field values. Again, in this code, you obtain the value of a field by using "objRS.Fields(i)". This defaults to the .Value property of the field. You could have also used the code "objRS(i)" and obtained the same results:

=============================================================
     ' -- Now output the contents of the Recordset
     objRS.MoveFirst
     Do While Not objRS.EOF
          ' -- output the contents
          Response.Write "<TR>"
          For i = 0 to objRS.Fields.Count - 1
               Response.Write "<TD>" & objRS.Fields(i) & "</TD>"
          Next
          Response.write "</TR>"
          ' -- move to the next record
          objRS.MoveNext
     Loop
==============================================================

>apollois<
0
 
simply_jessieAuthor Commented:
ok, il try this one. but i tink it will work for sure. and im getting ur point. thanks a lot.

btw, i saw dat uve done foxpro too. ive coded some foxpro 2.6 systems before...

thanks so much.......
0
 
simply_jessieAuthor Commented:
ok, il try this one. but i tink it will work for sure. and im getting ur point. thanks a lot.

btw, i saw dat uve done foxpro too. ive coded some foxpro 2.6 systems before...

thanks so much.......
0
 
Michel SakrCommented:
>why not to write directly in html table formats?!

Well.. I guess I said that first.. :o/

Anyways.. I'm deep in .Net now..
0
 
simply_jessieAuthor Commented:
sorry silvers5, i just didnt get it the first time. sorry.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 9
  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now