How to select certain date from filename using sub scanfolder function?
Hi,
At the moment, I am using sub scanfolder to get all files from different folder(its a yearly folder..inside this yearly folder got file based on month).
Now i got new request to only select few files based on certain month-year file.
I am stuck how to squeeze in date selection inside the loop based on the filename.
Request help from you guys.
below is sample of my script;
SUB Scanfolder(Root)
For each FileExtension in 'xlsx'
FOR Each FoundFile in FileList(Root &'\*.'& FileExtension)
T1:
LOAD ID,
NAMES,
SAL,
YEARS
FROM
[$(FoundFile)]
(ooxml, embedded labels, table is Sheet1);
Next FoundFile
NEXT FileExtension
For Each SubDirectory in DirList (Root & '\*' )
Call ScanFolder(SubDirectory)
Next SubDirectory
End Sub
Call ScanFolder('D:\Personal\LOADING DATA FROM MULTIPLE FOLDERS') ;
How is certain month year selected? Is it current month year? You could try something with the FIleName function or just use a variable inside your filename that will only pull for the certain months/years you need. Ex: FROM [lib://NS/ExcelFile*202407*.xlsx] or FROM [lib://NS/ExcelFile*$(vSelectedDate)*.xlsx]
It's quite simple by querying the file-name per if-loop within the filelist-loop. At first the relevant date-part is extracted and converted to a real date and this is then queried with >= and <= of your wanted periods, for example with something:
if date(date#(subfield(subfield('$(FoundFile)', '\', -1), '.', 1), 'DD MMMM YYYY')) >= MyPeriod then load ... end if
