Importing Excel files into R xlsx or xls

Question

Please can someone help me on the best way to import an excel 2007   xlsx  file into R  I have tried several methods and none seems to work  I have upgraded to 2 13 1  windows XP  xlsx 0 3 0  I don t know why the error keeps coming up  I tried   AB lt -read xlsx  C  AB DNA Tag Numbers xlsx   DNA Tag Numbers     OR  AB lt -read xlsx  C  AB DNA Tag Numbers xlsx  1    but I get the error    Error in  jnew  java io FileInputStream   file       java io FileNotFoundException  C  AB DNA Tag Numbers xlsx  The system cannot find the file specified    Thank you

User · Answer

Update

As the Answer below is now somewhat outdated, I'd just draw attention to the readxl package. If the Excel sheet is well formatted/lain out then I would now use readxl to read from the workbook. If sheets are poorly formatted/lain out then I would still export to CSV and then handle the problems in R either via read.csv() or plain old readLines().

Original

My preferred way is to save individual Excel sheets in comma separated value (CSV) files. On Windows, these files are associated with Excel so you don't loose the double-click-open-in-Excel "feature".

CSV files can be read into R using read.csv(), or, if you are in a location or using a computer set up with some European settings (where , is used as the decimal place), using read.csv2().

These functions have sensible defaults that makes reading appropriately formatted files simple. Just keep any labels for samples or variables in the first row or column.

Added benefits of storing files in CSV are that as the files are plain text they can be passed around very easily and you can be confident they will open anywhere; one doesn't need Excel to look at or edit the data.

User · Answer

As stated by many here  I am writing the same thing but with an additional point   At first we need to make sure that our R Studio has these two packages installed     readxl   XLConnect    In order to load a package in R you can use the below function   install packages  readxl XLConnect   library XLConnect  search     search will display the list of current packages being available in your R Studio   Now another catch  even though you might have these two packages but still you may encounter problem while reading  xlsx  file and the error could be like  error  more columns than column name   To solve this issue you can simply resave your excel sheet  xlsx  in to       CSV  Comma delimited     and your life will be super easy      Have fun

User · Answer

You may be able to keep multiple tabs and more formatting information if you export to an OpenDocument Spreadsheet file  ods  or an older Excel format and import it with the ODS reader or the Excel reader you mentioned above

User · Answer

Example 2012   library  xlsx   FirstTable  lt - read xlsx  MyExcelFile xlsx   1   stringsAsFactors F  SecondTable  lt - read xlsx  MyExcelFile xlsx   2   stringsAsFactors F     I would try  xlsx  package for it is easy to handle and seems mature enough worked fine for me and did not need any additionals like Perl or whatever   Example 2015    library  readxl   FirstTable   lt - read excel  MyExcelFile xlsx   1  SecondTable  lt - read excel  MyExcelFile xlsx   2     nowadays I use readxl and have made good experience with it  no extra stuff needed good performance

User · Answer

This new package looks nice http   cran r-project org web packages openxlsx openxlsx pdf It doesn t require rJava and is using  Rcpp  for speed

User · Answer

If you are running into the same problem and R is giving you an error -- could not find function   jnew  -- Just install the library rJava   Or if you have it already just run the line library rJava    That should be the problem   Also  it should be clear to everybody that csv and txt files are easier to work with  but life is not easy and sometimes you just have to open an xlsx

User · Answer

I recently discovered Schaun Wheeler s function for importing excel files into R after realising that the xlxs package hadn t been updated for R 3 1 0   https   gist github com schaunwheeler 5825002  The file name needs to have the   xlsx  extension and the file can t be open when you run the function   This function is really useful for accessing other peoples work  The main advantages over using the read csv function are when    Importing multiple excel files Importing large files Files that are updated regularly   Using the read csv function requires manual opening and saving of each Excel document which is time consuming and very boring  Using Schaun s function to automate the workflow is therefore a massive help    Big props to Schaun for this solution

User · Answer

I would definitely try the read xls function in the gdata package  which is considerably more mature than the xlsx package   It may require Perl

User · Answer

For a solution that is free of fiddly external dependencies   there is now readxl      The readxl package makes it easy to get data out of Excel and into R    Compared to many of the existing packages  e g  gdata  xlsx    xlsReadWrite  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       Readxl supports both the legacy  xls format and the modern xml-based    xlsx format   xls support is made possible the with libxls C library    which abstracts away many of the complexities of the underlying binary   format  To parse  xlsx  we use the RapidXML C   library    It can be installed like so   install packages  readxl     CRAN version   or  devtools  install github  hadley readxl     development version   Usage  library readxl     read excel reads both xls and xlsx files read excel  my-old-spreadsheet xls   read excel  my-new-spreadsheet xlsx      Specify sheet with a number or name read excel  my-spreadsheet xls   sheet    data   read excel  my-spreadsheet xls   sheet   2     If NAs are represented by something other than blank cells    set the na argument read excel  my-spreadsheet xls   na    NA       not strictly true  it requires the Rcpp package  which in turn requires Rtools  for Windows  or Xcode  for OSX   which are dependencies external to R  But they don t require any fiddling with paths  etc   so that s an advantage over Java and Perl dependencies    Update There is now the rexcel package  This promises to get Excel formatting  functions and many other kinds of information from the Excel file and into R

User · Answer

What s your operating system   What version of R are you running  32-bit or 64-bit   What version of Java do you have installed   I had a similar error when I first started using the read xlsx   function and discovered that my issue  which may or may not be related to yours  at a minimum  this response should be viewed as  try this  too   was related to the incompatability of  xlsx pacakge with 64-bit Java   I m fairly certain that the  xlsx package requires 32-bit Java   Use 32-bit R and make sure that 32-bit Java is installed   This may address your issue

User · Answer

I have tried very hard on all the answers above  However  they did not actually help because I used a mac  The rio library has this import function which can basically import any type of data file into Rstudio  even those file using languages other than English   Try codes below       library rio      AB  lt - import  C  AB DNA Tag Numbers xlsx       AB  lt - AB  1    Hope this help  For more detailed reference  https   cran r-project org web packages rio vignettes rio html

User · Answer

You may also want to try the XLConnect package  I ve had better luck with it than xlsx  plus it can read  xls files too    library XLConnect  theData  lt - readWorksheet loadWorkbook  C  AB DNA Tag Numbers xlsx   sheet 1    also  if you are having trouble with your file not being found  try selecting it with file choose

User · Answer

For me the openxlx package worked in the easiest way    install packages  openxlsx   library openxlsx  rawData lt -read xlsx  your xlsx

User · Answer

You have checked that R is actually able to find the file  e g  file exists  C  AB DNA Tag Numbers xlsx          Ben Bolker Aug 14  11 at 23 05   Above comment should ve solved your problem   require  xlsx   read xlsx  filepath filename xlsx  1     should work fine after that

[r] Importing Excel files into R, xlsx or xls

Update

Original

Examples related to r

Examples related to xlsx

Examples related to xls