469,619 Members | 2,437 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,619 developers. It's quick & easy.

Problems with Rows in Excel after downloading sql database info through webpage

Hi,

I am trying to download sql database through asp on my webpage and I keep getting weird outcomes in Excel. I have had to put in <char10> and <char13> into my sql data to have it display properly on the webpage and I know this is messing with the csv file download.

What it is doing in excel is breaking the "breaks" into multiple rows per recordset.

Here is my download code, is there anyway to fix this without removing them from my database?
Expand|Select|Wrap|Line Numbers
  1. response.expires = 0
  2.  
  3. dim i,x,y,rst,F,Head,SQLstring,tempstring, dbfield, hdr
  4.  
  5. public function replacebrComma(dbfield)
  6.     if len(dbfield)>0 then
  7.         replacebrComma=replace(replace(replace(replace(dbfield,"<br>",""),Chr(10),""),Chr(13),""),",","")
  8.     else
  9.         replacebrComma=""
  10.     end if
  11. end function
  12.  
  13. SQLstring =  "Select * from FourBlocker"
  14. set rst = my4Blocker.execute(SQLstring)
  15.  
  16.  
  17. response.contenttype = "application/vnd.ms-excel"
  18. response.write("<TABLE border='1'")
  19.  
  20. i=0
  21. for each f in rst.fields
  22.     i=i+1
  23.     hdr = hdr & "<TD>" & f.name & "</TD>"
  24.     'g = g & ", " & f.name
  25. next
  26. response.write("<TR><TD colspan='" & i & "'>Fourblocker Data Download</TD></TR>")
  27. response.write("<TR>" & hdr & "</TR>")
  28. alldata = rst.getrows
  29. for x=0 to ubound(alldata,2)
  30.     response.write("<TR>")
  31.     for y=0 to ubound(alldata,1)
  32.         if alldata(y,x) <> "" then
  33.             response.write("<TD>" & replace(alldata(y,x),"<BR>"," " & chr(10)) & "</TD>")    
  34.         else
  35.             response.write("<TD>" & alldata(y,x) & "</TD>")    
  36.         end if
  37.     next
  38.     response.write("</TR>")
  39. next
  40. response.write("</TABLE>")
Feb 21 '08 #1
1 1370
Try this :

Expand|Select|Wrap|Line Numbers
  1.  
  2. This code provides for the export of data from SQL server to Excel via two asp pages.
  3. You coould go directly to the 02extoex.asp page without showing 01viewdata.asp which
  4. is only there to show the user the data first.
  5.  
  6. =============================================================== 
  7.  
  8. PAGE ONE CALLED 01viewdata.asp :
  9.  
  10. <%@ Language=VBScript %>
  11. <!-- #include file="../incfiles/adovbs.inc" -->
  12. <% 
  13. dim Conn,RS1
  14.  
  15. '==================CONNECTION CODE=======================
  16. Set Conn = Server.CreateObject("ADODB.Connection")
  17. Set RS1 = Server.CreateObject("ADODB.RecordSet")
  18.  
  19. strConn = "Provider=SQLOLEDB;Data Source=LAPTOP01;Initial Catalog=polpact;"&_
  20. "User Id=logger;Password=logger;"
  21. Conn.Open strConn 
  22. '==================CONNECTION CODE=======================
  23.  
  24. sql1 = "Select * from tblAccessLog WHERE LogID < 10000" 
  25.  
  26. RS1.Open sql1, Conn
  27.  
  28. If RS1.eof Then
  29. %>
  30.  
  31.  
  32.  
  33. <html>
  34. <body>
  35. <table border="0" width="100%" cellspacing="0" cellpadding="2">
  36.  <tr>
  37.   <td>&nbsp;<font face="Arial" color="#FF0000" size="2">No Records Match Your Search</font></td>
  38.  </tr>
  39. </table>
  40.  
  41. <%Else%>
  42. <form method="POST" action="02extoex.asp">
  43. <table border=0 width="100%" cellspacing="0" cellpadding="2">
  44. <tr>
  45. <td>
  46. <input type="submit" value="Export To Excel Spread Sheet" name="B1" style="font-size: 8pt">&nbsp;
  47. <input type="hidden" name="InpSQL" size="63" value="<%=sql1%>"></td>
  48. </tr>
  49. </table>
  50. </form>
  51.  
  52. <table border="1" width="100%" cellspacing="0" cellpadding="2" bordercolor="#000000">
  53.  <tr>
  54.   <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log ID</font></td>
  55.   <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log Date</font></td>
  56.   <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log Time</font></td>
  57.   <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log User</font></td>
  58.   <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log IP Rem</font></td>
  59.   <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log IP Loc</font></td>
  60.   <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log Page</font></td>
  61.  </tr>
  62.  <%Do While Not RS1.eof%>
  63.  <tr>
  64.   <td><font face="Arial" size="2"><%=RS1("LogID")%></font></td>
  65.   <td><font face="Arial" size="2"><%=RS1("LogDate")%></font></td>
  66.   <td><font face="Arial" size="2"><%=RS1("LogTime")%></font></td>
  67.   <td><font face="Arial" size="2"><%=RS1("LogUser")%></font></td>
  68.   <td><font face="Arial" size="2"><%=RS1("LogIPRem")%></font></td>
  69.   <td><font face="Arial" size="2"><%=RS1("LogIPLoc")%></font></td>
  70.   <td><font face="Arial" size="2"><%=RS1("LogPage")%></font></td>
  71.  </tr>
  72.  <%
  73.  RS1.Movenext
  74.  Loop
  75.  End If
  76.  %>
  77. </table>
  78.  
  79.  
  80.  
  81. </td></tr>
  82. </table>
  83. </body>
  84. </html>
  85.  
  86. <%
  87. RS1.Close
  88. Conn.Close
  89.  
  90. Set Conn = Nothing
  91. Set RS1 = Nothing
  92. %>
  93.  
  94. ===============================================================
  95.  
  96. PAGE TWO CALLED 02extoex.asp :
  97.  
  98. <%@ Language=VBScript %>
  99. <!-- #include file="../incfiles/adovbs.inc" -->
  100. <% 
  101. dim Conn,RS1
  102.  
  103. sql1 = Request("InpSQL")
  104.  
  105. '==================CONNECTION CODE=======================
  106. Set Conn = Server.CreateObject("ADODB.Connection")
  107. Set RS1 = Server.CreateObject("ADODB.RecordSet")
  108.  
  109. strConn = "Provider=SQLOLEDB;Data Source=LAPTOP01;Initial Catalog=polpact;"&_
  110. "User Id=logger;Password=logger;"
  111. Conn.Open strConn 
  112. '==================CONNECTION CODE=======================
  113.  
  114. 'sql1 = "Select * from tblAccessLog WHERE LogID < 10000" 
  115.  
  116. RS1.Open sql1, Conn
  117.  
  118.  
  119. Response.ContentType = "application/vnd.ms-excel"
  120. Response.AddHeader "Content-Disposition", "attachment; filename=export.xls" 
  121. %>
  122.  
  123. <table border="1" width="100%">
  124.  <tr>
  125.   <td bgcolor="#000080"><font color="#FFFFFF">Log ID</font></td>
  126.   <td bgcolor="#000080"><font color="#FFFFFF">Log Date</font></td>
  127.   <td bgcolor="#000080"><font color="#FFFFFF">Log Time</font></td>
  128.   <td bgcolor="#000080"><font color="#FFFFFF">Log User</font></td>
  129.   <td bgcolor="#000080"><font color="#FFFFFF">Log IP Rem</font></td>
  130.   <td bgcolor="#000080"><font color="#FFFFFF">Log IP Loc</font></td>
  131.   <td bgcolor="#000080"><font color="#FFFFFF">Log Page</font></td>
  132.  </tr>
  133.  <%Do While Not RS1.eof%>
  134.  <tr>
  135.   <td><%=RS1("LogID")%></td>
  136.   <td><%=RS1("LogDate")%></td>
  137.   <td><%=RS1("LogTime")%></td>
  138.   <td><%=RS1("LogUser")%></td>
  139.   <td><%=RS1("LogIPRem")%></td>
  140.   <td><%=RS1("LogIPLoc")%></td>
  141.   <td><%=RS1("LogPage")%></td>
  142.  </tr>
  143.  <%
  144.  RS1.Movenext
  145.  Loop
  146.  %>
  147. </table>
  148.  
  149. <%
  150. RS1.Close
  151. Conn.Close
  152.  
  153. Set Conn = Nothing
  154. Set RS1 = Nothing
  155. %>
  156.  
  157. =========================================================================
  158.  
  159.  
Feb 28 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by shank | last post: by
1 post views Thread by Frank Bishop | last post: by
6 posts views Thread by Daniel | last post: by
reply views Thread by Jim Seymour | last post: by
3 posts views Thread by Adam Sandler | last post: by
1 post views Thread by =?Utf-8?B?Sm9obiBXYWxrZXI=?= | last post: by
3 posts views Thread by scoots987 | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.