Path must be a string error when reading an xlsx file in R

Question

library(dplyr)
library(data.table)
library(readxl)

macro <-
  list()

macro$filepath <-
  'C:\\Users\\TMC\\Documents'

macro$file <-
  list.files(
    macro$filepath, 
    pattern = 'EventData.xlsx',
    full.names = TRUE)

macro$sheet <- 
  'FY21'

macro$first_dt <- 
  '2021-02-01' %>% as.Date

dat <-
  macro$file[regexpr('~', macro$file) < 0] %>% 
  read_xlsx(., sheet = macro$sheet) %>% 
  data.table

dat <- 
  dat[!(DESCRIPTION %in% 'AEG')]

macro$keep <-
  c(
    'Product vs. buzz', 
    'CATEGORY', 
    'JUN', 
    'JUL', 
    'AUG', 
    'SEP', 
    'OCT', 
    'NOV', 
    'DEC', 
    'JAN', 
    'FEB', 
    'MAR',
    'APR', 
    'MAY')

dat <- 
  dat[
    , 
    intersect(macro$keep, dat %>% names), 
    with = FALSE]

setnames(
  dat,
  c('Product vs. buzz', 'CATEGORY'), 
  c('evt_type', 'raw'))

dat[, raw := (raw %>% tolower)]

##ISSUE IS HERE
macro$category <-
  list.files(
    macro$filepath, 
    pattern = 'BrandEventCategoryMapping', 
    full.names = TRUE) %>% 
  read_xlsx %>% 
  data.table

Hi all, I am trying to run the above code in R. when I try to read the excel file in the macro$category section I get the following error:

Error: `path` must be a string

Can anyone please help me out and let me know what I should do or edit to fix this? I am not good at R and have to unfortunately run someone else's script for a time sensitive deadline. I would really appreciate some help!

You seem to be passing just a pipe to `lapply` in the last chuck. What exactly are you retrying to iterate over and what function are you trying to apply those values? What does `list.files(macro$filepath, pattern = 'BrandEventCategoryMapping', full.names = TRUE)` return? It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input that can be used to test and verify possible solutions. — MrFlick, Jun 08 '21 at 23:17
Sorry I was trying a fix with the lapply. Its not a part of the code. I have edited the post. — ibarbo, Jun 08 '21 at 23:44
Assigning a bunch of data.tables to a column of a data.frame seems unwise. Can you do a `traceback()` to see if it's `list.files` or `read_xlsx` that is throwing the error? — IRTFM, Jun 09 '21 at 00:56

score 2 · Accepted Answer · answered Jun 09 '21 at 02:58

list.files returns multiple filenames. You cannot read all of them in one single read_xlsx. You may need to use lapply/map to read the files.

library(magrittr)

result <-
  list.files(
    macro$filepath, 
    pattern = 'BrandEventCategoryMapping', 
    full.names = TRUE) %>% 
  purrr::map_df(read_xlsx)
  #If you want them as list use map
  #purrr::map(read_xlsx)

Path must be a string error when reading an xlsx file in R

1 Answers1