Hey Seniors,
Really new to R, please has anyone imported a dataset from MS office excel
2007 into R yet? It seem to be giving me hard times which I did not expect as it
use to go well with the previous versions.
Thanks in advance.
arnoldo
---------------------------------
[[alternative HTML version deleted]]
Please read the Posting Guide and provide reproducible code. What do you
mean by "import"? -- via RODBC?... as a delimited text file? ... using
read.table(), scan(),... ???
Bert Gunter
Genentech Nonclinical Statistics
-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org]
On
Behalf Of Arnold Akem
Sent: Tuesday, October 16, 2007 6:38 AM
To: r-help at r-project.org
Subject: [R] Import from excel 2007
Hey Seniors,
Really new to R, please has anyone imported a dataset from MS office excel
2007 into R yet? It seem to be giving me hard times which I did not expect
as it use to go well with the previous versions.
Thanks in advance.
arnoldo
---------------------------------
[[alternative HTML version deleted]]
______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
On Tue, 2007-10-16 at 14:38 +0100, Arnold Akem wrote:> Hey Seniors, > > Really new to R, please has anyone imported a dataset from MS office > excel 2007 into R yet? It seem to be giving me hard times which I did > not expect as it use to go well with the previous versions. > > Thanks in advance. > arnoldoOffice 2007 is using a new format (.xlsx, .docx, etc.) that is not yet compatible with most applications (including OpenOffice), since MS does not document it. The best thing to do is to save the Excel file as a .CSV file and then use read.csv() to import the data. Alternatively, save the file in the older .xls format, which will enable you to use the CRAN packages with functions that support it. It is possible that Prof. Ripley's RODBC package might work, but I don't recall any posts confirming that. HTH, Marc Schwartz
The best way to read or write Excel files is with the RExcel package. The
development package available from
http://sunsite.univie.ac.at/rcom/download/devel/RExcel.installer_1.80-14.zip
reads both Excel 2003 and Excel 2007 files. In this development release,
the installer is structured as an ordinary R package for R on Windows. It
installs an Excel addin and includes complete documentation. RExcel
provides good two-way communication between R and Excel. The normal method
of reading an Excel file is to open the file in Excel and then send the
worksheet
to R from the Excel right-click menu.
Changing formats from Excel to .CSV or anything else has the risk of
scrambling data.
Such workarounds should normally be avoided.
Rich
-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org]
On
Behalf Of Arnold Akem
Sent: Tuesday, October 16, 2007 09:38 AM
To: r-help at r-project.org
Subject: [R] Import from excel 2007
Hey Seniors,
Really new to R, please has anyone imported a dataset from MS office excel
2007 into R yet? It seem to be giving me hard times which I did not expect
as it use to go well with the previous versions.
Thanks in advance.
arnoldo
---------------------------------
[[alternative HTML version deleted]]
______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
> Such workarounds should normally be avoided.You forgot to mention: Excel should normally be avoided. Risk of scrambling data while exporting to a simple ascii formatted text file? Is it a joke?
On Tue, 2007-10-16 at 19:21 +0200, Scionforbai wrote:> > Such workarounds should normally be avoided. > > You forgot to mention: Excel should normally be avoided. > > Risk of scrambling data while exporting to a simple ascii formatted text file? > Is it a joke?No. Excel is notorious for this. See: http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html HTH, Marc Schwartz
On 17/10/2007, at 7:52 AM, Alberto Monteiro wrote:> Scionforbai wrote: >> >> I just wonder: why should R and its community try to support >> such an awful program, with its protected formats and unmantained >> features/bugs? >> > By such logic, why should R be ported to Windows? :-) > > Short answer: because some of the data you want to use > is writen in Excel by people who only knows how to write > Excel spreadshitsI hope this was intentional!!! It certainly is appropriate. :-) cheers, Rolf Turner ###################################################################### Attention:\ This e-mail message is privileged and confid...{{dropped:9}}
On 10/16/07, Marc Schwartz <marc_schwartz at comcast.net> wrote:> On Tue, 2007-10-16 at 14:38 +0100, Arnold Akem wrote: > > Hey Seniors, > > > > Really new to R, please has anyone imported a dataset from MS office > > excel 2007 into R yet? It seem to be giving me hard times which I did > > not expect as it use to go well with the previous versions. > > > > Thanks in advance. > > arnoldo > > > Office 2007 is using a new format (.xlsx, .docx, etc.) that is not yet > compatible with most applications (including OpenOffice), since MS does > not document it.It is documented, but the spec is ~1,200 pages so implementing it is the hard part. Hadley -- http://had.co.nz/
"Marc Schwartz" <marc_schwartz at comcast.net> wrote in message news:1192550659.3526.20.camel at Bellerophon.localdomain...> On Tue, 2007-10-16 at 14:38 +0100, Arnold Akem wrote: >> Hey Seniors, >> >> Really new to R, please has anyone imported a dataset from MS office >> excel 2007 into R yet? It seem to be giving me hard times which I did >> not expect as it use to go well with the previous versions. >> >> Thanks in advance. >> arnoldo > > > Office 2007 is using a new format (.xlsx, .docx, etc.) that is not yet > compatible with most applications (including OpenOffice), since MS does > not document it. > > The best thing to do is to save the Excel file as a .CSV file and then > use read.csv() to import the data.Your question made me curious about RODBC and Excel, so I ran a test using Excel 2007. Using RODBC (Version 1.2-1) with R 2.6.0 with Windows Vista, I cannot read any of the new Excel formats, which there are three: .xlsx - Excel Workbook .xlsb - Excel Binary Workbook .xlsm - Excel Macro-Enable Workbook I get the same error for all three: "ODBC Excel Driver Logic Failed: External table is not in the expected format." When saving new Excel 2007 files, you can select "Save As" and "Excel 97-2003 Workbook" and use those .xls files like before with RODBC in R. But be careful using RODBC and Excel, such as this problem: RODBC and Excel: Wrong Data Type Assumed on Import http://tolstoy.newcastle.edu.au/R/help/05/11/14938.html efg Earl F. Glynn Scientific Programmer Stowers Institute for Medical Research
On Wed, 2007-10-17 at 11:53 -0500, Earl F. Glynn wrote:> "Marc Schwartz" <marc_schwartz at comcast.net> wrote in message > news:1192550659.3526.20.camel at Bellerophon.localdomain... > > On Tue, 2007-10-16 at 14:38 +0100, Arnold Akem wrote: > >> Hey Seniors, > >> > >> Really new to R, please has anyone imported a dataset from MS office > >> excel 2007 into R yet? It seem to be giving me hard times which I did > >> not expect as it use to go well with the previous versions. > >> > >> Thanks in advance. > >> arnoldo > > > > > > Office 2007 is using a new format (.xlsx, .docx, etc.) that is not yet > > compatible with most applications (including OpenOffice), since MS does > > not document it. > > > > The best thing to do is to save the Excel file as a .CSV file and then > > use read.csv() to import the data. > > Your question made me curious about RODBC and Excel, so I ran a test using > Excel 2007. > > Using RODBC (Version 1.2-1) with R 2.6.0 with Windows Vista, I cannot read > any of the new Excel formats, which there are three: > > .xlsx - Excel Workbook > .xlsb - Excel Binary Workbook > .xlsm - Excel Macro-Enable Workbook > > I get the same error for all three: "ODBC Excel Driver Logic Failed: > External table is not in the expected format." > > When saving new Excel 2007 files, you can select "Save As" and "Excel > 97-2003 Workbook" and use those .xls files like before with RODBC in R. > > But be careful using RODBC and Excel, such as this problem: > > RODBC and Excel: Wrong Data Type Assumed on Import > http://tolstoy.newcastle.edu.au/R/help/05/11/14938.htmlThis might be a question along the lines of "is it plugged in?", but do you have the Office 2007 version of the ODBC drivers installed? If not, then your results would not be a surprise. If you do, then we should get Prof. Ripley involved here, as we did have a brief offlist communication on this yesterday, thinking that RODBC "should" work if the proper version of the ODBC drivers are installed. Let us know. Thanks, Marc