R User Group of Milano (Italy)

Read Excel files from R

Many people still save their data into Microsoft Excel files. This is an unhappy choice for many reasons but many was already written about this topic. Furthermore, unfortunately Excel become a de facto standard in many business environment and this routine seems to be difficult to strike out.

Many solutions have been implemented to read Excel files from R: each one has advantages and disadvantages, so an universal solution is not available. Get an overview of all the solutions, allows the choice of the best solution case-by-case.

Save Excel files into text

Saving Excel files into CSV can be done directly from Excel or through some external tools that allows batch operations. Native R functions for text data import can so be used.

Copy and paste from Excel to R

This is a fast solutions, but it has one main drawbacks: it requires to open Excel file, select data and copy. By the way, this is the best compromise when you're in a hurry.

ODBC connection

For many years this was the easiest solutions based on R code for Windows users. Nowadays it still support only 32 bit versions of R and this limit discourage the use of this package. Besides Microsoft Windows and 32-bit R, it requires the Excel ODBC driver installed.

gdata package

In my experience the function provided by gdata package provides a good cross platform solutions. It is available for Windows, Mac or Linux. gdata requires you to install additional Perl libraries. Perl is usually already installed in Linux and Mac, but sometimes require more effort in Windows platforms.

xlsReadWrite package

xlsReadWrite is reported here for didactically purposes only although it is very fast: it doesn't support .xlsx files and this is not acceptable nowadays. Furthermore, it uses proprietary third party code and it should be downloaded from GitHub, CRAN cannot host it. It is available for Windows only.

XLConnect package

XLConnect is a Java-based solution, so it is cross platform and returns satisfactory results. For large data sets it may be very slow.

xlsx package

xlsx package read (and write) .xlsx and .xls files using Java. It is cross platform and uses rJava to deal with Java. Comments and examples below are taken from a stackoverflow answer. It probably returns the best results but requires some more options.

However, read.xlsx() function may be 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:

  • 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.
  • read.xlsx2("filename.xlsx", 1) is faster, but you will have to define column classes manually. A shortcut is to run the command twice. character specification converts your columns to factors. Use Date and POSIXct options for time.

A self made function

Finally, I found on the web a self made function to easily import xlsx files. It should work in all platforms and use XML. It doesn't work with old .xls files. It allows to read more sheets of a file in the same time, using the keep_sheets argument.

I always suggest to read .csv files into R. By the way, for small dataset and testing use, my favourite solution is given by XLConnect.

And you, what do you use to import your own Excel files into R? Please leave a comment!

5 comments

  1. lagvier /

    There is no longer an updated version of xlsReadWrite package (it was my favourite one for reading xls files which none of the above offer).
    Do you know anything about the gnumeric package?

  2. twoclownsfromitaly /

    Given the problems that each possibility you mentioned has, my experience is that the best way to import data from Excel is using RODBC. It's fast even for bigger data sets and the export I usually do using the write.table function. To import the data into Excel you can use VBA to copy the data directly into your excel table or via link. In the latter case there exists the problem that Excel can't update the links to csv so you have to open and close the csv file each time you want to update the data. Below you can find the code I am using:

    require(RODBC)

    tbl_name<-"Table_Name$"
    my_query<-paste('select * from ','[',tbls$TABLE_NAME[which(tbls$TABLE_NAME==tbl_name)],']',sep='')
    data<-sqlQuery(con,my_query,as.is=TRUE)

    data<-data[which(!is.na(data[,1])),]
    data$Date<-strptime(data$Date,format="%Y-%m-%d",tz="")
    data_mtrx<-as.matrix(data[,2:length(colnames(data))],dimnames=list(as.character(data$Date),colnames(data)[2:length(colnames(data))]))
    dimnames(data_mtrx)[[1]]<-as.character(data$Date)
    data<-as.xts(data_mtrx)
    rm("data_mtrx")

    write.table(x,file="file_name.csv",append=FALSE,sep=";",na="NA",dec=",",row.names=FALSE)

  3. byzheng /

    The function wrote by schaunwheeler still is slower to read large sheets (e.g. 10K+ rows). The main constraint is to parse the xml document using xpathApply.

    I modify schaunwheeler function through directly parsing xml with regular expression (https://gist.github.com/byzheng/6119160). The runtime is reduced from 120 s to 18 s to read a 3M xlsx file (one sheet contained 400K values). And it works for me.

  4. EngrStudent /

    I have 64 bit windows and rec'd the following error when trying to use the ODBC alternative:

    > conn = odbcConnectExcel(flist[1])
    Error in odbcConnectExcel(flist[1]) :
    odbcConnectExcel is only usable with 32-bit Windows

    "flist" is a data.table of filenames to read.

Trackbacks/Pingbacks

  1. Para compensar meu silĂȘncio… | De Gustibus Non Est Disputandum - [...] 5. Excel e R: manipulando arquivos [...]
  2. Read Excel files from R | Personal Blog - [...] Article was published on milanor Thanks to [...]
  3. How to open an SPSS file into R | Milano R net - [...] and published in InsulaR blog How to open into R a Microsoft Excel file? Please read again the post ...

Leave a Reply