{"id":770,"date":"2015-02-22T17:11:36","date_gmt":"2015-02-23T00:11:36","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=770"},"modified":"2015-02-22T17:11:36","modified_gmt":"2015-02-23T00:11:36","slug":"read-excel-files-from-r","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2015\/02\/22\/read-excel-files-from-r\/","title":{"rendered":"Read Excel files from R"},"content":{"rendered":"

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<\/em> standard in many business environment and this routine seems to be difficult to strike out.<\/p>\n

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.<\/p>\n

Save Excel files into text<\/h3>\n

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.<\/p>\n

\n
\u00a0df<\/span> =<\/span> read<\/span>.<\/span>table<\/span>(<\/span>\"myfile.csv\"<\/span>,<\/span> header<\/span> =<\/span> TRUE<\/span>)<\/span><\/pre>\n
Copy and paste from Excel to R<\/div>\n<\/div>\n
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.<\/p>\n
\n
\u00a0df<\/span> =<\/span> read<\/span>.<\/span>table<\/span>(<\/span>\"clipboard\"<\/span>)<\/span><\/pre>\n
ODBC connection<\/div>\n<\/div>\n
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.<\/p>\n
\n
\n
require<\/span>(<\/span>RODBC<\/span>)\n<\/span>conn<\/span> =<\/span> odbcConnectExcel<\/span>(<\/span>\"myfile.xlsx\"<\/span>)<\/span> # open a connection to the Excel file\n<\/span>sqlTables<\/span>(<\/span>conn<\/span>)<\/span>$<\/span>TABLE_NAME<\/span> # show all sheets\n<\/span>df<\/span> =<\/span> sqlFetch<\/span>(<\/span>conn<\/span>,<\/span> \"Sheet1\"<\/span>)<\/span> # read a sheet\n<\/span>df<\/span> =<\/span> sqlQuery<\/span>(<\/span>conn<\/span>,<\/span> \"select * from [Sheet1 $]\"<\/span>)<\/span> # read a sheet (alternative SQL sintax)\n<\/span>close<\/span>(<\/span>conn<\/span>)<\/span> # close the connection to the file<\/span><\/pre>\n<\/div>\n
gdata<\/code> package<\/div>\n<\/div>\n
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.<\/p>\n
\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n<\/div>\n<\/td>\n
\n
\n
require<\/span>(<\/span>gdata<\/span>)<\/span><\/div>\n
df<\/span> =<\/span> read<\/span>.<\/span>xls<\/span> (<\/span>“myfile.xlsx”<\/span>)<\/span>,<\/span> sheet<\/span> =<\/span> 1<\/span>,<\/span> header<\/span> =<\/span> TRUE<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n
xlsReadWrite<\/code> package<\/h3>\n
xlsReadWrite<\/code> 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.<\/p>\n
\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/div>\n<\/td>\n
\n
\n
require<\/span>(<\/span>xlsReadWrite<\/span>)<\/span><\/div>\n
xls<\/span>.<\/span>getshlib<\/span>(<\/span>)<\/span><\/div>\n
df<\/span> =<\/span> read<\/span>.<\/span>xls<\/span>(<\/span>“myfile.xls”<\/span>,<\/span> sheet<\/span> =<\/span> 1<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n
XLConnect<\/code> package<\/h3>\n
XLConnect<\/code> is a Java-based solution, so it is cross platform and returns satisfactory results. For large data sets it may be very slow.<\/p>\n
\n
<\/div>\n
\n\n\n\n
\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/div>\n<\/td>\n
\n
\n
require<\/span>(<\/span>XLConnect<\/span>)<\/span><\/div>\n
wb<\/span> =<\/span> loadWorkbook<\/span>(<\/span>“myfile.xlsx”<\/span>)<\/span><\/div>\n
df<\/span> =<\/span> readWorksheet<\/span>(<\/span>wb<\/span>,<\/span> sheet<\/span> =<\/span> “Sheet1”<\/span>,<\/span> header<\/span> =<\/span> TRUE<\/span>)<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n
xlsx<\/code> package<\/h3>\n
xlsx<\/code> 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<\/a>. It probably returns the best results but requires some more options.<\/p>\n
However, read.xlsx()<\/code> function may be slow, when opening large Excel files. read.xlsx2()<\/code> function is considerably faster, but does not quess the vector class of data.frame<\/code> columns. You have to usecolClasses()<\/code> command to specify desired column classes, if you use read.xlsx2()<\/code> function:<\/p>\n