CODEWITHSUNDEEP
rparsingeval
Tom
Tom

Reputation: 2351

Parsing issue, unexpected character when loading a folder

I am using this answer to load in a folder of Excel Files:

# Get the list of files
#----------------------------#
  folder <- "path/to/files"
  fileList <- dir(folder, recursive=TRUE)  # grep through these, if you are not loading them all

  # use platform appropriate separator
  files <- paste(folder, fileList, sep=.Platform$file.sep)

So far, so good.

# Load them in
#----------------------------#
  # Method 1:
  invisible(sapply(files, source, local=TRUE))

  #-- OR --#

  # Method 2:
  sapply(files, function(f) eval(parse(text=f)))

But the source function (Method 1) gives me the error:

Error in source("C:/Users/Username/filename.xlsx") : 
  C:/Users/filename :1:3: unexpected input
1: PK
      ^

For method 2 get the error:

Error in parse(text = f) : <text>:1:3: unexpected '/'
1: C:/
      ^

EDIT: I tried circumventing the issue by setting the working directory to the directory of the folder, but that did not help.

Any ideas why this happens?

EDIT 2: It works when doing the following:

How can I read multiple (excel) files into R?

setwd("...")
library(readxl)
file.list <- list.files(pattern='*.xlsx')
df.list <- lapply(file.list, read_excel)

Upvotes: 0

Views: 235

Answers (1)

Edo
Edo

Reputation: 7858

just to provide a proper answer outside of the comment section...

  • If your target is to read many Excel files, you shouldn't use source.
  • source is dedicated to run external R code.
  • If you need to read many Excel files you can use the following code and the support of one of these libraries: readxl, openxlsx, tidyxl (with unpivotr).
filelist <- dir(folder, recursive = TRUE, full.names = TRUE, pattern = ".xlsx$|.xls$", ignore.case = TRUE) 
l_df <- lapply(filelist, readxl::read_excel)

Note that we are using dir to list the full paths (full.names = TRUE) of all the files that ends with .xlsx, .xls (pattern = ".xlsx$|.xls$"), .XLSX, .XLS (ignore.case = TRUE) in the folder folder and all its subfolders (recursive = TRUE).

readxl is integrated with tidyverse. It is pretty easy to use. It is most likely what you're looking for.

Personally, I advice to use openxlsx if you need to write (rather than read) customized Excel files with many specific features.

tidyxl is the best package I've seen to read Excel files, but it may be rather complicated to use. However, it's really careful in the types preservation. With the support of unpivotr it allows you to handle complicated Excel structures. For example, when you find multiple headers and multiple left index columns.

Upvotes: 2

Related Questions