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

1 New Member

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

 response.expires = 0
 
dim i,x,y,rst,F,Head,SQLstring,tempstring, dbfield, hdr
 
public function replacebrComma(dbfield)

    if len(dbfield)>0 then

        replacebrComma=replace(replace(replace(replace(dbfield,"<br>",""),Chr(10),""),Chr(13),""),",","")

    else

        replacebrComma=""

    end if

end function
 
SQLstring =  "Select * from FourBlocker"

set rst = my4Blocker.execute(SQLstring)
 
response.contenttype = "application/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 & "'>Fourblocker 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>")

Feb 21 '08 #1

Subscribe Reply

1553

Merlin1857

New Member

Try this :

Expand|Select|Wrap|Line Numbers

  
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

%>
 
=========================================================================

Feb 28 '08 #2

Similar topics

6139

update multiple rows

by: shank | last post by:

Visually, the page will look somewhat like a spreadsheet. It could have hundreds of records (rows) displayed. I want to enable the user to edit any one or any number of records and any fields, then click a save button to UPDATE the SQL table. I'd like to use stored procedures if possible. How is this done? Where do I start? thanks

ASP / Active Server Pages

7782

Add rows to top of existing spreadsheet

by: Dixie | last post by:

How do I, in VBA from an access form module, add 5 rows to the top of a spreadsheet called MySpreadsheet.xls. The worksheet is called MyWorksheet and there is already data in the first 5 rows. I simply need to insert 5 blank rows at the top and move the rest of the data down so it starts at row 6. dixie

Microsoft Access / VBA

1206

Problems with Response.AddHeader

by: Frank Bishop | last post by:

I am having problems downloading an Excel file after I export it with another procedure. When I look at the file after export, it looks fine, but when I use the sub below, it rearranges all the data in the excel file to an unreadable glob. Help appreciated. Thanks, Frank <code> Sub DisplayDownloadDialog() Dim ExportPath As String

ASP.NET

6768

Can we open excel in WebForm?

by: Daniel | last post by:

Hi all, Can i open and edit the excel sheet on web page after downloading? After editing, i close the web page and the excel file auto upload to the server. Is it possible? I really struggling about the ability. If not, what advice can u provide? thank you in advance. ur help will be appreaciated.

ASP.NET

1720

Problems Vacuum'ing

by: Jim Seymour | last post by:

Hi, Environment: PostgreSQL 7.4.2 Locally built with GCC 3.3.1 Solaris 8 (Sparc) I have a relatively simple database created with...

PostgreSQL Database

2060

problems connecting to a data source

by: Adam Sandler | last post by:

Hello all, I'm trying to use VWD 2005 Express Edition to connect to a database. For my initial prototyping though, I'm just using an Excel spreadsheet as the source and everything resides on the local host. All I want to do right now is get the info from the source and display it on a webpage. I've dropped a GridView and a SQLDataSource onto the page and here's the code:

ASP.NET

3577

Complex database - having problems getting data updated

by: rustyc | last post by:

Well, here's my first post in this forum (other than saying 'HI' over in the hi forum ;-) As I said over there: ... for a little side project at home, I'm writing a ham radio web site in uby/Rails. I started it in Perl and gave up on Perl as I went from the 'display the database information on the web page' to the 're-display the information from the database and allow the user to update the database using the web page' stage and realized...

Ruby / Ruby on Rails

1319

Export Datagrids to Excel

by: =?Utf-8?B?Sm9obiBXYWxrZXI=?= | last post by:

Hi, I am using the code below to export a webpage to Excel. The webpage has three datagrids on it and they are all exported to Excel properly and everything looks very nice, but we would really only like two of the three datagrids to be exported. Is there a way to exclude certain sections of the webpage from being exported to Excel? "DG" in the code here is one of the datagrids on the page: ...

ASP.NET

3005

Importing from Excel problems

by: scoots987 | last post by:

What do others do if you need to import excel files into SQL Server? My main problems are 1) zipcode formatting issues. If the column is a mix of zip and zip+4, I have problems retrieving all zipcodes. 2) If the last column contains NULL no information is imported. All this with using the Management console using Import data in SQL Server 2005. I am simply trying to import the data into NEW databases.

Microsoft SQL Server

9579

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9422

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10038

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8867

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7404

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6662

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

3952

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3558

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2812

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General