R – How to Import Excel Workbook from a Folder using Partial Filename [Step-by-Step Guide]

When you have a folder containing multiple Excel workbooks with similar filenames and you want to import a specific workbook into R for analysis or further processing.

This tutorial would be helpful in situations where you don’t have the complete filename but only remember a partial name or pattern that identifies the desired workbook. By following the step-by-step guide, you can efficiently locate and import the correct Excel workbook into R, enabling you to work with the data contained within it.

Step 1: Install and Load the Required Package
First, you need to install and load the “tidyverse” package, which includes “readxl” and other useful packages for data manipulation and analysis. You can install it by running the following command:

install.packages("tidyverse")

Once installed, load the package using the library() function:

library(tidyverse)

Step 2: Find the File Path
Next, locate the folder where the Excel workbook is stored. Make sure you have the complete path to the folder.

folder_path <- "C:/Folder/"  # Specify the path to the folder containing the Excel workbook

Step 3: List Files in the Folder
To find the file with the partial filename, you can list all the files in the folder using the list.files() function. Specify the path to the folder as the argument. For example, if the folder is located at “C:/Folder/”:

file_list <- list.files(path = folder_path)  # List all files in the folder

This will create a character vector (file_list) containing the names of all the files in the folder.

Step 4: Filter Files by Partial Name
Now, filter the file list to include only the files that match the partial filename using the grep() function. Specify the partial filename as the first argument and the vector of file names as the second argument. For example, if the partial filename is “partial_name”:

partial_name <- "partial_name"  # Specify the partial filename to match
matching_files <- grep(partial_name, file_list, value = TRUE)  # Filter files based on partial name

The value = TRUE argument ensures that the function returns the matching file names instead of the indices.

Step 5: Import the Excel Workbook
Finally, import the Excel workbook into R using the read_excel() function from the “tidyverse” package. Specify the full path to the matching file as the argument. For example, if the path to the workbook is “C:/Folder/matching_file.xlsx” we will need to use folder_path and matching_files to create our full path.

file_path <- file.path(folder_path, matching_files)

It’s always best to check if you have more than one match, this way we can bring in the first matching file.

if (length(matching_files) > 0) {
  # Use the first matching file if multiple files match the partial name
  file_path <- file.path(folder_path, matching_files[1])  # Get the full path to the matching file
  data <- readxl::read_excel(file_path)  # Import the Excel workbook into R
  # Additional processing or analysis with the imported data can be performed here
} else {
  # Handle case when no matching file is found
  cat("No matching file found.\n")
}

That’s it! You have successfully imported the Excel workbook into R using the partial filename. The data will be stored in the variable data, which you can further manipulate and analyze in R.

Full code example:

# Step 1: Check and Load the Required Package
if (!require(tidyverse)) {
  install.packages("tidyverse")  # Install the tidyverse package if not already installed
  library(tidyverse)  # Load the tidyverse package
}

# Step 2: Find the File Path
folder_path <- "C:/Folder/"  # Specify the path to the folder containing the Excel workbook

# Step 3: List Files in the Folder
file_list <- list.files(path = folder_path)  # List all files in the folder

# Step 4: Filter Files by Partial Name
partial_name <- "partial_name"  # Specify the partial filename to match
matching_files <- grep(partial_name, file_list, value = TRUE)  # Filter files based on partial name

# Step 5: Import the Excel Workbook
if (length(matching_files) > 0) {
  # Use the first matching file if multiple files match the partial name
  file_path <- file.path(folder_path, matching_files[1])  # Get the full path to the matching file
  data <- readxl::read_excel(file_path)  # Import the Excel workbook into R
  # Additional processing or analysis with the imported data can be performed here
} else {
  # Handle case when no matching file is found
  cat("No matching file found.\n")
}