Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
I need your help in the below scenario:
I have subfolders in QVData folder as below and in the same order
BD-4
BD-3
BD-2
BD-1
BD1
BD2
BD3
BD4
At first I will have BD-4 folder only available, but after interval of time, another folder named BD-3 will be created and so on.
I need to old .XLS files from only the Latest folder available as per the order of folders above.
Thanks in advance,
Singh
Hi,
Check this ,
This code will load three year excel files based on file name ,You can modify your code based on your requirement,
LET vFilePath = 'Your Path';
FOR EACH file in FileList('$(vFilePath)\File*.xlsx');
LET vFileYear = Left(Right(file, 9), 4);
LET vMaxYear1 = (Rangemax(vFileYear, vMaxYear1))-1;
LET vMaxYear2 = (Rangemax(vFileYear, vMaxYear2))-2;
LET vMaxYear = (Rangemax(vFileYear, vMaxYear));
Next
Data:
LOAD *
FROM
[$(vFilePath)\Eng-$(vMaxYear1).xlsx]
(ooxml, embedded labels, table is Data);
LOAD *
FROM
[$(vFilePath)\Eng-$(vMaxYear2).xlsx]
(ooxml, embedded labels, table is Data);
LOAD *
FROM
[$(vFilePath)\Eng-$(vMaxYear).xlsx]
(ooxml, embedded labels, table is Data);
HTH,
Hirish
LastDirectory:
LOAD
MaxString(DirectoryName) as LastDirectory
Resident Directories
;
LET vLastDirectory = Peek('LastDirectory',-1,'LastDirectory');
DROP Table LastDirectory;