Get files from a specific date range

Help Studio
, ,
1

Hi all! Hope you’re doing well

I’m facing an automation issue.

I have a folder with multiple files (sample file name: FollowUp_20220815040539.xls) where what comes after the underscore is the date, 20220815. For the automation I need to read the files from Monday to Friday and add them to a single file.

How can I get the files from the specific date range (Mon-Fri)?

Thank you for the help.

2

I would read the file names, extract the date sequence, parse it as a DateTime object and apply the DayOfWeek property on it. Then you can identify your files, read them etc.

3

Hey @roberto.piccolli,

You can use.
Convert.ToDateTime(DateTime.ParseExact(“20220815”, “yyyyMMdd”,System.Globalization.CultureInfo.InvariantCulture).ToString(“yyyy-MM-dd”)).DayOfWeek

Thanks,
Sanjit

4

Thank you for the reply!
This is where I’m having issue. I can get the list with the files name but not isolate the date in it.
After I have the date I’d need a condition to just ready the files from this week (process to run on Fridays only).
Many thanks

5

You can use System.Text.RegularExpressions.Regex.Match(filename, “(?<=_)\d{8}”).toString to extract only the digits for the date ( assuming the date is always written like this YYYYMMdd ) then Parse it like @Sanjit_Pal elaborated

If you only want to read the files from this ( current week ), set the start date of the week and the end date by adding / substracting days from DateTime.Now and check if the extracted date from the filename is in this interval.

6

Hey @roberto.piccolli,

Suppose in a list you have list of file path, so for each file path you can use a function Path.GetFileNameWithoutExtension(Path).Substring(LastIndexOf(“_”)+1,8).
After that you will get the date value and you can use the given expression

Thanks,
Sanjit

7

bringing the different building blocks forward to a complete scenario we can do:

Imports Panel (close to Variable Panel)
add namespaces:

  • System.Globalization
  • System.Text.RegularExpressions
  • System.IO

Assign Activity:
arrFilePaths | String ( ) - A string Array =

(From fi in New DirectoryInfo("YourFolderPath").GetFiles()
Let ds = Regex.Match(fi.Name, "(?<=_)\d{8}").Value
Let dp = DateTime.ParseExact(ds, "yyyyMMdd", CultureInfo.InvariantCulture)
Let dn = dp.DayOfWeek
Where dn >=1 And dn <= 5
Select f = fi.FullName).toArray

so, we get returned an array with all FilePaths with files related to Mon-Fri and can later use it for postprocessing

8

Thank you all for the help.

I got to a solution, perhaps less elegant than the suggested but it seems to work. I created a variable for each day of the week with AddDays() - -4 for Monday, -3 for Tuesday and so on.
Then I get files from directory (Directory.GetFiles(str_FolderPath, “" + str_Monday + "” )). From there I can read it and add to a data table which I merge afterwards.

Thank you!