exporting more tha 65k rows to excel

hi all,

I wanted to know how to export a report that returns more than 65,000 rows of data to MS Excel, it will fail because excel has a limit of 65K records per excel sheet.

What i want to know if there is some way to use multiple sheets (in the same excel file) to show more than 65K records

Eg we have 105K rows of data ...can we export 65K records in the first sheet, and the remaining 40K in the second sheet (of the same excel file)?

Appreciate the Help,

Siddharth

Hello Siddharth,

Using a Table, you can force an explicit page break after a certain number of rows. Because the Excel renderer creates a new worksheet for every explicit page break, you can use this technique to make sure no more than 65,536 rows are exported to a single sheet.

Create an outer table group using this group expression: =Int((RowNumber(Nothing)-1)/65000). Set Page break at end on the group.

Keep in mind that there isn't a one-to-one relationship between SSRS table rows and Excel rows unless the table is the only report item in the report body. So, if you have other report items in addition to your table you will have to also take that into account.

-Chris

|||

Hi Chris,

I knew page break created a new sheet in excel but didnt know how to do it. thanks a lot for thehelp its woking. appreciate it.

siddharth

sql

Export to XML file from SQL Server 2005

I have a stored procedure which returns XML using SELECT with the FOR XML PATH, TYPE commands.

How do I pipe the results to a file? I had assumed that Integration Services would do this, but I see nothing about XML as a destination, only as a source.

You can use the sp_OA stored procs.

HTH.

|||After you run the FOR XML command, you can get a string like xml. Click that you can see the xml file itself. Then you just save this file. Hope this answer your question.|||

Hello,

Well, I already try this, runing a query in Data flow source (OLE BD Source) and send the result to a flat file destination. The problem now is matching types. OLE DB Source generate DT_IMAGE and until now I couldn't put directily into a xmlt/html file...

Export to XML file from SQL Server 2005

I have a stored procedure which returns XML using SELECT with the FOR XML PATH, TYPE commands.

How do I pipe the results to a file? I had assumed that Integration Services would do this, but I see nothing about XML as a destination, only as a source.

You can use the sp_OA stored procs.

HTH.

|||After you run the FOR XML command, you can get a string like xml. Click that you can see the xml file itself. Then you just save this file. Hope this answer your question.|||

Hello,

Well, I already try this, runing a query in Data flow source (OLE BD Source) and send the result to a flat file destination. The problem now is matching types. OLE DB Source generate DT_IMAGE and until now I couldn't put directily into a xmlt/html file...

Export to XML file from SQL Server 2005

I have a stored procedure which returns XML using SELECT with the FOR XML PATH, TYPE commands.

How do I pipe the results to a file? I had assumed that Integration Services would do this, but I see nothing about XML as a destination, only as a source.

You can use the sp_OA stored procs.

HTH.

|||After you run the FOR XML command, you can get a string like xml. Click that you can see the xml file itself. Then you just save this file. Hope this answer your question.|||

Hello,

Well, I already try this, runing a query in Data flow source (OLE BD Source) and send the result to a flat file destination. The problem now is matching types. OLE DB Source generate DT_IMAGE and until now I couldn't put directily into a xmlt/html file...

Export to MS Excel carriage returns

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

I haven't found one as yet. The other minor annoyance is that when you open the exported report in excel the cells with carriage returns all have the same row height and hence you can't actually tell if a cell has multiple lines unless you manually autosize the rows.|||

It really appears that the report export to MS Excel is just a file for the users to work with once the data gets exported. As the data does not come over clean (too many carriage returns), or the data does not completely display (all the data arrives, but it is hidden in the column, even if wrap text is checked), or the excel spreadsheet over extends the page to the left and prints 2 pages for 1 report page.

Has anyone had any success with exporting a report to MS Excel, or is it just a way for the users to edit the data?

If anyone can offer advice, I would appreciate it.

Thank you,

T.J.

|||The other thing it can't do is export sureports.|||

Thank you for telling me that. I had no idea.

Strange. I have a sub-report that exports to Excel just fine, but a sub-report on the sub-report does not export (the original sub-report is not linked, just a straight listing of data, seperate from the rest of the report).

Oh my.

|||Reading your post I just noticed that you have the order switched for those characters. You are using a line feed (chr 10) and then a carriage return (chr 13). Micro$ likes to see them in the other order: vbcrlf - or char(13) + char(10)... and some other OS's (that shall not be named) just want to see the line feed... which is probably why Excel is adding in the extra characters (it's picking up on it as a non-ms format).

Export to MS Excel carriage returns

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

I haven't found one as yet. The other minor annoyance is that when you open the exported report in excel the cells with carriage returns all have the same row height and hence you can't actually tell if a cell has multiple lines unless you manually autosize the rows.|||

It really appears that the report export to MS Excel is just a file for the users to work with once the data gets exported. As the data does not come over clean (too many carriage returns), or the data does not completely display (all the data arrives, but it is hidden in the column, even if wrap text is checked), or the excel spreadsheet over extends the page to the left and prints 2 pages for 1 report page.

Has anyone had any success with exporting a report to MS Excel, or is it just a way for the users to edit the data?

If anyone can offer advice, I would appreciate it.

Thank you,

T.J.

|||The other thing it can't do is export sureports.|||

Thank you for telling me that. I had no idea.

Strange. I have a sub-report that exports to Excel just fine, but a sub-report on the sub-report does not export (the original sub-report is not linked, just a straight listing of data, seperate from the rest of the report).

Oh my.

|||Reading your post I just noticed that you have the order switched for those characters. You are using a line feed (chr 10) and then a carriage return (chr 13). Micro$ likes to see them in the other order: vbcrlf - orchar(13) + char(10)... and some other OS's (that shall not be named) just want to see the line feed... which is probably why Excel is adding in the extra characters (it's picking up on it as a non-ms format).

Export to Excel - Change the Sheet Name

I need to change the sheetname with the field value retured by the stored
procedure.
Say the stored procedure returns 10 rows, when exporting the report , the
report should contain 10 sheets with the sheetname(field 1 value).
How can I do this.
Balaji
--
Message posted via http://www.sqlmonster.comSheet renaming isn't supported in RS SP1. I've seen a couple of threads
where people have created document maps to work around this 'limitation'.
Do a search in the newsgroup for 'document maps'.
--
Adrian M.
MCP
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ee446645890e4ae9a42b10c0d60a07e0@.SQLMonster.com...
>I need to change the sheetname with the field value retured by the stored
> procedure.
> Say the stored procedure returns 10 rows, when exporting the report , the
> report should contain 10 sheets with the sheetname(field 1 value).
> How can I do this.
> Balaji
> --
> Message posted via http://www.sqlmonster.com