ExcelXP Tagset File Size

[Jeff_DOC]

Good morning.

As a new user I would appreciate any help available.

In using the tagests.ExcelXP with bygroup processing my output is xml (even though the extension is xls) and the file size is over 43 mgs. If I bring up the file and save as xlsx it becomes a bit over 3mgs. Using the same file and saving it as xls it becomes about 8mgs. The files, many times, need formatting, so a simple export won't general work (although we've been known to get sick of attempting to solve the issue and just gone that route if feasible). There are hundreds of reports that go out so some type of post processing isn't feasible either.

This problem has plagued us for quite some time since our processing involves automatic reports that are auto-emailed. Our system has a 10 mg limit on attachments so the "normal" tagset version will not work and either of the others will. I would think, surely there must be a minimization strategy for this type of "bloat"?

Thank you all.

Jeff

[Reeza · Accepted solution]

Batch convert to native Excel file using VBS

43496 - Convert files created using an ODS destination to native Excel files

If you have SAS 9.4 can also look into ODS Excel, which may still be experimental.

[Scott_Mitchell]

This assumes that he is running on a Windows server.

I don't believe that you can run VBS on a UNIX box (although I could be wrong).

[RW9]

Hi,

Yes, you need to supply more information.  What system are you generating on, what is your target system, what is the requirement at the customer end.

The tagset output is XML as you mentioned, this is a bloated markup language, not really much you can do about this unless you post process the output file and manually trim out some of the non-essentials, but even then you might lose formatting.

Some suggestions:

Look into compressing your files using other software - ZIP, 7Zip etc.  These can be command line automated from your SAS program and provide very good compression at differing levels.

If formating is not required then drop down to CSV which is about as minimal as you can get for text data, this will also open in Excel fine.

Learn Open Office Format - your XLSX files are actually ZIP files which contains directories and XML files.  With knowledge of these you can create smaller native Open Office documents.

This is not really a suggestion as I don't recommend this approach any more - DDE - create actual binary files which are the old XLS standard.  These are very small, but proprietary so its hard to import/use them outside MS applications and you need SAS/Access to write them which would only be on Windows.

Consider what you are doing, is it the best method?  Perhaps separate data from stylesheet (check out Cascading Style Sheets).  Do you need to send formatting information.

Possibly - though not looked at myself - use other tagset such as Latex.

Oh, and yes, if you have Excel itself, you can write VBA which will open files where specified and loop through saving as .xls which automates the program.  I have posted code on here before for this type of thing.