Importing external data stored in *.ods format

[MikeHunter]

I've received data stored in *.ods format.  ODS is part of the OASIS Open Document Format (ODF) family of file formats and is an acronym derived from OpenDocument Spreadsheet.  It is a native spreadsheet format of OpenOffice.org 2.0, XML-based format, usually ZIP-compressed, can support multiple sheets in one file and was  developed by the OASIS industry consortium, based upon the OpenOffice.org SXC format. 

Please note that this data is not from or associated in any way with SAS' ODS reporting, graphics or output formats.

Not surprisingly, Proc Import doesn't support this file type...R has a module that reads it as does Mathematica.

Any suggestions for getting this information into SAS?

[Marina]

If you just want to get information from the file you have into SAS dataset, you can open this file in MS Excel, save it as Excel workbook, and import Excel file.

If you want to import .ods file into SAS dataset - try to contact SAS tech support.

[MikeHunter]

Thanks.  I must have an old version of Excel because my copy doesn't recognize *.ods formatting.  And if I can find no information about ODS data (again, not the ODS graphics or reporting structure that SAS uses) anywhere in online SAS support, then I don't think calling Tech Support will get very far.

[MohammadFayaz]

Hello,

If you can read it in R, you can write it with R too !!

only things you need is write.csv() function or from library(foreign) , write directly to SAS data sets.

for example if you read it in the mydat file in R so you can follow these steps:

write.csv(mydata,"mydata.csv")

then launch SAS and read the mydata.csv with proc import or wizard.

and I don't know how SAS will read ODS data sets directly.

[Tim_SAS · Accepted solution]

Apache OpenOffice supports the ODS spreadsheet format. You could install it (it's free) and use it to open your file and then save it as CSV or XLS.

Also when I search for "convert ods spreadsheet to csv" in Google I see a number of web sites that claim to support converting ODS-format documents to CSV and other formats. Perhaps one of them will do the trick.

[MikeHunter]

Thanks, Tim...that's very helpful.  Mike

[snoopy369]

ODS files are zipped collections of xml files.  You could, in theory, unzip the file and then read in the xml files (content.xml I think is the main one you need; if it's like XLSX [which is an extremely similar format], it may also have a strings.xml for storing text).  It's nontrivial; I wrote an XLSX reader last year and would say for a very skilled SAS programmer it is probably 20-30 hours of work to deal with anything past a trivial example.  On SAS-L, FriedEgg posted a short example that does read in a trivial example at least here: http://listserv.uga.edu/cgi-bin/wa?A2=ind1208a&L=sas-l&P=21161 so perhaps that might be of help if you decide to go that route.  It's possible SAS TS could help you if ODS is sufficiently close to XLSX that you could make it look like XLSX by changing some of the file names inside the zip; not sure.  Certainly getting it in something else that can read and then write a CSV or Excel file would be easier.

[MikeHunter]

Snoop-

  Btw, I followed the link you provided...what a fricking rabbit hole!  Heh heh...

Thanks again,

Michael Thomas

[hollandnumerics]

In 2004 I wrote a conference paper "Help, I've Received a Spreadsheet File from StarOffice Calc.....!", which described how to extract data from SXC files (the predecessor of ODS files).  This paper may help you read the XML files inside the ODS zip file.