Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
can we pick latest file available in the path.
for EX:
C:\Doc\Text_release20_sep.xlx ------- created time 15th sep
C:\Doc\Text_release26_sep.xlx ------- created time 21st sep
C:\Doc\Text_release29_sep.xlx ------- created time 27th sep
so from above file our qvw should pick last one(created time 27th sep). I have tried with couple of loops but still not getting it. can some one to get loop or any other approach.
-Priya
Hi Priya,
Try like this
let vFilePath = 'C:\Doc';
searchStr = '\*.xlsx';
rootPath = '$(vFilePath)';
sub DoDir (Root)
for each File in filelist (Root&searchStr)
TmpFileLog:
LOAD
*,
Date#(Mid(FileName, 13, 6), 'DD_MMM') AS FileDate;
Load '$(File)' as PathName,
'$(Root)' as Folder,
mid('$(File)',index('$(File)','\',-1)+1,len('$(File)')-index('$(File)','\',-1)) as FileName,
right('$(File)',len('$(File)')-index('$(File)','.',-1)+1) as FileExt,
Filesize('$(File)') as Size,
Filetime('$(File)') as FileTime
autogenerate 1;
next File
end sub
call DoDir (rootPath)
MaxDate_Temp:
LOAD
Max(FileDate) AS MaxDate
RESIDENT TmpFileLog;
DROP TABLE TmpFileLog;
LET vMaxDate = Date(Peek('MaxDate'), 'DD_MMM');
DROP TABLE MaxDate_Temp;
Data:
LOAD *,
FileName() AS FileName
FROM
[$(vFilePath)\Text_release$(vMaxDate).xlsx]
(ooxml, no labels, table is Sheet1);
Regards,
jagan.
Hi Priya,
Try like this
let vFilePath = 'C:\Doc';
searchStr = '\*.xlsx';
rootPath = '$(vFilePath)';
sub DoDir (Root)
for each File in filelist (Root&searchStr)
TmpFileLog:
LOAD
*,
Date#(Mid(FileName, 13, 6), 'DD_MMM') AS FileDate;
Load '$(File)' as PathName,
'$(Root)' as Folder,
mid('$(File)',index('$(File)','\',-1)+1,len('$(File)')-index('$(File)','\',-1)) as FileName,
right('$(File)',len('$(File)')-index('$(File)','.',-1)+1) as FileExt,
Filesize('$(File)') as Size,
Filetime('$(File)') as FileTime
autogenerate 1;
next File
end sub
call DoDir (rootPath)
MaxDate_Temp:
LOAD
Max(FileDate) AS MaxDate
RESIDENT TmpFileLog;
DROP TABLE TmpFileLog;
LET vMaxDate = Date(Peek('MaxDate'), 'DD_MMM');
DROP TABLE MaxDate_Temp;
Data:
LOAD *,
FileName() AS FileName
FROM
[$(vFilePath)\Text_release$(vMaxDate).xlsx]
(ooxml, no labels, table is Sheet1);
Regards,
jagan.
Let vMax = 0;
For Each vFile in FileList('C:\Doc\Text_release*.xlx')
Let vCurr = FileTime(vFile);
If vCurr > vMax Then
Let vMax = vCurr;
Let vLatest = vFile;
End If
Next
Data:
LOAD *,
FileBaseName() as Source
FROM [$(vLatest)]
(biff, ...)
;
Adjust the load statement to your requirements.