Can not export as excel file more than 10000 rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can not export as excel file more than 10000 rows

Author

Topic

lydia
Starting Member

34 Posts

Posted - 2012-03-14 : 03:20:19

--Could you please help me.I use asp with vb script.Code below is export as excel file. I can export only 10000 records(rows). I would like to export more than 10000 rows.Thanks in advance :)

<%
call OpenDB22()
sql= "select * from inhouse_db.dbo.app_auth"
set rs=Conn1.Execute(sql)

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=Enquiry_List.xls"
if Rs.eof <> true then
response.write "<table border=1>"
while not Rs.eof
%>
<TR align="left" >

<TD><%=rs("userid")%></TD>
<TD><%=rs("password")%></TD>
<TD><%=rs("username")%></TD>
<TD><%=rs("registerdate")%></TD>
<TD><%=rs("status")%></TD>
<TD><%=rs("rights")%></TD>

</TR>
<%
Rs.movenext
wend
response.write "</table>"
end if
set rs=nothing

conn1.close
%>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-14 : 06:36:28

What is preventing you from exporting more than 10,000 rows? Are you getting an error message?

My guess is that you're exceeding a memory limit in your application by writing all that HTML. A few things you can do:

1. Don't include the font information for every <TD> element. It's wasteful and unnecessary (especially for an Excel sheet). I made a modification to the code if you absolutely have to have it.
2. Learn to use the GetRows and GetString methods of the Recordset object. They are MUCH faster and more efficient that using a While loop. Code modification below.
3. Don't use ASP to export formatted data to Excel. If you can, use Reporting Services for formatted reports, or export to a simple CSV file. This is especially true if you have many custom reports and have to program them separately.

Here are some code fixes that may alleviate your problem. As I said in #3, this is still not a recommended practice:

<%
call OpenDB22()
sql= "select userid,password,username,registerdate,status,rights from inhouse_db.dbo.app_auth"

set rs=Conn1.Execute(sql)
data=rs.GetString(2,,"</td><td>","</td></tr><tr><td>"," ")
rs.Close
Conn1.close
set rs=nothing
set Conn1=nothing

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=Enquiry_List.xls"
if data <> "" then
response.write "<table border=1 style='font-family:Calibri;color:blue;font-size:12pt;text-align:left;'><tr><td>" & left(data,len(data)-len("<tr><td>")) & "</table>"
end if
%>

It's possible this still won't work due to some internal limit of the Excel MIME type, or other memory issues. If that's the case you need to look at the options I listed in #3.

Here's a link on the GetString method: http://www.w3schools.com/ado/met_rs_getstring.asp

lydia
Starting Member

34 Posts

Posted - 2012-03-14 : 07:04:52

Thanks so much for your kidnly support :), i will try as you told me now.

lydia
Starting Member

34 Posts

Posted - 2012-03-14 : 07:39:08

Many thanks for your helpfull again.It's sucessfull after follow your advise:)

David_Basics
Starting Member

1 Post

Posted - 2012-03-15 : 05:34:42

I just resolved this problem very neatly.
I was getting a system out of memory exception on writing large excel files above 10,000 records.

I close the connection and open it based on a rows counter of 5000 rows max.

If you open the task manager and go to the performance tab you can see the CPU usage peak and trough.

excelCon.Open()
' create table
Dim cmd As New OleDb.OleDbCommand(sbFormatString.ToString, excelCon)
cmd.ExecuteNonQuery()

'sbInsertString contains list of columns
cmd.CommandText = sbInsertString.ToString
cmd.Parameters.AddRange(paramsList.ToArray)

Dim counter As Integer
Dim maxCounter As Integer = 5000

For Each dr As System.Data.DataRow In dt.Rows
counter += 1

For n As Integer = 0 To j
cmd.Parameters(n).Value = dr(n)
Next
If counter = maxCounter Then
' this controls the memory usage for large files
excelCon.Close()
excelCon.Open()
counter = 0
End If
cmd.ExecuteNonQuery()

Next

excelCon.Close()

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 13:01:07

I would do

bcp inhouse_db.dbo.app_auth out c:\test.dat -S<servername> -T -c

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources