topic Re: Importing external data stored in *.ods format in SAS Procedures

Importing external data stored in *.ods format

MikeHunter — Thu, 21 Nov 2013 14:58:26 GMT

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?

Re: Importing external data stored in *.ods format

Marina — Thu, 21 Nov 2013 15:27:43 GMT

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.

Re: Importing external data stored in *.ods format

MikeHunter — Fri, 22 Nov 2013 12:32:16 GMT

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.

Re: Importing external data stored in *.ods format

MohammadFayaz — Fri, 22 Nov 2013 15:05:20 GMT

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.

Re: Importing external data stored in *.ods format

snoopy369 — Fri, 22 Nov 2013 16:47:26 GMT

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.

Re: Importing external data stored in *.ods format

MikeHunter — Fri, 22 Nov 2013 23:36:43 GMT

Snoop-

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

Thanks again,

Michael Thomas

Re: Importing external data stored in *.ods format

Tim_SAS — Mon, 25 Nov 2013 14:55:00 GMT

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.

Re: Importing external data stored in *.ods format

MikeHunter — Mon, 25 Nov 2013 16:02:56 GMT

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

Re: Importing external data stored in *.ods format

hollandnumerics — Thu, 23 Jan 2014 18:21:18 GMT

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.