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.