Parsing issue, unexpected character when loading a folder

Question

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)

hey @Tom, try to use this: `dir(folder, recursive = TRUE, full.names = TRUE) `, it will remove the need of the following `paste` — Edo, Dec 04 '20 at 09:09
also... your first error is caused by `source("C:/Users/Username/filename.xlsx")`, shouldn't you use `source` just to run R script files? Try with this: try this: `dir(folder, recursive=TRUE, full.names = TRUE, pattern = ".R$")` — Edo, Dec 04 '20 at 09:11
@Edo The first error changes when doing that: `Error in file(filename, "r", encoding = encoding) : cannot open the connection` Second error stays the same. Could there be anything wrong with the naming of the file or the filepath (undesirable characters?) EDIT: for both of your comments — Tom, Dec 04 '20 at 09:13
But wait, Edo, are you saying that I cannot load Excel files this way? Because the folder only has Excel files.. That is I guess also why your second comment returns an empty list. — Tom, Dec 04 '20 at 09:19
Exactly. `source` is to run external R code. If you need to load Excel files, try with one of this libraries: `readxl` (probably what you are looking for), `openxlsx`, `tidyxl` (this one is the most complicated but the best if your excel files are rather messy) — Edo, Dec 04 '20 at 09:26

Edo · Accepted Answer · 2020-12-04T09:44:20.360

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.

Parsing issue, unexpected character when loading a folder

1 Answers1