Loading data from Excel file into R or Octave

Question

I have an Excel file with a large set of data. The built-in graphs available in Excel are not enough to analyze these data, so I am thinking about using some tool like octave or R.

I was thinking about some method to load an Excel file directly into octave or R. I searched the web and found that many people have succeeded using by exporting data from Excel into a CSV file.

The question: Is there a direct way to load an Excel file in R or Octave?

Kevon

Answer 1

Yes, here's how:

Windows users can use odbcConnectExcel in package RODBC. This can select rows and columns from any of the sheets in an Excel spreadsheet file (at least from Excel 97–2003, depending on your ODBC drivers: by calling odbcConnect directly versions back to Excel 3.0 can be read). The version odbcConnectExcel2007 will read the Excel 2007 formats as well as earlier ones (provided the drivers are installed: see RODBC).

Ahlgren

Answer 2

Another (and even portable) option is read.xls in the gdata package. It is available cross-platform as it relies on underlying Perl code to parse, read, ... data from the xls file into a csv file that is then read. The required Perl packages are all provided by gdata.

An Octave package could easily be created using the same trick.

Greenness

Answer 3

Your question has already been answered. In case this helps for some future case, a really useful resource for these types of questions is the R Data Import/Export document which covers a lot of useful API, packages, tips, etc for accessing data from some common databases and file formats.

For example, there's an Excel section, which covers a lot of the ground in the answers already provided.

Another useful tip is to try the R help search system. For example, try either of the following from the R prompt:

> ??xls
> ??excel

EDIT: BTW, ??xls is short for help.search("xls").

Panto

+1, thanks for pointing me to this document, Actually for unknown reason, I was more inclined towards octave, and did not concentrate on R. But, I will now starting using R ;-) ... – Kevon

Answer 4

The easiest way is for your data to be in the form of a CSV file, and then use read.csv() to read in the data. Be aware that when you read in the data, that R will read in character strings and convert them into factors. This can become an issue when you try to subset, the data, etc.

Alternatively, if you would like to read it in as a native Excel file, then you can use the RODBC package.

library('RODBC')
yourData <- sqlFetch(odbcConnectExcel("yourData.xls"), 
  sqtable = "nameOfSheet",
  na.strings = "NA", 
  as.is = TRUE)                    
odbcCloseAll()

Buskus

Answer 5

An even simpler solution for Windows is to use the xlsReadWrite package. No need for Perl, just install as an R package and you can read and write Excel files to your heart's content.

Savarin

However, it is neither Open Source nor portable. – Greenness

Answer 6

Now we can used read.csv or read.table.

For example

df = read.csv("~/data/demo.csv",header = TRUE)

Linguiform