CODEWITHSUNDEEP
rdatexlsx
Nicodemus Sigit Sutanto
Nicodemus Sigit Sutanto

Reputation: 151

how to extract date in file name and sort it to find the latest file?

I currently have several files in a folder. It contains everyday updates on stock. It's looked like this.

Onhand Harian 12 Juli 2019.xlsx
Onhand Harian 13 Juli 2019.xlsx
Onhand Harian 14 Juli 2019.xlsx... and so on.

I would like to read ONLY the latest excel file by using the date on the file name. How to done this? thanx in advance

Upvotes: 2

Views: 478

Answers (2)

r.user.05apr
r.user.05apr

Reputation: 5456

I would do something like:

library(stringr)
library(tidyverse)

x <- c("Onhand Harian 12 Juli 2019.xlsx",
       "Onhand Harian 13 Juli 2019.xlsx",
       "Onhand Harian 14 Juli 2019.xlsx")

lookup <- set_names(seq_len(12),
                    c("Januar", "Februar", "März", "April", "Mai", "Juni", "Juli",
                      "August", "September", "Oktober", "November", "Dezember"))

enframe(x, name = NULL, value = "txt") %>%
  mutate(txt_extract = str_extract(txt, "\\d{1,2} \\D{3,9} \\d{4}")) %>% # September is longest ..
  separate(txt_extract, c("d", "m", "y"), remove = FALSE) %>%
  mutate(m = sprintf("%02d", lookup[m]),
         d = sprintf("%02d", as.integer(d))) %>%
  mutate(date = as.Date(str_c(y, m, d), format = "%Y%m%d")) %>%
  filter(date == max(date)) %>%
  pull(txt) 
#  "Onhand Harian 14 Juli 2019.xlsx"

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 389135

If all of your files contain the same name, you can do

#List all the file names in the folder
file_names <- list.files("/path/to/folder/", full.names = TRUE)

#Remove all unwanted characters and keep only the date
#Convert the date string to actual Date object
#Sort them and take the latest file
file_to_read <- file_names[order(as.Date(sub("Onhand Harian ", "", 
       sub(".xlsx$", "", basename(file_names))), "%d %B %Y"), decreasing = TRUE)[1]]

Apparently, if your files are generated everyday you can also select them based on their creation or modification time using file.info ? Details in the post.

Upvotes: 0

Related Questions