How do I automatically take the most recently modified .txt file in a folder, convert it to .xlsx, and overwrite an existing .xlsx file in RStudio?

[vntgevlhks]

I am attempting to use RStudio to automate data freshness in an Excel document, but am new to coding and am having trouble.

Here's the scenario:

Each morning, I get a .txt file with a unique name (same information) deposited into the same folder. It does not overwrite the previous day's .txt file, it simply creates a new one.

I am hoping to use RStudio to automatically read the most recent .txt file from the folder, convert it to .xlsx, and have it update/overwrite an existing .xlsx file. This .xlsx file will serve as the basis for a dashboard, so it's important that the same .xlsx file is getting the new information from each day's .txt file.

Again, I'm very new to coding, so I don't have the most educated guess as to how this would be coded, but below is my best starting attempt:

require(readtext)
require(dplyr)
require(eeptools)

#Set Working Directory

setwd("G:\\My Drive\\Dashboards\\Text Files")

#Read latest text file

data_files <- file.info(Sys.glob("*.txt"))
row.names(data_files)[which.max(data_files[["ctime"]])]

Does anybody know exactly how I would have to code this?

Thank you in advance for your help. I greatly appreciate it!

[technocrat]

The only difficult part is identifying the latest file of the given type. Note that the reprex working directory is different; when you cut and paste this you will get the files in your current directory.

# current directory

the_files <- dir(pattern = ".R$")
mod_times <- file.info(the_files)$mtime 

DF <- data.frame(the_files = the_files, mod_times = mod_times)
DF
#>                   the_files           mod_times
#> 1      drear-kitty_reprex.R 2021-12-12 17:37:22
#> 2 drear-kitty_reprex.spin.R 2021-12-12 17:37:22
target <- DF[which.max(DF$mod_times),1]
target
#> [1] "drear-kitty_reprex.spin.R"