Dear R-experts, It is a quite stupid question but please help me. I am very confuced. I am able to import normal txt ant mat-files to R but unable to import .xls-file I do not understand the online help. Can please anyone send me the corresponding command lines? The .xls-file is attached. In my file we use commas for the decimal format (example: 0,712), changes might be needed. Thanks, Corinna
Corinna Schmitt wrote:> > It is a quite stupid question but please help me. I am very > confuced. I am able to import normal txt ant mat-files to R but > unable to import .xls-file >I've tried two ways to import excel files, but none of them seems perfect. Method 1: This method uses library RODBC. The way to import excel files is this: channel <- odbcConnectExcel("myfile.xls") tables <- sqlTables(channel) # list the names of the spreadsheets name1 <- tables[1, "TABLE_NAME"] # get the name of the 1st spreadsheet plan1 <- sqlFetch(channel, name1) # this _should_ work, but it doesn't # The reason is that somehow the names of the sheets are altered plan1 <- sqlFetch(channel, "sheet name") # this works # but you must type the exact name of the sheet # the next line works, no matter what is name1 (taken from tables) plan1 <- sqlQuery(channel, sprintf("select * from [%s]", name1)) odbcClose(channel) # close it This is not perfect. Some (most?) of the numerical fields in the spreadsheet are translated to NA and become meaningless. Method 2: This method uses library xlsReadWrite. You must know the index of the spreadsheet that you want to load: plan6 <- read.xls(filename, sheet = 6, colClasses="double") This works in most cases.> I do not understand the online help. Can please anyone send me the > corresponding command lines? >help(help) # :-)> The .xls-file is attached. >No, it's not.> In my file we use commas for the decimal format (example: 0,712), > changes might be needed. >I *think* this is an internal flag. If the numbers are numbers, then this should be no problem. An excel spreadsheet in any language is portable to other languages, even when the evil geniuses of M$ decided to localize function names so that, in Portuguese, we have SENO instead of SIN and RAIZ instead of SQRT. Alberto Monteiro
2007/4/18, Schmitt, Corinna <Corinna.Schmitt at igb.fraunhofer.de>:> It is a quite stupid question but please help me. I am very confuced. I > am able to import normal txt ant mat-files to R but unable to import > .xls-fileSearching for Excel on e.g. http://www.r-project.org/search.html, http://tolstoy.newcastle.edu.au/R/about.html or http://wiki.r-project.org/rwiki/doku.php gives: - RODBC package - xlsReadWrite package - gdata package - rexcelpoi package - ActiveX (RDCOMClient package, search for examples in the mailling list) - read.table command to read .csv files I'd take xlsReadWrite (but I am biased), RODBC is also good. ActiveX if you have lower level know how. read.table if working with .csv files is ok.> I do not understand the online help. Can please anyone send me the > corresponding command lines?library(xlsReadWrite) dat <- read.xls( <filename> ) details in ?read.xls>The .xls-file is attached.binary files will be dropped from the list>In my file we > use commas for the decimal format (example: 0,712), changes might be > needed.Don't know if this is relevant. Sorry. -- Regards, Hans-Peter
To avoid complications, save your file as comma separated and use one of the instructions for reading delimited files. If you are using a comma as a decimal point you are probably using ; as a separator. If this is so use read.csv2. Please see the help files for read.table. Best Regards John On 18/04/07, Schmitt, Corinna <Corinna.Schmitt at igb.fraunhofer.de> wrote:> Dear R-experts, > > It is a quite stupid question but please help me. I am very confuced. I > am able to import normal txt ant mat-files to R but unable to import > .xls-file > > I do not understand the online help. Can please anyone send me the > corresponding command lines? The .xls-file is attached. In my file we > use commas for the decimal format (example: 0,712), changes might be > needed. > > Thanks, Corinna > > > ______________________________________________ > 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 > and provide commented, minimal, self-contained, reproducible code. > >-- John C Frain Trinity College Dublin Dublin 2 Ireland www.tcd.ie/Economics/staff/frainj/home.html mailto:frainj at tcd.ie mailto:frainj at gmail.com
Ein eingebundener Text mit undefiniertem Zeichensatz wurde abgetrennt. Name: nicht verf?gbar URL: https://stat.ethz.ch/pipermail/r-help/attachments/20070418/5fab1625/attachment.pl
---------- Forwarded message ---------- From: John C Frain <frainj at gmail.com> Date: 18-Apr-2007 22:35 Subject: Re: [R] importing excel-file To: Alberto Monteiro <albmont at centroin.com.br> One additional suggestion would be to use gretl. Gretl will read excel files with an option to possibly ignore the first few rows and columns and then the data can be exported to an R session started from within Gretl. This will also work in both Windows and Linux. I find it hard to imagine an excel file that can not be read in Open Office or Gnumeric or even Excel and then output to a delimited file in a matter of seconds. In my previous employment I conducted several campaigns against the use of excel formats in favour of a delimited file which can be opened in almost any program. I think that we wouls all be better off if this practice was more widespread. John Frain On 18/04/07, Alberto Monteiro <albmont at centroin.com.br> wrote:> John C Frain wrote: > > > > To avoid complications, save your file as comma separated and use one > > of the instructions for reading delimited files. If you are using a > > comma as a decimal point you are probably using ; as a separator. If > > this is so use read.csv2. Please see the help files for read.table. > > > I think the problem is that we _can't_ alter or write the excel > file, or it would be unpractical to do it (say, this xls file > is generated by someone else once a day, and we must use it). > > I usually save the excel file to plain text, and then read it - but > this is not the best solution when the file keeps changing. > > Alberto Monteiro > >-- John C Frain Trinity College Dublin Dublin 2 Ireland www.tcd.ie/Economics/staff/frainj/home.html mailto:frainj at tcd.ie mailto:frainj at gmail.com -- John C Frain Trinity College Dublin Dublin 2 Ireland www.tcd.ie/Economics/staff/frainj/home.html mailto:frainj at tcd.ie mailto:frainj at gmail.com
The issue Greg mentions, that "most scientists store their experimental data in MS-Excel spreadsheets" is the motivation for one of the sessions at the Interface 2007 conference http://sbm.temple.edu/interface07/index.html in Philadelphia, May 23-26, 2007 Erich and Thomas designed the RExcel interface. Naras uses RExcel. Robert uses the interface between S-Plus and Excel. Accessible Interfaces to Advanced Statistics Software Richard Heiberger, organizer Erich Neuwirth, Thomas Baier, An Office-Software and Menu-Driven Interface for Advanced Statistics in the Biological Sciences Narasimhan Balasubramanian, Disseminating Statistical Methodology and Results via R and Excel: Two Examples Robert Gagnon, Analysis and Visualization of Microarray Gene Expression Data Using Excel, SAS, and SPlus
2007/4/18, Alberto Monteiro <albmont at centroin.com.br>:> Method 2: > This method uses library xlsReadWrite. You must know the index > of the spreadsheet that you want to load: > plan6 <- read.xls(filename, sheet = 6, colClasses="double")it works with the sheet name too! You can write: plan6 <- read.xls(filename, sheet = "sheet name", colClasses="double")> This works in most cases.~~~~~~~~~~~~ Huu? (the package is supposed to work in *all* cases...!). Do you use the newest version (v1.3.1 or v1.3.2)? If there are any bugs/issues, please report them to me and they - most likely - will get fixed. -- Regards, Hans-Peter (author of xlsReadWrite)
Hans-Peter wrote:> >> Method 2: >> This method uses library xlsReadWrite. You must know the index >> of the spreadsheet that you want to load: >> plan6 <- read.xls(filename, sheet = 6, colClasses="double") > > it works with the sheet name too! You can write: > plan6 <- read.xls(filename, sheet = "sheet name", colClasses="double") >Ok, it works.>> This works in most cases. > ~~~~~~~~~~~~ > Huu? (the package is supposed to work in *all* cases...!). >Yes, but I did not test *all* cases to make such a strong assertion O:-)> Do you use > the newest version (v1.3.1 or v1.3.2)? >No, I was using 1.1.1> If there are any bugs/issues, > please report them to me and they - most likely - will get fixed. >Ok - I will do it. Thanks. Alberto Monteiro
Corinna,
I find the easiest way to read simple Excel files (under Windows) is
to highlight the section of the worksheet I want to read, copy it to
the clipboard, and then use
data <- read.delim("clipboard")
You will also need the parameter dec="," to set the decimal point.
Regards,
John Field
Adelaide, SA
Message: 23
Date: Wed, 18 Apr 2007 16:10:51 +0200
From: "Schmitt, Corinna" <Corinna.Schmitt@igb.fraunhofer.de>
Subject: [R] importing excel-file
To: <r-help@stat.math.ethz.ch>
Message-ID:
<8B7B0FD99E8AF541A21609104D1961589128BD@izs-xchg01.izs.fraunhofer.de>
Content-Type: text/plain; charset="us-ascii"
Dear R-experts,
It is a quite stupid question but please help me. I am very confuced. I
am able to import normal txt ant mat-files to R but unable to import
.xls-file
I do not understand the online help. Can please anyone send me the
corresponding command lines? The .xls-file is attached. In my file we
use commas for the decimal format (example: 0,712), changes might be
needed.
Thanks, Corinna
[[alternative HTML version deleted]]
I am coming in late in this discussion because I am away from home and do not read my email regularly. Here are my few cents. If you are working on Windows and have Excel installed, an easy way of transferring data between R and Excel is my RExcel package which ships with Thomas Baier's R(D)COM package, and also can be used with Thomas' rcom package. rcom is available as a package from CRAN and can be installed from the package menu in RGui. R(D)COM is available from CRAN in section Other. You have to download it and run the installer program (and you need admin rights to be able to do this). If you use rcom, you should have R and Excel open simultaneously. Then, just select a range in Excel, right-click any cell in the range, and click on "Put Dataframe" in the context menu that pops up. Your data are immediately transferred to R as a dataframe (you choose that name, or RExcel uses the Excel file name or the worksheet name). The main difference between the R(D)COM server and the rcom package is that with rcom RGui is visible, so you can switch between R and Excel. With R(D)COM, you have R in the background. There is a mailing list, for all rcom and R(D)COM related questions, you can subscribe by way of the rcom web server, http://rcom.univie.ac.at (the link to the mailing list is there). Schmitt, Corinna wrote:> Dear R-experts, > > It is a quite stupid question but please help me. I am very confuced. I > am able to import normal txt ant mat-files to R but unable to import > .xls-file > > I do not understand the online help. Can please anyone send me the > corresponding command lines? The .xls-file is attached. In my file we > use commas for the decimal format (example: 0,712), changes might be > needed. > > Thanks, Corinna > > > > ------------------------------------------------------------------------ > > ______________________________________________ > 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 > and provide commented, minimal, self-contained, reproducible code.-- Erich Neuwirth, Didactic Center for Computer Science University of Vienna Visit our SunSITE at http://sunsite.univie.ac.at Phone: +43-1-4277-39464 Fax: +43-1-4277-9394