?
Solved

How to write from database to excel using ASP?

Posted on 2003-03-17
20
Medium Priority
?
295 Views
Last Modified: 2007-12-19
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
Comment
Question by:simply_jessie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 3
20 Comments
 
LVL 10

Expert Comment

by:apollois
ID: 8157195
Hi simply_jessie,

>>>Response.write vbNewLine

Response.write "<BR>" & vbNewLine

Best Regards,
>apollois<
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 8157201
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
 
LVL 10

Expert Comment

by:apollois
ID: 8157203
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

Expert Comment

by:Silvers5
ID: 8157209
0
 
LVL 10

Expert Comment

by:apollois
ID: 8157254
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
 
LVL 10

Expert Comment

by:apollois
ID: 8157292
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
 

Author Comment

by:simply_jessie
ID: 8157328
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
 

Author Comment

by:simply_jessie
ID: 8157403
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
 

Author Comment

by:simply_jessie
ID: 8157410
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
 
LVL 10

Accepted Solution

by:
apollois earned 2000 total points
ID: 8157421
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
 
LVL 10

Expert Comment

by:apollois
ID: 8157433
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
 

Author Comment

by:simply_jessie
ID: 8157438
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
 
LVL 10

Expert Comment

by:apollois
ID: 8157441
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
 

Author Comment

by:simply_jessie
ID: 8157462
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
 
LVL 10

Expert Comment

by:apollois
ID: 8157480
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
 
LVL 10

Expert Comment

by:apollois
ID: 8157507
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
 

Author Comment

by:simply_jessie
ID: 8157541
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
 

Author Comment

by:simply_jessie
ID: 8157546
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
 
LVL 20

Expert Comment

by:Silvers5
ID: 8157563
>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
 

Author Comment

by:simply_jessie
ID: 8157667
sorry silvers5, i just didnt get it the first time. sorry.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question