BookmarkSubscribeRSS Feed
dkanand86
Calcite | Level 5

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Tim_SAS
Barite | Level 11

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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...

jakarman
Barite | Level 11

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 --<-----
dkanand86
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1885 views
  • 0 likes
  • 5 in conversation