Error: Unexpected string constant

R code
1

Hello Everyone!

I am working on a project. The document is an excel document with multiple sheets. I have imported my data but I am trying to tell R to read all the sheets in the excel document. Below is the code i used.

library(readxl)
read_excel_allsheets ← function(“Interactive Map - Surveillance - STI.xlsx”, tibble = FALSE) {
sheets ← readxl::excel_sheets(“Interactive Map - Surveillance - STI.xlsx”)
x ← lapply(sheets, function(X) readxl::read_excel(“Interactive Map - Surveillance - STI.xlsx”
, sheet = X))
if(!tibble) x ← lapply(x, as.data.frame)
names(x) ← sheets
x
}

However, I keep getting an error saying: unexpected string constant in "read_excel_allsheets ← function (“Interactive Map - Surveillance - STI.xlsx”

This is somewhat urgent as I am working on a project.

I have google the error but I have not found much answers. Google is saying i have a double “” but i dont see it in my code.

Thank You in advance for any suggestions!

1 Like
2

Hi @sbrothers ,

Thanks for posting about this situation - it is very common!

One approach (you have likely already considered) is to simply run multiple import() commands to import the sheets one at a time, saved as distinct data frames:

# load {rio} package to import data
pacman::p_load(rio)

# run separate `import()` commands for each sheet
sheet1 <- import("Interactive Map - Surveillance - STI.xlsx", sheet = "sheet1name")
sheet2 <- import("Interactive Map - Surveillance - STI.xlsx", sheet = "sheet2name")
sheet3 <- import("Interactive Map - Surveillance - STI.xlsx", sheet = "sheet3name")

Assuming that the data in each sheet is stored in the same format, and that you want to combine them into one dataframe… there is another option that involves iteration with the {purrr} package and is described in this section of the Epi R Handbook:

# load packages
pacman::p_load(readxl, purrr, tidyverse)

# extract the sheet names and save them as sheet_names
sheet_names <- readxl::excel_sheets("Interactive Map - Surveillance - STI.xlsx")

# pass these names through the import() function one at a time, combine the imported sheets into one data frame, with a new column that contains the sheet of origin

# begin with sheet names
combined <- sheet_names %>%  
  
  # set their names                                            
  purrr::set_names() %>%      

  # iterate, import, save in list                                                   
  map(.f = ~import("Interactive Map - Surveillance - STI.xlsx", which = .x)) %>%   

  # combine list of data frames, preserving origin sheet in new column
  bind_rows(.id = "origin_sheet")

You should now have one data frame containing all the data, with a column containing the sheet of origin!

3 Likes