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
– Edo, Commented Dec 4, 2020 at 9: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
– Edo, Commented Dec 4, 2020 at 9: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
– Tom, Commented Dec 4, 2020 at 9:13
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
– Edo, Commented Dec 4, 2020 at 9:26

Edo · Accepted Answer · 2020-12-04 09:44:20Z

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.

Collectives™ on Stack Overflow

Parsing issue, unexpected character when loading a folder

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related