The SAS Output Delivery System and reporting techniques

Excel file size when using ODS

Accepted Solution Solved
Reply
Regular Contributor
Posts: 151
Accepted Solution

Excel file size when using ODS

I'm transferring a SAS dataset to Excel using the ODS ExcelXP tagset.  The spreadsheet created is 41MB, however if I open it, save it without making any changes, then close it, the file size reduces to 34MB.  I assume this is due to some form of compression, but does anyone know if this can be done automatically as part of the ODS process?


Accepted Solutions
Solution
‎05-23-2012 10:18 AM
Respected Advisor
Posts: 3,156

Re: Excel file size when using ODS

Excelxp tagset ODS outputs 'XML' files (sometime faked with xls name), not 'true' 'xls' files. XML is some sort of 'formated' text file that both human and machine can read. While XLS is binary file, which is much more compact. So when you resave it, its structure may have been modified to true XLS file. That is why there is a need to shrink XML file using third party software or VBA scripts. I usually find it can shrink to 1/5 or more after conversion.

Haikuo

View solution in original post


All Replies
Occasional Contributor
Posts: 14

Re: Excel file size when using ODS

I don't think you can do this.  What you are describing seems to occur when you save a .xls to a .xlsx in Excel.  I don't believe the ExcelXP tagset can write .xlsx files directly.

You may try to use a different tagset which can write .xlsx files if one exists.  Or you could write a VBA macro after to go and convert all your sheet to xlsx.

Sorry if this was off the mark, but this is my best guess to what is happening.

Regular Contributor
Posts: 151

Re: Excel file size when using ODS

Posted in reply to Spacetime

Thanks for your reply Spacetime.  I am using Excel 2003, so the files are xls, not xlsx.

Occasional Contributor
Posts: 14

Re: Excel file size when using ODS

Yeah no problem, now I see from the other responses it's due to the xml structure, and not the xlsx.  I would always wonder why I would save them as xlsx and they would shrink, but the same thing would have happened with just straight xls.  This is helpful to solidify my understanding here.

Solution
‎05-23-2012 10:18 AM
Respected Advisor
Posts: 3,156

Re: Excel file size when using ODS

Excelxp tagset ODS outputs 'XML' files (sometime faked with xls name), not 'true' 'xls' files. XML is some sort of 'formated' text file that both human and machine can read. While XLS is binary file, which is much more compact. So when you resave it, its structure may have been modified to true XLS file. That is why there is a need to shrink XML file using third party software or VBA scripts. I usually find it can shrink to 1/5 or more after conversion.

Haikuo

Regular Contributor
Posts: 151

Re: Excel file size when using ODS

Thanks Hai.kuo, I guessed it may be something like that.  I suppose it's similar to the old days of using ODS HTML to create an .xls file.  With that method you had to click 'Save As' in Excel and choose Excel Workbook as the file type.

Super User
Posts: 11,343

Re: Excel file size when using ODS

And since this is a result of the "File Save As" in Excel it isn't possible directly with ODS. A work around using DDE is possible but is fragile as Excel version, file types and command syntax changes. It was worth it to me as I had hundreds of files to do the "File Save As" to.

See https://communities.sas.com/thread/34669

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 672 views
  • 0 likes
  • 4 in conversation