We have are relatively simple Reporting Services report that our users commonly export to Excel. I've noticed that the files produced by the Excel export seem unusually large. If I open one of these files and just click save, without making any changes, the file size reduces to about half of it's previous size. Has anyone else run into this and is there a known workaround?
views:
6223answers:
6
+3
Q:
What causes Excel export from SQL Server Reporting Services to produce an abnormally large file?
+1
A:
I don't have an immediate solution, but a common problem in Excel is files bloating because one/some/all of the worksheets have saved all 64K rows instead of the ones being used. The fix in Excel is to select all the lower rows not being used, and delete them, then save the spreadsheet, close and reopen. Therefore, I'd pursue the angle of extra rows being saved in the export, and see if there is a way to keep this from happening.
Lance Roberts
2008-11-06 21:14:56
Thanks Lance. I considered that. Once I open the file and save it without changing anything, the size of the file reduces to about half. After that, I've tried deleting extra columns and rows, but that change makes no impact on the size.
Paul G
2008-11-07 13:22:56
It may be setting up a size based on extra rows, columns or code, but then eliminating those extras at some critical point after that setup. This would probably be a hard bug to track down though. Good luck.
Lance Roberts
2008-11-07 17:46:26
A:
What tool are you using when exporting to Excel?
I have also managed to reduce # of rows in my Excel worksheet by copying it to another worksheet, then deleting the original sheet.
You could also try copying only the data in your worksheet, and paste it into a new Excel Workbook (file).
Berzerk
2008-11-14 08:55:15
I'm just using the regular export to excel option from the reporting services server web site. No other tools are involved.
Paul G
2008-11-17 22:23:45
+2
A:
You've mentioned that the report is relatively simple, but this is important to check. The export to Excel will go to extraordinary lengths to try and maintain how your report looks.
If you have lots of different borders or colours (particularly if different formatting is determined by the data in your report) this will bloat the file.
Also check if many columns with very small and unusual sizes are created in the exported worksheet. The export does this to try and match alignment in Excel with the original report.
Try recreating your report as a basic table with no formatting or headers/footers and see if you can reproduce the problem. If Excel's behaviour is acceptable then add each piece of formatting back until it goes awry. Please let us know what you find.
Alex Angas
2008-11-17 16:03:18
Paul G
2008-11-17 19:01:51
OK well I'm out of ideas. Again, try recreating the report. Also look at SQL updates, they may fix this.
Alex Angas
2008-11-18 15:44:40
A:
when i tried to export csv intead of excel text columns are apearing badly with some characters (%'# ext.) how can i fix this problem. if i try to export excel the excel file growing very largely and when i want to open this xls file text and date column values does not appering
A:
May be i am answering your question very late. Here's the solution for exporting to CSV.
You need to give the a name that you want to see as a column header for the field ( not the column name) in the designer.
By default all the text headers are exported as a separate columns along with the table columns and make sure that you name the Design name in the properties with the name you want to see.
The other important thing to note about the option DataElementOutput which is set to Auto meaning it will be exported. You can change that if you don't want it to be exported.
The last but not least thing ... after you export the data looks messed up. You need select the whole first column and go to the Data tab - > convert text to column -> use the delimenter as comma and say Finish. That should solve your issue.
Eranna