Basically, there are a few excel reports that are emailed every morning to us. I download them directly to a specified drive then I wrangle them in R.
The thing is that I have to manually open each file and save them before running my script in R. If not this will happen:
When I go in and manually open and save the files and re-run my script. I get the correct results:
Firstly, do you guys know why this happens? and secondly, is there a function that will allow me to open these files and save them. I did try openxlsx. However, I still have to manually press the save button.
Here is the function I created to bring in the files:
store.FUN = function(x)
{
m = as.data.frame(read_excel(file))
names(m) = c(1:length(m))
m[1, 1] = str_sub(m[2, 1], 13)
m = bind_rows((m)[1, ], subset(m, (m)[1] == "Total Income"))
m[2, 1] = m[2, 2]
m = m[-c(2)]
return(m)
}
district_1.stores = sapply(store.file, store.FUN, simplify=FALSE) %>%
bind_rows(.id = "Store ID")
Thanks!
Edit: So it looks like the cells are formulated:
But, if I do nothing and only save the file and go back to R to perform the script, the numbers pull in just fine.
Here is an example of the excel file: enter image description here
