[R] readxl question

[PIKAL Petr]

Hi


Are you sure that your command read values from respective cells?

I tried it and got empty data frame with names 
> WO <- lapply(files, read_excel, sheet=1, range=("B3")) 
> as.data.frame(WO)
[1] ano                 TP303               X96                
[4] X0                  X3.7519999999999998 X26.7              
<0 rows> (or 0-length row.names)

To get data, col_names argument should be set to FALSE
WO <- lapply(files, read_excel, sheet=1, range=("B3"), col_names=FALSE)
WO2 <- lapply(files, read_excel, sheet=1, range=("B5"), col_names=FALSE)

After that unlist and one rbind together with t should be enough to give you
one table
WO <- unlist(WO)
WO2 <- unlist(WO2)
result <- t(rbind(WO, WO2))
result
     WO      WO2       
...1 "ano"   "ano"     
...1 "TP303" "261119/2"
...1 "96"    "288"     
...1 "0"     "192"     
...1 "3.752" "25.92094"
...1 "26.7"  "38.6"    
>

And instead txt document you could do

write.table(result, "result.xls", sep = "\t", row.names = F)

And now "result.xls" is directly readable with Excel

Cheers
Petr

>
> -----Original Message-----
> From: R-help <r-help-bounces using r-project.org> On Behalf Of Thomas Subia via
> R-help
> Sent: Saturday, August 22, 2020 6:25 AM
> To: r-help using r-project.org
> Subject: [R] readxl question
> 
> Colleagues,
> 
> 
> 
> I have 250 Excel files in a directory. Each of those files has the same
layout.
> The problem is that the data in each Excel data is not in rectangular
form. I've
> been using readxl to extract the data which I need.
> Each of my metrics are stored in a particular cell. For each metric, I
create text
> files which stores my metrics.
> 
> 
> 
> library(plyr)
> 
> library(readxl)
> 
> 
> 
> files <- list.files(pattern="*.xls", full.names = FALSE)
> 
> 
> 
> # Extract Work Order
> 
> WO <- lapply(files, read_excel, sheet="Sheet1", range=("B9")) WO_list <-
> as.data.frame(WO) trans_WO <- t(WO_list) write.table(trans_WO ,"WO.txt")
> 
> 
> 
> # Extract bubble 14_1
> 
> BUBBLE_14_1 <- lapply(files, read_excel, sheet="Sheet1", range=("c46"))
> BUBBLE_14_1_list <- as.data.frame(BUBBLE_14_1)
> 
> trans_BUBBLE_14_1 <- t(BUBBLE_14_1_list)
> 
> 
> 
> write.table(trans_BUBBLE_14_1,"BUBBLE_14_1.txt")
> 
> 
> 
> 
> 
> # Extract bubble 14_2
> 
> BUBBLE_14_2 <- lapply(files, read_excel, sheet="Sheet1", range=("c62"))
> BUBBLE_14_2_list <- as.data.frame(BUBBLE_14_2)
> 
> trans_BUBBLE_14_2 <- t(BUBBLE_14_2_list)
> 
> write.table(trans_BUBBLE_14_2,"BUBBLE_14_2.txt")
> 
> 
> 
> After the text files have been created, I cut and paste the contents of
each
> text file to Excel.
> 
> This has worked fine if the number of cells I am extracting from a file is
small.
> 
> If the number gets larger, this method is inefficient.
> 
> 
> 
> Any advice on how to do this would be appreciated.
> 
> 
> 
> All the best,
> 
> 
> 
> Thomas Subia
> 
> 
> 	[[alternative HTML version deleted]]
> 
> ______________________________________________
> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-
> guide.html
> and provide commented, minimal, self-contained, reproducible code.