Excel may not be the most powerful data analysis program, but it is one of the most widely used. It should be no surprise that there are many ways to import Excel data into R, with varying degrees of difficulty. In this article, we describe a simple method to read an Excel file into R: the flipAPI package. This package uses the popular readxl package by Hadley Wickham, with additional features to simplify data importing and formatting.
Installing R package
Because flipAPI does not require external libraries that use Java or Perl, installation is very straightforward. Simply open R and type the following into the console:
Once the flipAPI package is installed, a file can be read in by using the command
Data output format
In many cases, the Excel format contains multiple tables with comments and other text. It is not necessary to reformat the file before importing. We can specify particular sheets or ranges to import.
cola1 = DownloadXLSX("https://wiki.q-researchsoftware.com/images/b/b9/Cola_Discriminant_Functions.xlsx", want.col.names = TRUE, range = "A2:G9") cola2 = DownloadXLSX("https://wiki.q-researchsoftware.com/images/b/b9/Cola_Discriminant_Functions.xlsx", want.col.names = TRUE, want.row.names = FALSE, sheet = 2, range = "AB2:AC330"
To check the result of these command, we type
and see the output:
num [1:7, 1:6] -3.4 2.653 -0.566 -0.458 -0.428 ... - attr(*, "dimnames")=List of 2 ..$ : chr [1:7] "Intercept" "Coca-Cola" "Diet Coke" "Coke Zero" ... ..$ : chr [1:6] "Coca-Cola" "Diet Coke" "Coke Zero" "Pepsi" ...
Similarly, we type str(cola2) and get output:
'data.frame': 328 obs. of 2 variables: $ Highest Score : num 1.1202 1.8786 1.8311 3.6638 0.0754 ... $ Predicted Preferred Cola: Factor w/ 6 levels "Coca-Cola","Coke Zero",..: 6 6 3 1 3 6 2 1 3 2 ...
We can see that DownloadXLSX automatically parses and converts the data into the correct format. cola1, which contains only numeric data, is converted into a matrix, whereas cola2, which has both numeric and categorical data, is converted into a data frame.
Importing Excel files from cloud storage
Another useful feature of DownloadXLSX, which is not supported by the readxl package, is that it can read Excel files directly from the URL. If your file is in cloud storage — such as Dropbox, One Drive or Google Drive — you can simply use the link to that file instead of downloading a copy. Note that most links provided by cloud storage services do not take you directly to the file but instead to a page that shows a preview in your browser. DownloadXLSX will try to get the direct link automatically; however, this is supported only for Dropbox, One Drive and Google Drive.