Excel Export Fail when Number of rows in the Excel sheet exceeded the limit of 65536 rows.

  • rayabharapusuman

    SSCommitted

    Points: 1912

    Hi,

    I have ran into the below error because of bulk and huge data. The error is given below:

    "Error: System.Exception: Excel Rendering Extension : Number of rows in the Excel sheet exceeded the limit of 65536 rows. Message: Excel Rendering Extension : Number of rows in the Excel sheet exceeded the limit of 65536 rows. (Internal) Stack inner exception generated an error."

    I know that the error is caused because of the more number of rows exceeding 65536.

    Now i want to know how can i handle that in the SSRS 2005. I have a tabular report with header, details and footer section.

    I tried assigning "=Int((RowNumber(Nothing) - 1) / 20)" as the expression when i right clicked on details section and selected the edit group option. And when i try to run this i get 9 pages becuase i have 175 records and the strange thing is only the first record is shown per page. And the sheet is broke in excel.

    I donno why all other columns in the detail section are invisiible except the first record per page. only 1 record is shown out of 20 records and 21 out of 40 in second page and goes on.

    If the records are visible my problem is solved. I will just replace that with 65536 so that the maximum number of records in excel are reached and then page break is applied. I dont know why are the data invisible.

    Please tell me why the remaining items are getting invisible.

    Any help would be greatly appreciated.

    Thanks,

    Suman

  • Bob Griffin

    SSCertifiable

    Points: 5863

    The page break will not get you around the problem of max rows in excel. If you could maybe post some screen shots we might be able to offer some more information...

  • rayabharapusuman

    SSCommitted

    Points: 1912

    Thanks for the reply. Can you please give me solution to show the records in second worsksheeet in excel if the records excell 65536.

    I need solution to show records even if they cross 65536 when exported to excel.

    Please answer my question, any help would be appreciateed.

    Thanks,

    Suman

  • Bob Griffin

    SSCertifiable

    Points: 5863

    The excel limit is a hard limit. The only way to work around that is to summarize the data more in your select statement (group by) or select a smaller range of data in your parameters i.e. date range, from/to etc...

  • GSquared

    SSC Guru

    Points: 260824

    The only other solution I can think of is paginate the query, and limit it to the max number of rows per sheet, and include the "page number" as an input parameter.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • TKD-BB

    SSC Eights!

    Points: 993

    In your table, try creating a group (outside of any current grouping you have) with no header or footer, and the page break after value set to true. For the grouping value, use an expression similar to

    =ROWNUMBER(Nothing) MOD 60000

    This should force a page break after 60000 rows of data, which will translate into a new excel tab when you export. I believe the 65K limit in Excel is on a per worksheet basis.

  • rayabharapusuman

    SSCommitted

    Points: 1912

    can you please let me know how i can paginate the query. It would be great if you can assist me in making this happen.

    Is this all to be written on RDL file ?? Can you please make it more clear instead?

    Thanks,

    Suman

  • rayabharapusuman

    SSCommitted

    Points: 1912

    Thanks for the reply.

    I could achieve that as you said:

    Create a group outside the table with no headers and having page break at the end and the expression for grouping should be as given. 30 is the number for which you need a break. Similarly we can use 65000 but it is really failing bcoz of the grouping I suppose, not sure what might be the answer.

    =ceiling(rownumber(nothing)/30)

    when i use a less number like 30 40 100 200 it is ok, but when i use 65000 my RDLC file looks like it is hanged up and no report comes up. But when i remove that and execute the report for the same data it comes it easily on the rdlc files. And exporting to excel from this viewer is really failing i think it is becoz of the memory option.

    But page break with 65000 is making the report come up very slowly compared to without pagination. Is there any way we can avoid this.

    And export to excel fails in normal (without pagination) when rows exceed like 40 or 50k.

    Please let me know if you have any answer.

    Any help would be greatly appreciated.

  • SQLWannabe

    SSCrazy

    Points: 2840

    I'm thinking about a different route. I don't know what kind of "good" information can be contained in a +65K line Excel spreadsheet. I don't think people looking at that data could glean any meaningful info. Try to go back to your customer and find out what they're using this for. You may be able to deliver a better solution thru an SSAS report or some kind of Excel Pivot table that reads from an SSAS cube.

    If you're just dumping informational rows of data, try using a different format, such as CSV. Then the user can use some other tool (Access, etc.) besides Excel to view the data. But I would still consider the reason for the report request.

  • GSquared

    SSC Guru

    Points: 260824

    There is a solution to go beyond 65k rows, which is use Excel 2007, which can go MUCH larger than that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Mark Eytcheson

    Ten Centuries

    Points: 1152

    If you do go with Excel 2007, you'll have to remember to change the file type to xlsx during the Export process as SSRS2005 and 2008 default to saving files as xls file type when the Export choice of Excel is picked.

  • crookj

    SSChampion

    Points: 14045

    GSquared (10/29/2009)


    There is a solution to go beyond 65k rows, which is use Excel 2007, which can go MUCH larger than that.

    100% correct - in fact you can go up to 1,048,576 rows in Excel 2007.

    Joe

  • becklery

    Hall of Fame

    Points: 3315

    Spread the results on multiple worksheets.

    Have a look at pagebreaks:

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

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

    RAQ Report: Web-based Excel-like Java reporting tool[/url]

  • Mauve

    SSChampion

    Points: 11316

    crookj (10/30/2009)


    100% correct - in fact you can go up to 1,048,576 rolls in Excel 2007.

    Joe

    That's a lot of dinner rolls! 😛


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • Mauve

    SSChampion

    Points: 11316

    Mark Eytcheson (10/30/2009)


    If you do go with Excel 2007, you'll have to remember to change the file type to xlsx during the Export process as SSRS2005 and 2008 default to saving files as xls file type when the Export choice of Excel is picked.

    Everything I read, including the BOL (http://msdn.microsoft.com/en-us/library/ms159836.aspx), states that Excel 2007 is not supported is SSRS 2008.


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply