In this video, Mark Niemann-Ross demonstrates reading an entire Excel workbook into R. Learn how to convert Excel workbooks and worksheets into R data structures.
- There are a lot of R packages that you can use to import Excel workbooks and I'm going to show you one of them. It's called Rio and it's the simplest of all to use. Just a note, I've provided a folder full of sample excel files and you'll find it in the Exercise Files, Chapter One. I'll need a list of all of those sample files and to create that list, I'll use the list dot files command putting the results into allSampleFiles.
I'm also going to create a variable called multipleSheetFile, which points to a workbook, which has multiple sheets in it. Now, we're ready to use Rio to import. If you haven't already installed a package, use install packages for Rio and then the library command. Which makes Rio available for our use. Now, to import one Excel worksheet with Rio, I use the import command and that's a Rio command and I'm going to pull in the first of the sample files.
So I'll type in allSampleFiles bracket one and when I hit command return, you can see that I have a list of the contents of an Excel file. Let's take a look at that Excel file in Excel itself and you can see that I have a number of columns, year, cash flow, discount rate, discounted cash flow, followed by a number of rows and that corresponds to the data that we've imported with Rio.
Now, I may want to import a workbook that has multiple sheets. So, to do that, what I'll do is create a vector, let's call it multipleSheetImport and into that vector, I'm going to import a list and that list will come from a file called multipleSheetFile and it's important to use the R Bind option.
R B-I-N-D equals true and this will import the Excel file as a data frame instead of a list and when I hit command return, I now have a data frame called multiSheetImport which contains all of the worksheets from that particular file. There are a lot of packages available to import Excel data and in the exercise file zero one underbar zero two Read Excel workbook underbar end, you'll find a series of examples for different packages that you can use to import Excel workbooks.
- Name the three types of big data. List three considerations used to determine the appropriate R package for Excel. Determine the best package used to import entire Excel workbooks. Explain how to import standard text files using base R and tidyverse. Define the purpose of the foreign language package for R. Recognize restrictions when working on SAS files in the foreign language package. Identify the problems involved with extracting data from a PDF in R.