read a xls file
On Tue, 2009-01-20 at 14:45 +0100, Ian Jenkinson wrote:
See the R Import/Export manual. Also
RSiteSearch("import excel")
gives many hits. It seems as if this question is
asked almost daily.
On Sun, Jan 18, 2009 at 9:15 AM, Michele Santacatterina
<miksanta at gmail.com> wrote:
Hello, i have a xls file. I will read it in r, what library-command i use for this?? any ideas??
I feel concerned because I have just spent a frustrating couple of days trying to read an Excel (xls) file, with the aid of the R book (Crawley, 2007), and R help files. I failed, but finally found a workaround. My experience might help others.
You did read ?read.table yes?
There are three arguments there that can help in such situations:
'colClasses' allows the finest grained control over how R imports your
text files. You specify what each column is, noting that if you have
lots of columns, things like
c("numeric", rep("character", 12))
will deal with runs of columns of the same type, without having to type
them all by hand.
'as.is' is a vector of logicals (TRUE/FALSE) that controls whether a
column is read in as is or converted.
'stringsAsFactors' a single logical. Should all character variables be
converted to factors.
Some of these would have been useful in your case.
I'm not sure what you tried, but I have found that saving an .xls file
as a CSV via OpenOffice.org (on Linux) and subsequently reading it in
with read.csv("foo.csv", ...) to be reasonably fool proof, especially
when one makes use of the arguments about for fine-grained processing.
Someone in this thread posted a response that included the use of RODBC,
which I haven't tried, but there are a plethora of ways to read data
from Excel without having to torture yourself and the data formats to do
so.
HTH
G
My data were in an Excel xls file
I have R (version 2.6.2) installed in Kubuntu Linux
I also have R (version 2.6.2) installed in Windows XP SP_3 running in
VirtualBox (a Virtual Computer) in Kubuntu, and I have (very old) Excel
97 on this system.
I wasted a lot of time exporting from Excel in various formats (txt,
csv, dif, tab-delimited, ;-delimited ,-delimited, etc.). (I checked they
were of correct format by peeking with a text editor.)
Then I would try reading using e.g. read.table("[file
path]",header=TRUE) or read.csv(...) or read.csv2(...), or
read.DIF(...), with or without "header=TRUE" or "header =FALSE".
I also copied to the "clipboard" and tried reading using
read.DIF("clipboard")
In many of these cases I did get a data.frame that looked nice on-screen.
My recurrent problem, however, was that many of the numeric variables in
the resultant data frame were CLASS "factor". If you do arithmetic or
plotting on factors, either it fails or gives wrong results.
So I spent hours using (as.numeric(...)) with variants and permutations,
etc. Most times (as.numeric(...)) seems to work, but actually the data
either remained unchanged (as a "factor") or gave "numeric" but wrong
numbers.
I read the xls file using gnumeric application and saved as a dif file,
then used read.DIF("[file path]"). This gave some correct "numeric"
numbers but jumbled and partly duplicated.
N.B. My problems were essentially the same whether I used R in XP or in
Linux (kubuntu)
MY SOLUTION (working in Linux):
Read the Excel file (xls) using Open Office.org (version 2.4.1)
(downloadable for free for Linux or Windows).
Save as dif file.
In R, TT<-read.DIF("[file path]",header=TRUE)
It worked, and all my numerical data elements were "numeric", correct
and in the right order. Omit "header=TRUE" if you don't want the first
elements of the spreadsheet columns declared as headers.
Hope this may help someone.
Here's a subset of my data in a data.frame (environmental data on
plankton):
>TT
Stn Day Mean.salinity Mean.temperature Secchi.disc. Log.microplank.biomass 1 1 12 0 14 0.7 1.954242509 2 1 70 13.5 16.55 0.3 3.083860801 3 1 93 13.45 16.85 0.6 2.651278014 4 1 153 6.78 14.2 0.5 2.075546961 5 1 200 0 9.3 0.7 1.612783857 6 1 231 0 7.1 0.8 1.491361694 7 1 283 0 8.8 0.4 2.123851641 8 1 330 4.95 9.45 0.3 2.276461804 9 1 370 16.6 12.3 0.4 2.728353782 10 3 12 16.25 11.95 0.55 2.025305865 11 3 70 22.35 16.1 0.5 2.096910013 12 3 93 26.05 17.15 1.5 1.707570176 13 3 153 23.4 14.2 1 1.755874856 14 3 200 14.05 8.6 0.4 1.812913357 15 3 231 7.9 6.3 0.3 1.897627091 16 3 283 11.2 7.25 0.7 1.832508913 17 3 330 19.95 8.1 0.5 1.785329835 18 3 370 24.35 11.5 0.4 2.361727836 19 4 12 18.1 12.05 0.6 1.792391689 20 4 70 24.35 15.9 0.7 1.973127854 21 4 93 27 17.35 1.3 1.982271233 22 4 153 25.8 14.2 0.8 1.924279286 23 4 200 16.2 9 0.4 1.653212514 24 4 231 11.5 6.85 0.4 1.819543936 25 4 283 10.95 8.2 0.25 2.096910013 26 4 330 19.7 8.45 0.4 2.025305865 27 4 370 25.6 11.5 0.5 2.274157849 Ian Jenkinson
______________________________________________ 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.
%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~% Dr. Gavin Simpson [t] +44 (0)20 7679 0522 ECRC, UCL Geography, [f] +44 (0)20 7679 0565 Pearson Building, [e] gavin.simpsonATNOSPAMucl.ac.uk Gower Street, London [w] http://www.ucl.ac.uk/~ucfagls/ UK. WC1E 6BT. [w] http://www.freshwaters.org.uk %~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%