The SAS Output Delivery System and reporting techniques

What is the code for creating xlsx extension excel file through ODS?

Reply
Contributor
Posts: 52

What is the code for creating xlsx extension excel file through ODS?

Hi all,

What is the code for creating xlsx extension excel file through ODS?

Whenever i try to use basic ODS command for this purpose, i am unable to create the XLSX file.

Please provide code and solution to this problem.

Thanks

Regards

Super User
Super User
Posts: 7,997

Re: What is the code for creating xlsx extension excel file through ODS?

Posted in reply to dkanand86

There isn't one.  XLSX is an Open Office format document.  That means is a Zip file, with subdirectories and xml files.  Ods uses tagsets to output a single XML file which Excel can read and interpret.  It doesn't matter really what you call the extension, that is just there to indicate the type of data or associate it with a certain program.  If you do:

ods tagsets.excelxp file="abc.xls"

This doesn't create an Excel binary file, but a text file you can right click and open with notepad.

As far as I am aware there is no inbuilt function to write Open Office documents - although theres no reason you couldn't write one yourself if you know how.

Super Contributor
Posts: 394

Re: What is the code for creating xlsx extension excel file through ODS?

Well, there's the ODS destination for Excel, available as "pre-production" in 9.4M2 and production in 9.4M3. This destination produces an .xlsx file. This is the 2nd of our Office Open XML destinations, after the destination for PowerPoint.

You're right, there's no reason you couldn't write your own code to create .xlsx files, except that (to paraphrase J.B.S. Haldane), it's not only harder than you imagine, it's harder than you can imagine. Smiley Wink

Super User
Super User
Posts: 7,997

Re: What is the code for creating xlsx extension excel file through ODS?

Nice to hear there is one coming out (or is out), 9.4 any release is a bit far off for me at the moment though.  I agree its not easy to create XLSX, the actual package is easy with ods package (structure is pretty basic), what always threw me was the lack of debugging, Excel just opens and states it doesn't like the file if there is even the slightest thing and will not open it.

Of course, you could just export CSV data and have a small macro which opens the CSV, and uses Excel SaveAs functionality...

Trusted Advisor
Posts: 3,215

Re: What is the code for creating xlsx extension excel file through ODS?

Posted in reply to dkanand86

Very nice "  it's not only harder than you imagine, it's harder than you can imagine ".

I have seen the open office specs (OASIS) knowing MS added some things to that. Not an easy one to implement.

But they were already published as of 2007. (Office 2007 new type), that is almost 8 years ago.      

---->-- ja karman --<-----
Contributor
Posts: 52

Re: What is the code for creating xlsx extension excel file through ODS?

Then, guys, suggest the sas code for exporting the file in csv format, opening the file and saving it as XLSX format.

Super User
Super User
Posts: 7,997

Re: What is the code for creating xlsx extension excel file through ODS?

Posted in reply to dkanand86

Search is your friend: https://communities.sas.com/message/219824#219824

As for the VB or VBA script to open a csv and save as xlsx, well, that is outside of the remit of a SAS forum but to get started:

This is using VBA within Excel itself, so open an Excel file and run a macro:

Excel VBA Programming - Opening Text and CSV files

Excel VBA open CSV file and import | Making the world simple through software.

And saving as:

http://www.rondebruin.nl/win/s5/win001.htm

You can do the same thing using VB (search for it on Google), been a while since I even looked at it so at a guess, create and office object, then use the inbuilt functions of the object to save/parse/saveas.

Got to ask though, why both with XSLX in the first place, there's no real benefit.

Super User
Posts: 10,046

Re: What is the code for creating xlsx extension excel file through ODS?

Posted in reply to dkanand86

If I was right . SAS9.4 +    ODS EXCEL file='...x,xlsx' ;

Ask a Question
Discussion stats
  • 7 replies
  • 1072 views
  • 0 likes
  • 5 in conversation