Active1 year, 10 months ago
- Software Rstudio
- Download Excel Reader On Mac For R-studio Recovery Software
- Rstudio Download For Windows
I am trying to link up my excel data set to R for statistical analysis. I am running on OSX Sierra (10.12.6) with R studio (1.0.153) and Java 8 (update 144).
The function 'read_excel' was able to open my excel document a week ago. When I moved the excel and the R document together to another folder, it no longer worked. Reloading the libraries has had no effect. After multiple attempts (and restarting R studio and computer), something finally worked but function 'lmer' was no longer found. After reloading library 'lme4', 'read_excel' no longer worked!
Excel Reader is a freeware that you can use to Open, view and print any Microsoft Office Excel( Xls,Xlsx) document.It's a quick and convenient way to view Excel files on your computer. Excel Reader from FoxPDF lets you open, view Excel Xls, Xlsx and Rtf files as well as TXT files and it doesn't require Microsoft Excel.It can run as a desktop application in Windows. Mac users interested in Excel viewer for mac os x generally download. Showing results for 'excel viewer' as the words for mac os x are considered too common Cuprum (Community Edition) Free. Cuprum (Community Edition) is a simple multi-layer Gerber and Excellon viewer. RStudio is an active member of the R community. We believe free and open source data analysis software is a foundation for innovative and important work in science, education, and industry. The many customers who value our professional software capabilities help us contribute to this community. Excel Reader.NET Component is a.NET library designed to read Excel Spreadsheet files ( XLS, CSV ) from.NET applications or website projects (C#, VB.NET). The Excel spreadsheet is represented as a tree structure. Free Microsoft Excel Reader Description Excel + is an application for reliably viewing and interacting with Excel documents across platforms and devices. Install the Excel + app to work with Excel documents on your Windows 8 or Windows RT.
I have also tried using 'read.xlsx' and 'readWorksheet(loadWorkbook(...))', which didn't work. 'read.csv' also did not work properly since the commas were creating disorganized columns and I am dealing with a larger excel workbook with ongoing changes.
Reading on Stack, question Importing .xlsx file into R has not resolved my issue! Please help!
Libraries loaded:
R data file:
aosmith23.5k55 gold badges4848 silver badges7676 bronze badges
user8760612user8760612
2 Answers
While you called the library, try and see if adding readxl::read_excel(path = 'yourPath',sheet=1), or even remove the sheet reference. It will automatically take the first sheet.
carlcarl
Perhaps, when you moved the excel and R file to another folder, the pathway should be change either.Try change the pathway, or replace the pathay by
file.choose()
and search the excel file manually.You called the package 'xlsx', which can do the thing what you need. Maybe you're typing it wrong.or
I hope it helps.
Luis AntolinLuis Antolin
Not the answer you're looking for? Browse other questions tagged rmacosreadxl or ask your own question.
Active1 year ago
How can I read an Excel file directly into R? Or should I first export the data to a text- or CSV file and import that file into R?
waanders
waanderswaanders3,9002121 gold badges5757 silver badges9494 bronze badges
12 Answers
Yes. See the relevant page on the R wiki. Short answer:
read.xls
from the gdata
package works most of the time (although you need to have Perl installed on your system -- usually already true on MacOS and Linux, but takes an extra step on Windows, i.e. see http://strawberryperl.com/). There are various caveats, and alternatives, listed on the R wiki page.The only reason I see not to do this directly is that you may want to examine the spreadsheet to see if it has glitches (weird headers, multiple worksheets [you can only read one at a time, although you can obviously loop over them all], included plots, etc.). But for a well-formed, rectangular spreadsheet with plain numbers and character data (i.e., not comma-formatted numbers, dates, formulas with divide-by-zero errors, missing values, etc. etc. ..) I generally have no problem with this process.
David LeBauer17.1k2424 gold badges9797 silver badges170170 bronze badges
Ben BolkerBen Bolker141k1515 gold badges251251 silver badges334334 bronze badges
Let me reiterate what @Chase recommended: Use XLConnect.
The reasons for using XLConnect are, in my opinion:
- Cross platform. XLConnect is written in Java and, thus, will run on Win, Linux, Mac with no change of your R code (except possibly path strings)
- Nothing else to load. Just install XLConnect and get on with life.
- You only mentioned reading Excel files, but XLConnect will also write Excel files, including changing cell formatting. And it will do this from Linux or Mac, not just Win.
XLConnect is somewhat new compared to other solutions so it is less frequently mentioned in blog posts and reference docs. For me it's been very useful.
JD LongJD Long37.8k4646 gold badges174174 silver badges256256 bronze badges
And now there is readxl:
The readxl package makes it easy to get data out of Excel and into R. Compared to the existing packages (e.g. gdata, xlsx, xlsReadWrite etc) readxl has no external dependencies so it's easy to install and use on all operating systems. It is designed to work with tabular data stored in a single sheet.
Software Rstudio
readxl is built on top of the libxls C library, which abstracts away many of the complexities of the underlying binary format.
It supports both the legacy .xls format and .xlsx
readxl is available from CRAN, or you can install it from github with:
Usage
Note that while the description says 'no external dependencies', it does require the
BenBenRcpp
package, which in turn requires Rtools (for Windows) or Xcode (for OSX), which are dependencies external to R. Though many people have them installed for other reasons.33.1k1414 gold badges100100 silver badges185185 bronze badges
EDIT 2015-October: As others have commented here the
openxlsx
and readxl
packages are by far faster than the xlsx
package and actually manage to open larger Excel files (>1500 rows & > 120 columns). @MichaelChirico demonstrates that readxl
is better when speed is preferred and openxlsx
replaces the functionality provided by the xlsx
package. If you are looking for a package to read, write, and modify Excel files in 2015, pick the openxlsx
instead of xlsx
.Pre-2015: I have used
xlsx
package. It changed my workflow with Excel and R. No more annoying pop-ups asking, if I am sure that I want to save my Excel sheet in .txt format. The package also writes Excel files.However, I find
read.xlsx
function slow, when opening large Excel files. read.xlsx2
function is considerably faster, but does not quess the vector class of data.frame columns. You have to use colClasses
command to specify desired column classes, if you use read.xlsx2
function. Here is a practical example:read.xlsx('filename.xlsx', 1)
reads your file and makes the data.frame column classes nearly useful, but is very slow for large data sets. Works also for .xls
files.read.xlsx2('filename.xlsx', 1)
is faster, but you will have to define column classes manually. A shortcut is to run the command twice (see the example below). character
specification converts your columns to factors. Use Date
and POSIXct
options for time.4,11744 gold badges3434 silver badges7373 bronze badges
Download Excel Reader On Mac For R-studio Recovery Software
Given the proliferation of different ways to read an Excel file in
R
and the plethora of answers here, I thought I'd try to shed some light on which of the options mentioned here perform the best (in a few simple situations).I myself have been using
xlsx
since I started using R
, for inertia if nothing else, and I recently noticed there doesn't seem to be any objective information about which package works better.Any benchmarking exercise is fraught with difficulties as some packages are sure to handle certain situations better than others, and a waterfall of other caveats.
That said, I'm using a (reproducible) data set that I think is in a pretty common format (8 string fields, 3 numeric, 1 integer, 3 dates):
I then wrote this to csv and opened in LibreOffice and saved it as an .xlsx file, then benchmarked 4 of the packages mentioned in this thread:
xlsx
, openxlsx
, readxl
, and gdata
, using the default options (I also tried a version of whether or not I specify column types, but this didn't change the rankings).I'm excluding
RODBC
because I'm on Linux; XLConnect
because it seems its primary purpose is not reading in single Excel sheets but importing entire Excel workbooks, so to put its horse in the race on only its reading capabilities seems unfair; and xlsReadWrite
because it is no longer compatible with my version of R
(seems to have been phased out).I then ran benchmarks with
NN=1000L
and NN=25000L
(resetting the seed before each declaration of the data.frame
above) to allow for differences with respect to Excel file size. gc
is primarily for xlsx
, which I've found at times can create memory clogs. Without further ado, here are the results I found:1,000-Row Excel File
So
readxl
is the winner, with openxlsx
competitive and gdata
a clear loser. Taking each measure relative to the column minimum:We see my own favorite,
xlsx
is 60% slower than readxl
.25,000-Row Excel File
Due to the amount of time it takes, I only did 20 repetitions on the larger file, otherwise the commands were identical. Here's the raw data:
Here's the relative data:
So
readxl
is the clear winner when it comes to speed. gdata
better have something else going for it, as it's painfully slow in reading Excel files, and this problem is only exacerbated for larger tables.Two draws of
MichaelChiricoMichaelChiricoopenxlsx
are 1) its extensive other methods (readxl
is designed to do only one thing, which is probably part of why it's so fast), especially its write.xlsx
function, and 2) (more of a drawback for readxl
) the col_types
argument in readxl
only (as of this writing) accepts some nonstandard R
: 'text'
instead of 'character'
and 'date'
instead of 'Date'
.![Mac Mac](https://www.gadgetdaily.xyz/wp-content/uploads/2014/04/Microsoft-Excel.png)
21.8k99 gold badges6666 silver badges128128 bronze badges
I've had good luck with
ChaseChaseXLConnect
: http://cran.r-project.org/web/packages/XLConnect/index.html52.3k1313 gold badges123123 silver badges155155 bronze badges
Brandon BertelsenBrandon Bertelsen26.5k2828 gold badges129129 silver badges236236 bronze badges
Just gave the package
ChrisChrisopenxlsx
a try today. It worked really well (and fast). 1,74155 gold badges2626 silver badges3838 bronze badges
Another solution is the
xlsReadWrite
package, which doesn't require additional installs but does require you download the additional shlib before you use it the first time by :Forgetting this can cause utter frustration. Been there and all that...
Rstudio Download For Windows
On a sidenote : You might want to consider converting to a text-based format (eg csv) and read in from there. This for a number of reasons :
- whatever your solution (RODBC, gdata, xlsReadWrite) some strange things can happen when your data gets converted. Especially dates can be rather cumbersome. The
HFWutils
package has some tools to deal with EXCEL dates (per @Ben Bolker's comment). - if you have large sheets, reading in text files is faster than reading in from EXCEL.
- for .xls and .xlsx files, different solutions might be necessary. EG the xlsReadWrite package currently does not support .xlsx AFAIK.
gdata
requires you to install additional perl libraries for .xlsx support.xlsx
package can handle extensions of the same name.
![Download Excel Reader On Mac For Rstudio Download Excel Reader On Mac For Rstudio](/uploads/1/2/6/0/126097791/998229276.png)
53k2121 gold badges121121 silver badges167167 bronze badges
Joris MeysJoris Meys84.7k2525 gold badges185185 silver badges247247 bronze badges
As noted above in many of the other answers, there are many good packages that connect to the XLS/X file and get the data in a reasonable way. However, you should be warned that under no circumstances should you use the clipboard (or a .csv) file to retrieve data from Excel. To see why, enter
russellpiercerussellpierce=1/3
into a cell in excel. Now, reduce the number of decimal points visible to you to two. Then copy and paste the data into R. Now save the CSV. You'll notice in both cases Excel has helpfully only kept the data that was visible to you through the interface and you've lost all of the precision in your actual source data.3,24322 gold badges2424 silver badges3737 bronze badges
Expanding on the answer provided by @Mikko you can use a neat trick to speed things up without having to 'know' your column classes ahead of time. Simply use
read.xlsx
to grab a limited number of records to determine the classes and then followed it up with read.xlsx2
Example
JasonAizkalnsJasonAizkalns13.3k44 gold badges3636 silver badges8989 bronze badges
An Excel file can be read directly into R as follows:
Reading xls and xlxs files using readxl package
Ashok Kumar JayaramanAshok Kumar Jayaraman1,35411 gold badge1313 silver badges2323 bronze badges
protected by Brad Larson♦Jul 26 '16 at 17:53
Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
Would you like to answer one of these unanswered questions instead?