Microsoft Excel (!)

.csv

The nice thing about .csv files is that they tend to be pretty clean. There are two main functions that are part of the utils package (which is loaded by many, many other packages) that can be used to read CSV files into a data frame:

• read.csv() – the simplest form of this function is read.csv("filename.csv"). This assumes the first row in the file is a header.
• read.csv2() – this is identically structured as read.csv(), but is built for use in countries where the comma is used as a decimal point and a semicolon is used as a field separator.

(Clean) .xlsx

A “clean” .xlsx file is one where the data is in a fairly raw form – it’s not, for instance, a dashboard with multiple tables arranged within the workbook. There are many options for bringing this sort of data in:

• In RStudio when you select Import Dataset you can select Excel as one of the options. This may prompt you to load readxl:

• The xlsx package has read.xlsx() and read.xlsx2() functions for specifying the workbook and the sheet from which you wish to bring data in.
• The gdata package has a read.xls() function that works very similarly to the xlsx package.
• The XLConnect package works a little bit differently, in that you first “load” the workbook (with loadworkbook()) and then “read” a workssheet from the workbook with a separate function (readworksheet()).

There are enough quirks across platforms and files that it may take some experimentation to find the package and functions that work best in your situation.

(Messy) .xlsx

The least optimal type of data to bring in from Excel is one where the workbook has been formatted and organized – when the data it includes is not the “raw” data. If the workbook has “presentation layer” worksheets that are set up being highly formatted, while also having “raw data” worksheets that are flat tables, then, if it’s the raw data you need, you can likely use one of the packages described above.

However, if that is not the case, then the jailbreakr package may enable you to reliably import data: http://blog.revolutionanalytics.com/2016/08/jailbreakr.html.

.csv

If you need to export the data to a simple, flat, structure, then you can push it out as a simple .csv file. These two functions – both in the utils package – do the trick quite nicely:

• write.csv() – simply specify what to output and the filename to which to output it. Type ?write.csv for the complete documentation
• write.csv2() – just like read.csv2(), write.csv2() is designed for use in countries where a comma is used for a decimal point and a semicolon is used as the delimiter.

.xlsx

If you have multiple data frames that you want to place on separate tabs in a single workbook, the WriteXLS package (install.packages("WriteXLS")) and then library(WriteXLS)) is one option for doing so. This can get a bit more involved, but it can also be quite useful. Let’s use an example where we have multiple data frame objects named df1, df2, and df3, and we want to output each data frame to its own worksheet in a workbook called my_dataframes.xlsx. We also want to rename the worksheet tabs to be Data Frame 1, Data Frame 2, and Data Frame 3. We would do that with the code below, and even do some light formatting on the output (having the columns automatically adjust their width, making the header row on each worksheet bold, and turning on autofiltering).

# Create a vector with the names of the data frame objects
sheet_data <- c("df1", "df2", "df3")

# Create a vector with the worksheet names we want to use
sheet_names <- c("Data Frame 1", "Data Frame 2", "Data Frame 3")
  
# Write out an Excel file with auto-width columns, a bolded header 
# row, and filters turned on.
WriteXLS(sheet_data,
         ExcelFileName = "my_dataframes.xlsx",
         SheetNames = sheet_names,
         AdjWidth = TRUE, BoldHeaderRow = TRUE, AutoFilter = TRUE)