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

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • JRussell
    New Member
    • Feb 2008
    • 1
    #1

    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?
    [code=asp]response.expire s = 0

    dim i,x,y,rst,F,Hea d,SQLstring,tem pstring, dbfield, hdr

    public function replacebrComma( dbfield)
    if len(dbfield)>0 then
    replacebrComma= replace(replace (replace(replac e(dbfield,"<br> ",""),Chr(10)," "),Chr(13),""), ",","")
    else
    replacebrComma= ""
    end if
    end function

    SQLstring = "Select * from FourBlocker"
    set rst = my4Blocker.exec ute(SQLstring)


    response.conten ttype = "applicatio n/vnd.ms-excel"
    response.write( "<TABLE border='1'")

    i=0
    for each f in rst.fields
    i=i+1
    hdr = hdr & "<TD>" & f.name & "</TD>"
    'g = g & ", " & f.name
    next
    response.write( "<TR><TD colspan='" & i & "'>Fourbloc ker Data Download</TD></TR>")
    response.write( "<TR>" & hdr & "</TR>")
    alldata = rst.getrows
    for x=0 to ubound(alldata, 2)
    response.write( "<TR>")
    for y=0 to ubound(alldata, 1)
    if alldata(y,x) <> "" then
    response.write( "<TD>" & replace(alldata (y,x),"<BR>"," " & chr(10)) & "</TD>")
    else
    response.write( "<TD>" & alldata(y,x) & "</TD>")
    end if
    next
    response.write( "</TR>")
    next
    response.write( "</TABLE>")[/code]
    Last edited by jhardman; Feb 23 '08, 04:36 AM. Reason: put code in code tags. Please note button marked - #
    Tags: None
    • Merlin1857
      New Member
      • Sep 2007
      • 14
      #2
      Try this :

      Code:
       
This code provides for the export of data from SQL server to Excel via two asp pages.
You coould go directly to the 02extoex.asp page without showing 01viewdata.asp which
is only there to show the user the data first.

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

PAGE ONE CALLED 01viewdata.asp :

<%@ Language=VBScript %>
<!-- #include file="../incfiles/adovbs.inc" -->
<% 
dim Conn,RS1

'==================CONNECTION CODE=======================
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS1 = Server.CreateObject("ADODB.RecordSet")

strConn = "Provider=SQLOLEDB;Data Source=LAPTOP01;Initial Catalog=polpact;"&_
"User Id=logger;Password=logger;"
Conn.Open strConn 
'==================CONNECTION CODE=======================

sql1 = "Select * from tblAccessLog WHERE LogID < 10000" 

RS1.Open sql1, Conn

If RS1.eof Then
%>

 

<html>
<body>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
  <td>&nbsp;<font face="Arial" color="#FF0000" size="2">No Records Match Your Search</font></td>
 </tr>
</table>

<%Else%>
<form method="POST" action="02extoex.asp">
<table border=0 width="100%" cellspacing="0" cellpadding="2">
<tr>
<td>
<input type="submit" value="Export To Excel Spread Sheet" name="B1" style="font-size: 8pt">&nbsp;
<input type="hidden" name="InpSQL" size="63" value="<%=sql1%>"></td>
</tr>
</table>
</form>

<table border="1" width="100%" cellspacing="0" cellpadding="2" bordercolor="#000000">
 <tr>
  <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log ID</font></td>
  <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log Date</font></td>
  <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log Time</font></td>
  <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log User</font></td>
  <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log IP Rem</font></td>
  <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log IP Loc</font></td>
  <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log Page</font></td>
 </tr>
 <%Do While Not RS1.eof%>
 <tr>
  <td><font face="Arial" size="2"><%=RS1("LogID")%></font></td>
  <td><font face="Arial" size="2"><%=RS1("LogDate")%></font></td>
  <td><font face="Arial" size="2"><%=RS1("LogTime")%></font></td>
  <td><font face="Arial" size="2"><%=RS1("LogUser")%></font></td>
  <td><font face="Arial" size="2"><%=RS1("LogIPRem")%></font></td>
  <td><font face="Arial" size="2"><%=RS1("LogIPLoc")%></font></td>
  <td><font face="Arial" size="2"><%=RS1("LogPage")%></font></td>
 </tr>
 <%
 RS1.Movenext
 Loop
 End If
 %>
</table>

 

</td></tr>
</table>
</body>
</html>

<%
RS1.Close
Conn.Close

Set Conn = Nothing
Set RS1 = Nothing
%>

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

PAGE TWO CALLED 02extoex.asp :

<%@ Language=VBScript %>
<!-- #include file="../incfiles/adovbs.inc" -->
<% 
dim Conn,RS1

sql1 = Request("InpSQL")

'==================CONNECTION CODE=======================
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS1 = Server.CreateObject("ADODB.RecordSet")

strConn = "Provider=SQLOLEDB;Data Source=LAPTOP01;Initial Catalog=polpact;"&_
"User Id=logger;Password=logger;"
Conn.Open strConn 
'==================CONNECTION CODE=======================

'sql1 = "Select * from tblAccessLog WHERE LogID < 10000" 

RS1.Open sql1, Conn


Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=export.xls" 
%>

<table border="1" width="100%">
 <tr>
  <td bgcolor="#000080"><font color="#FFFFFF">Log ID</font></td>
  <td bgcolor="#000080"><font color="#FFFFFF">Log Date</font></td>
  <td bgcolor="#000080"><font color="#FFFFFF">Log Time</font></td>
  <td bgcolor="#000080"><font color="#FFFFFF">Log User</font></td>
  <td bgcolor="#000080"><font color="#FFFFFF">Log IP Rem</font></td>
  <td bgcolor="#000080"><font color="#FFFFFF">Log IP Loc</font></td>
  <td bgcolor="#000080"><font color="#FFFFFF">Log Page</font></td>
 </tr>
 <%Do While Not RS1.eof%>
 <tr>
  <td><%=RS1("LogID")%></td>
  <td><%=RS1("LogDate")%></td>
  <td><%=RS1("LogTime")%></td>
  <td><%=RS1("LogUser")%></td>
  <td><%=RS1("LogIPRem")%></td>
  <td><%=RS1("LogIPLoc")%></td>
  <td><%=RS1("LogPage")%></td>
 </tr>
 <%
 RS1.Movenext
 Loop
 %>
</table>

<%
RS1.Close
Conn.Close

Set Conn = Nothing
Set RS1 = Nothing
%>

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

        Comment

        Previous template Next
        Working...