[R] [FORGED] Re: readxl issue

Rolf Turner r@turner @end|ng |rom @uck|@nd@@c@nz
Thu Feb 6 00:48:36 CET 2020

On 6/02/20 11:01 am, Thomas Subia wrote:

> Jeff,
> You wrote: " Pay attention to whether the read_csv call is configured to expect first line as header."
> Here is the code I'm using to extract one cell from a series of Excel files having the same physical format.
> library(plyr)
> library(readxl)
> files <- list.files(pattern="*.xls", full.names = TRUE)
> # Extract part average from cell c6 for all Excel files
> avgs <- lapply(files, read_excel, sheet="Flow Data", range=("c9"))
> # Write data to text file
> write.table(avgs ,"avgs.txt",sep="\t")
> I'm not sure where read_csv applies here.

Jeff probably did not read your email as carefully as he might have, and 
made the unwarranted assumption that you'd be doing something sensible 
like using *.csv data files rather than mucking about with the 
notoriously perilous arcana of Excel.

I *don't* muck about with Excel myself, unless absolutely forced to, so 
I can't make any specific suggestions, but I *can* make a general 
suggestion.  When things go wrong, break the procedure down into simple 
atomic steps.

* initially deal with just *one* of your *.xls files
* read it in:  xxx <- read_excel(<whatever>)
* *don't* write it out to a text file yet, *look* at the <expletive 
deleted> thing in R first; print it (just type "xxx" or "head(xxx)" if 
it's large)
* perhaps convert it to a data frame (it will be a "tibble" and tibbles 
confuse the issue): yyy <- as.data.frame(xxx)
* investigate whether supplying some more arguments to read_excel() 
solves your problem
* perhaps try read.xlsx from the xlsx package and see if any insights 
are revealed.
* if you're still stumped, make available to the list one of the 
problematic *.xls files (note that you *cannot* attach such a file to 
your email; it will get stripped; you'll need to provide a URL from 
which the file can be obtained).  Given such a file someone on the list 
may be able to help you.


Rolf Turner

Honorary Research Fellow
Department of Statistics
University of Auckland
Phone: +64-9-373-7599 ext. 88276

More information about the R-help mailing list