Excel Export Large File Size

When I export a matrix report to Excel (65 rows by 228 columns, about 15000
cells) the file size exceeds 15mb.
As the Excel file is sent out daily as an e-mail attachment in an SRSS
subscription I receive a lot of complaints about this.
Other export formats for the same report such as Web archive are tiny, but
the subscribers insist on Excel.
I have applied the latest service packs to SQL Server 2005.
Any tips/workarounds would be appreciated.
Regards
Ian OlsenOn Mar 22, 7:47 am, Wrighty <wrig...@.donotspam.com> wrote:
> When I export a matrix report to Excel (65 rows by 228 columns, about 15000
> cells) the file size exceeds 15mb.
> As the Excel file is sent out daily as an e-mail attachment in an SRSS
> subscription I receive a lot of complaints about this.
> Other export formats for the same report such as Web archive are tiny, but
> the subscribers insist on Excel.
> I have applied the latest service packs to SQL Server 2005.
> Any tips/workarounds would be appreciated.
> Regards
> Ian Olsen
The only other options are to use a compression tool to zip the file
before sending it (most likely, not a snapshot option, but a custom
one) or to export to CSV instead (which might save a little file size
but you loose formatting). The only other thing would be to have the
users save a web archive, etc as an excel file. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Excel Destination errors for fields > 255 chars

Hi:

Am trying to write sql2005 data to Excel. I have problems when data length exceeds 255 chars. I used a sample destination file with cells containing >255 chars where required, so that the Excel Destination external column was recognized as DT_Text.

My oledb source columns(external and output) are both varchar (1000).

Works fine , but fails when values>255 chars.

Error 0xC0202009 on ProcessInput.

I use SQL2005 enterprise SP2.

I tried changing the Oledb source output to Textstream, but that does not work at all.

How do I export values >255 chars to excel?

TIA

Kar

Hello,

could you try using Import/Export wizard and saving the package it produces? It will likely use DT_STR on the source, DT_TEXT on the destination and insert a data conversion (DT_STR->DT_TEXT).

HTH.|||

Hi:

Using Varchar(Max) in the SQL table works both ways(excel source and excel destination) without any need to convert. I used the Import wizard as suggested, and alllowed it to create a new SQL table. Thanks for the tip.

Kar

|||

Hmmm, I rejoiced too soon.

Using varchaar(max) in SQL Destination works in the normal case, when the column contains >255 chars in the reference rows (as set by the GuessTypeRows registry value).

However, if the column contains <255 rows, then I get error 0xC0208265 - Failed to retrieve long data for Column.

My problem is that I have multiple xls files from which I import data to SQL. Now most files would have a given column with data >255 chars, but maybe one of the files has that column with all values <255 chars.

I thought I would just convert everything to text, and then use a Text File Source. Problem is that "Excel Automation - Save as text" gives me a series of hashes for cells with >255 chars.

So now I am stuck, and the only inelegant solution is to add a dummy first row to each excel and set guessTypeRows to 1 and force excel to get at least one cell>255 chars for all files..

UGH.

How do I make sure the package works for all cases? Somebody please help me!

TIA
Kar

|||

Well, you can either go with your solution or try to build two packages if you know which Excel files will have <255 char columns.

Thanks.

Excel Destination errors for fields > 255 chars

Hi:

Am trying to write sql2005 data to Excel. I have problems when data length exceeds 255 chars. I used a sample destination file with cells containing >255 chars where required, so that the Excel Destination external column was recognized as DT_Text.

My oledb source columns(external and output) are both varchar (1000).

Works fine , but fails when values>255 chars.

Error 0xC0202009 on ProcessInput.

I use SQL2005 enterprise SP2.

I tried changing the Oledb source output to Textstream, but that does not work at all.

How do I export values >255 chars to excel?

TIA

Kar

Hello,

could you try using Import/Export wizard and saving the package it produces? It will likely use DT_STR on the source, DT_TEXT on the destination and insert a data conversion (DT_STR->DT_TEXT).

HTH.|||

Hi:

Using Varchar(Max) in the SQL table works both ways(excel source and excel destination) without any need to convert. I used the Import wizard as suggested, and alllowed it to create a new SQL table. Thanks for the tip.

Kar

|||

Hmmm, I rejoiced too soon.

Using varchaar(max) in SQL Destination works in the normal case, when the column contains >255 chars in the reference rows (as set by the GuessTypeRows registry value).

However, if the column contains <255 rows, then I get error 0xC0208265 - Failed to retrieve long data for Column.

My problem is that I have multiple xls files from which I import data to SQL. Now most files would have a given column with data >255 chars, but maybe one of the files has that column with all values <255 chars.

I thought I would just convert everything to text, and then use a Text File Source. Problem is that "Excel Automation - Save as text" gives me a series of hashes for cells with >255 chars.

So now I am stuck, and the only inelegant solution is to add a dummy first row to each excel and set guessTypeRows to 1 and force excel to get at least one cell>255 chars for all files..

UGH.

How do I make sure the package works for all cases? Somebody please help me!

TIA
Kar

|||

Well, you can either go with your solution or try to build two packages if you know which Excel files will have <255 char columns.

Thanks.