Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using the script below for incremental loading of files copied out to a shared drive and it works great.
I want to change this code to only load the latest file for another application I am working on. Any suggestions how I can determine which file is the most recent? I did not write this code, I got this from the community some time back.
//Creates current table of excel files in the directory for incremental loading to compare to existing
TRACE DoDir;
SUB DoDir (Root)
FOR Each File in FileList(Root&'$(vSourceFile)')
Files:
LOAD '$(File)' as File_Name
AutoGenerate 1;
NEXT File
ENDSUB
CALL DoDir (vRoot)
Trace Loop;
FOR i=0 to NoOfRows('Files')-1
LET vFileName = Peek('File_Name', 0, 'Files');
IF Date(Date#(SubField(vFileName, '_', 4), 'DD-MMM-YYYY')) >= vDateFrom and Date(Date#(SubField(vFileName, '_', 5), 'DD-MMM-YYYY')) <= vDateTo then
AppExtracts:
LOAD *
From [$(vRoot)\$(vFileName)] ;
ENDIF
NEXT i;
you can use the filetime function to determine the latest one :
Returns a timestamp for the date and time of the last modification of the file filename. If no filename is specified, the function will refer to the currently read table file.
Examples:
filetime( 'xyz.xls' )
Will return the timestamp of the last modification of the file xyz.xls.
Load *, filetime() as X from abc.txt ;
Will return the date and time of the last modification of the file (abc.txt) as a timestamp in field X in each record read.
You could add filetime() and and use a second load with a sorting-order and use the first or last row or a max-statement about the filetime:
....
Files:
LOAD '$(File)' as File_Name, filetime('$(File)') as FileTime
AutoGenerate 1;
....
MaxTime:
Load File_Name, max(FileTime) as MaxFileTime Resident Files Group By File_Name;
.....
- Marcus
thank you! I will try that and use it to sort and use peek function to get last file, will that work?
yes, or top 1 if you order the results correctly
Thank you. this helped but I only want to load the one record with the latest date not all the files. I can order desc by file date then pull in only the first record I believe
load top 1 filename
....
order by filetime desc
Thank you all for suggestions. In the end, I convinced them to just save the latest file so we don’t have to worry about this script. But I was able to successfully create the logic to load the last file.
can you attach script for the same.
vikas
Maybe this is helpful: Re: Pick the latest excel file from folders
- Marcus