R Programming Ignore Case Sensitivity when reading from Excel Files-CodePudding

I have a line that basically reads an excel file:

MyData <- read_excel("C:/Folder/MySpreadsheet.xlsx", sheet = "Get_Data", skip = 2)

However R is case sensitive, so when the sheet name is written like "GET_DATA", it produces an error that sheet is not found.

The sheet is not always in the same location all the time, and this line of code is inside a loop looking at hundreds of spreadsheets.

Is there a way for R to read the sheet ignoring case sensitivity?

CodePudding user response：

As we are using readxl, using the excel_sheets will be an option to return all the sheets, find the name corresponding to 'Get_Data' with case ignored (ignore.case = TRUE), and use that in reading

library(readxl)
nm <- grep("Get_Data", excel_sheets("C:/Folder/MySpreadsheet.xlsx"), 
          ignore.case = TRUE, value = TRUE)
MyData <- read_excel("C:/Folder/MySpreadsheet.xlsx", sheet = nm, skip = 2)

CodePudding user response：

# Import relevant libraries: 
library(readxl)

# Store a variable denoting the path to the file: 
# spreadsheet_file_path => character scalar
spreadsheet_file_path <- "C:/Folder/MySpreadsheet.xlsx"

# Store a vector of sheet names: 
# sheet_names => character vector
sheet_names <- excel_sheets(spreadsheet_file_path)

# Find the relevant sheetname regardless of case: 
# import_sheet_name => character vector
import_sheet_name <- grep(
  "get_data", 
  sheet_names, 
  ignore.case = TRUE, 
  value = TRUE
)

# Read in the data: MyData => data.frame
MyData <- read_excel(
  spreadsheet_file_path, 
  sheets = import_sheet_name,
  skip = 2
)