readxl question

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

readxl question

R help mailing list-2
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 each column of data
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]]

______________________________________________
[hidden email] 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.