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