Just like a CSV file can be imported and exported in R, we can also import and export an Excel file. If we have data in Excel format, we cannot use read.csv or write.csv functions to import or export it. Instead, we use other functions like read_excel and write_excel for that purpose.
We start by importing an Excel file in R. To import an Excel file in R we use the read_csv functions. As this function is part of readxl package, which is further part of tidyverse set packages, so we need to install and load the package by using following commands
Download Example Fileinstall.packages("readxl")
library(readxl) Once the package has been loaded, we import the Excel file saved by the name of example_data in our current directory. It is important to note that file should be available in the current working directory, because if it’s not, R would give an error. To import the file using read_excel, we use the following command
data <- read_excel("example_data.xlsx") The data file contains 500 observations from 5 variables, including ID, race, married, wage and hours.
If, however, the file isn’t in current working directory and instead is stored somewhere else, you should specify the path of the file in the following way to import that file.
data <- read_excel("E:\\Files\\example_data.xlsx") In this case , the file is saved in Files folder in E drive.
Moving on, if we want to read the data set from a specific sheet in Excel file, it can also be done using the read_excel command. For instance, if we have a sheet by the name of “race” and we just want to import data of that sheet, we will specify the sheet’s name in the command in following way
race <- read_excel("example_data.xlsx", sheet = "race") This command will load the data set for race variable saved in the race sheet in Excel.
Similarly, if we have another sheet in the Excel file saved by the name of “skip”, and we want to import the sheet in such a way that, it not only excludes the first two rows of the sheet, but also includes only specific number of observations in the file while importing it. To carry out these operations, we use the following command
range <- read_excel("example_data.xlsx", sheet = "skip", skip = 2, range = "A3:E100") In the above command, skip=2 specifies to skip first two rows of the sheet and range=A3:E100, specifies the range of observations to be included in the file. The sheet imported will have 97 observations, instead of 500 observations.
Now, if we want to retain only a certain number of rows in the file, we use a parameter with the read_excel function. The parameter will be used in command in following way
maximum_rows <- read_excel("example_data.xlsx", n_max = 10) The n_max=10 specifies only to select 10 rows from the data.
Similarly, if you don’t need headers or column names to be imported, they can be set FALSE in the following way
column_name <- read_excel("example_data.xlsx", col_names = FALSE) That’s all about importing the Excel file in R.
Exporting Excel file in R
When working with an Excel file in R that has already been manipulated, or you have done certain changes in it, it is not possible to export the file directly using the read_excel command. To export the modified Excel file from R, you should use the write_excel function, instead. First, make the necessary changes to the Excel file within your R script, and then export it using the write_excel function.
To use the write_excel package, first install and load the “xlsx” packages by using the following commands
install.packages("xlsx") library(xlsx) Now, if we want to create a sample data frame called missing, we can create it by using the write.xlsx function. We can then export the data frame to an Excel file named “new_file.xlsx” in the current working directory. The command for this operation will be following
write.xlsx(missing,"new_file.xlsx")
If there are missing values present in the data, which are represented by NA, however, we want those values to be replaced by blank or empty space, we use either of the following commands which give the same results
write.xlsx2(missing,"new_file.xlsx") or write.xlsx(missing,"new_file.xlsx", showNA = FALSE,)
We can use the write.excel function with additional options such as specifying the sheet name, including column and row names, preventing appending to an existing file, and setting a password for protection.
To carry out these operations, we use the following command
write.xlsx2(missing,"new_file.xlsx", sheetName = "all_data", col.names = TRUE, row.names = TRUE, append = FALSE, password = "123")
In short, we can export and import Excel files in R. While importing, R can read Excel files into data frames, allowing for easy data manipulation and analysis, and while exporting, R facilitates the creation of Excel files, allowing users to save their data, and results.
