Hi! Is it possible to easily cut & paste data from an Excel spreadsheet to an R edit( ) grid or to variable? It seems that R cannot handle the cell delimiters Excel hands over. Regards, Werner
Werner Wernersen wrote:> Hi! > > Is it possible to easily cut & paste data from an > Excel spreadsheet to > an R edit( ) grid or to variable? > It seems that R cannot handle the cell delimiters > Excel hands over. > > Regards, > Werner > > ______________________________________________ > R-help at stat.math.ethz.ch mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide! http://www.R-project.org/posting-guide.htmlcopy in Excel and say in R: read.table(file("clipboard")) Uwe Ligges
I've had good luck with the scan() function when I want to get a few numbers from Excel into R quickly to use it as a calculator. CAVEAT: you have to have the numbers you want to copy in a column not a row in Excel. For example: In Excel your data are in a column as follows: Col A 1 2 3 Then copy the 3 cells (e.g. 1, 2,3) in Excel and open R and type in:> data <- scan()Then Paste using Ctrl-V. Hit the Enter key. You know have an object called "data" that you can use and manipulate in R. I've taken this even further by creating an R function that will take a column of numbers from Excel and then scan() them into R, create a matrix, and then perform a Chi-square test. Let me know if you'd like to know more. I'm a beginner and if I can do so can you!! ~Nick
Thank you all very much for the answers!
The read.table / read.delim2 commands are exactly what
I was looking for to get
a couple of numbers or a little matrix quickly into R
without creating an extra
text file every time.
And it works the other way around as well:
write.table(x, file("clipboard"), sep="\t")
Fantastic!
Thanks again,
Werner
Nick Drew wrote:> I've had good luck with the scan() function when I
> want to get a few numbers from Excel into R quickly
to> use it as a calculator. CAVEAT: you have to have the
> numbers you want to copy in a column not a row in
> Excel. For example:
>
> In Excel your data are in a column as follows:
> Col A
> 1
> 2
> 3
>
> Then copy the 3 cells (e.g. 1, 2,3) in Excel and
open> R and type in:
>
>>data <- scan()
>
>
> Then Paste using Ctrl-V. Hit the Enter key. You know
> have an object called "data" that you can use and
> manipulate in R.
>
> I've taken this even further by creating an R
function> that will take a column of numbers from Excel and
then> scan() them into R, create a matrix, and then
perform> a Chi-square test. Let me know if you'd like to know
> more. I'm a beginner and if I can do so can you!!
>
> ~Nick
>
>
>
>
> __________________________________
Hi, I tried the interesting suggestion below, discussed in several postings yesterday on the help-list, on my Mac (0S 10.3.7) but could not get it to work, as shown in the tests indicated below.>> >> read.table(file("clipboard"), sep="\t", dec=",") >>If it is obvious (even, if not), can someone tell me what I am doing wrong? Do I need to perform an additional operation to open "clipboard", or is this option not available on Mac? I did not find any special discussion of this in the FAQ nor searching under "clipboard" in the archives. Thank you, in advance, for any enlightenment. data.frame(matrix(rnorm(12),ncol=3)) X1 X2 X3 1 0.4276964 -0.49584891 0.02150469 2 -0.8323586 -0.40120649 -1.90733346 3 -0.8954563 -1.33195844 -1.28261484 4 0.4772382 -0.03703087 0.46719156 #At this point, I block-marked the printed output and apple-C'd it into the clipboard. #I then checked the clipboard to verify that the data was indeed copied there.> read.table("clipboard")Error in file(file, "r") : unable to open connection In addition: Warning message: cannot open file `clipboard'> read.table(file("clipboard"))Error in open.connection(file, "r") : unable to open connection In addition: Warning message: cannot open file `clipboard'> read.table(file("clipboard","r"))Error in file("clipboard", "r") : unable to open connection In addition: Warning message: cannot open file `clipboard'> read.table(file("clipboard","r"),header=TRUE)Error in file("clipboard", "r") : unable to open connection In addition: Warning message: cannot open file `clipboard'>read.delim(file("clipboard","r"),header=TRUE)Error in file("clipboard", "r") : unable to open connection In addition: Warning message: cannot open file `clipboard'> file("clipboard")description class mode text opened can read "clipboard" "file" "r" "text" "closed" "yes" can write "yes"> file("clipboard","r")Error in file("clipboard", "r") : unable to open connection In addition: Warning message: cannot open file `clipboard' platform powerpc-apple-darwin6.8 arch powerpc os darwin6.8 system powerpc, darwin6.8 status major 2 minor 0.1 year 2004 month 11 day 15 language R ____________________ Ken Knoblauch Inserm U 371 Cerveau et Vision 18 avenue du Doyen Lepine 69675 Bron cedex France tel: +33 (0)4 72 91 34 77 fax: +33 (0)4 72 91 34 61 portable: 06 84 10 64 10
Here is something quick & dirty for Mac that may be serviceable in
some cases, while awaiting someone with greater understanding of
programming connections than I have currently.
With the following copied to the clipboard from Excell:
H T Q F
1 2 3.3 a
3 5 10.2 b
5 9 11 A
I tried in R:
read.table(pipe("pbpaste"),header=TRUE)
H T Q F
1 1 2 3.3 a
2 3 5 10.2 b
3 5 9 11.0 A
Warning message:
incomplete final line found by readTableHeader on `pbpaste'
> str(read.table(pipe("pbpaste"),header=TRUE))
`data.frame': 3 obs. of 4 variables:
$ H: int 1 3 5
$ T: int 2 5 9
$ Q: num 3.3 10.2 11
$ F: Factor w/ 3 levels "A","a","b": 2 3 1
Warning message:
incomplete final line found by readTableHeader on `pbpaste'
I haven't been able to track down readTableHeader yet. The warning
occurs even without headers in the data.
Quoting Prof Brian Ripley <ripley at stats.ox.ac.uk>:
> On Thu, 17 Feb 2005, Uwe Ligges wrote:
>
> > Ken Knoblauch wrote:
> >
> >> I tried the interesting suggestion below, discussed in several
postings
> >> yesterday on the help-list, on my Mac (0S 10.3.7) but could not
get it
> to
> >> work, as shown in the tests indicated below.
> >>
> >>
> >>>> read.table(file("clipboard"),
sep="\t", dec=",")
> >
> > Connections to the clipboard are only available on Windows.
>
> Ken is of course welcome to contribute them for MacOS X (or indeed for
> X11).
> People do take for granted the work the developers do to provide such
> things ....
>
> --
> Brian D. Ripley, ripley at stats.ox.ac.uk
> Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/
> University of Oxford, Tel: +44 1865 272861 (self)
> 1 South Parks Road, +44 1865 272866 (PA)
> Oxford OX1 3TG, UK Fax: +44 1865 272595
>
____________________
Ken Knoblauch
Inserm U 371
Cerveau et Vision
18 avenue du Doyen Lepine
69675 Bron cedex
France
tel: +33 (0)4 72 91 34 77
fax: +33 (0)4 72 91 34 61
portable: 06 84 10 64 10
In fact, I noticed today that when I copied from an Excell spreadsheet, rather than just putting some text in a file, that it worked as you say. I also downloaded xclip and compiled it, which works just fine on the Mac, and this mechanisms seems to work the same way as pbpaste, in terms of generating or not warnings under the same circumstances. xclip has slightly different options than pbpaste (e.g., you can access 3 different pasteboards) and it would seem to cover the X11 situation. So, it represents a solution that is slightly more general, as applying to both Mac and linux. Quoting Don MacQueen <macq at llnl.gov>:> I tried Ken's suggestion > read.table(pipe("pbpaste"),header=TRUE) > on my Mac OS X system and it worked *without* generating any warning > message. > > If my experience represents the norm, and Ken's is the exception, it > is so simple that no further contribution to R is needed, I would > say. Thank you, Ken. > > The method can also be to go the other way, using pbcopy instead of > pbpaste. > Emulating an example found in R's help under ?'pipe': > > > test <- data.frame(a=1:3,b=letters[1:3]) > > zz <- pipe('pbcopy','w') > > write.table(test,file=zz,sep='\t',row.names=FALSE) > > close(zz) > > Then in Excel (or any other Mac-native application) use the Paste command. > > > In the past I would get the warning message that Ken reports when I > used read.delim() on tab-delimited files > created using Excel's "Save as tab delimited" option. Excel does not > put a newline at the > end of the last line, and as a result R would generate that error > message. Excel still does not. > However, R now reads such files correctly, without generating the > warning message. > > pbpaste and pbcopy are included in the OS as distributed by Apple > > [163]% which pbpaste > /usr/bin/pbpaste > > (though perhaps only if one of the optional developer-related > packages has been installed) > so these methods should be available to all Mac users of R, without > any extra work on their part > (other than learning about them, that is). > > By the way, there doesn't appear to be open connection left behind: > > > bah <- read.table(pipe('pbpaste'),header=TRUE) > > dim(bah) > [1] 21 5 > > showConnections(all=TRUE) > description class mode text isopen can read can write > 0 "stdin" "terminal" "r" "text" "opened" "yes" "no" > 1 "stdout" "terminal" "w" "text" "opened" "no" "yes" > 2 "stderr" "terminal" "w" "text" "opened" "no" "yes" > > > > version > _ > platform powerpc-apple-darwin6.8.5 > arch powerpc > os darwin6.8.5 > system powerpc, darwin6.8.5 > status > major 2 > minor 0.1 > year 2004 > month 11 > day 15 > language R > > Mac OS 10.3.8 > > Excel 2004, version 11.1 (040909) > > -Don > > At 11:09 PM +0100 2/17/05, Ken Knoblauch wrote: > >Here is something quick & dirty for Mac that may be serviceable in > >some cases, while awaiting someone with greater understanding of > >programming connections than I have currently. > > > >With the following copied to the clipboard from Excell: > >H T Q F > >1 2 3.3 a > >3 5 10.2 b > >5 9 11 A > > > >I tried in R: > > > >read.table(pipe("pbpaste"),header=TRUE) > > H T Q F > >1 1 2 3.3 a > >2 3 5 10.2 b > >3 5 9 11.0 A > >Warning message: > >incomplete final line found by readTableHeader on `pbpaste' > >> str(read.table(pipe("pbpaste"),header=TRUE)) > >`data.frame': 3 obs. of 4 variables: > > $ H: int 1 3 5 > > $ T: int 2 5 9 > > $ Q: num 3.3 10.2 11 > > $ F: Factor w/ 3 levels "A","a","b": 2 3 1 > >Warning message: > >incomplete final line found by readTableHeader on `pbpaste' > > > >I haven't been able to track down readTableHeader yet. The warning > >occurs even without headers in the data. > > > > > >Quoting Prof Brian Ripley <ripley at stats.ox.ac.uk>: > > > >> On Thu, 17 Feb 2005, Uwe Ligges wrote: > >> > >> > Ken Knoblauch wrote: > >> > > >> >> I tried the interesting suggestion below, discussed in several > postings > >> >> yesterday on the help-list, on my Mac (0S 10.3.7) but could not get > it > >> to > >> >> work, as shown in the tests indicated below. > >> >> > >> >> > >> >>>> read.table(file("clipboard"), sep="\t", dec=",") > >> > > >> > Connections to the clipboard are only available on Windows. > >> > >> Ken is of course welcome to contribute them for MacOS X (or indeed for > >> X11). > >> People do take for granted the work the developers do to provide such > >> things .... > >> > >> -- > >> Brian D. Ripley, ripley at stats.ox.ac.uk > > > Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ > >> University of Oxford, Tel: +44 1865 272861 (self) > >> 1 South Parks Road, +44 1865 272866 (PA) > >> Oxford OX1 3TG, UK Fax: +44 1865 272595 > >> > > > > > > > >____________________ > >Ken Knoblauch > >Inserm U 371 > >Cerveau et Vision > >18 avenue du Doyen Lepine > >69675 Bron cedex > >France > >tel: +33 (0)4 72 91 34 77 > >fax: +33 (0)4 72 91 34 61 > >portable: 06 84 10 64 10 > > > >______________________________________________ > >R-help at stat.math.ethz.ch mailing list > >https://stat.ethz.ch/mailman/listinfo/r-help > >PLEASE do read the posting guide! > http://www.R-project.org/posting-guide.html > > > -- > -------------------------------------- > Don MacQueen > Environmental Protection Department > Lawrence Livermore National Laboratory > Livermore, CA, USA > -------------------------------------- >____________________ Ken Knoblauch Inserm U 371 Cerveau et Vision 18 avenue du Doyen Lepine 69675 Bron cedex France tel: +33 (0)4 72 91 34 77 fax: +33 (0)4 72 91 34 61 portable: 06 84 10 64 10
You are right. The warning disappears at exactly five lines of data, not including the header. Well, how often do you come across a data.frame with less than 5 rows and too many covariates to enter by hand? kk Quoting Peter Dalgaard <p.dalgaard at biostat.ku.dk>:> Don MacQueen <macq at llnl.gov> writes: > > > I tried Ken's suggestion > > read.table(pipe("pbpaste"),header=TRUE) > > on my Mac OS X system and it worked *without* generating any warning > message. > > My conjecture is that it only happens when there are fewer than 5 data > lines. > > We still need to sort out X11. Too bad that the xclip program isn't > ubiquitous. > > -- > O__ ---- Peter Dalgaard Blegdamsvej 3 > c/ /'_ --- Dept. of Biostatistics 2200 Cph. N > (*) \(*) -- University of Copenhagen Denmark Ph: (+45) 35327918 > ~~~~~~~~~~ - (p.dalgaard at biostat.ku.dk) FAX: (+45) 35327907 >____________________ Ken Knoblauch Inserm U 371 Cerveau et Vision 18 avenue du Doyen Lepine 69675 Bron cedex France tel: +33 (0)4 72 91 34 77 fax: +33 (0)4 72 91 34 61 portable: 06 84 10 64 10