Alex Rivera | Logout

Importing Excel files into R, xlsx or xls

Asked 2011-08-13T07:57:37.557
107

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<-read.xlsx("C:/AB_DNA_Tag_Numbers.xlsx","DNA_Tag_Numbers")

OR

AB<-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.

r xlsx xls
Edit
Report

2 Answers

24

obsolete: read.xls has been removed from gdata as of 2024.

from the NEWS file: "Excel file support was first introduced in gregmisc/gdata 1.11.0 back in 2004, but today we have packages such as openxlsx, readxl, XLConnect, and xlsx offering dedicated Excel file support with more features."

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 ...

answered 2011-08-14T23:06:45.123
21

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.

answered 2011-08-13T08:17:13.493

Your Answer