Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I'm trying to get the filename of the current file im on in my Filelist loop and store that as a variable, is this possible? Filename() just returns a NULL value. I also need to extract the Filetime from the file too to store it as a variable.
Example code:
SET SalesFactFiles = $(vSourceDataFilePath)qvd\aggregated\monthly\*2021_salesfact.qvd;
FOR EACH FILE IN FILELIST('$(SalesFactFiles)')
LET Weeklyname = FileName($(File));
LET SalesfactTime = FileTime($(File));
Hi Neil
You're doing the right thing, just need to ensure correct capitalisation and wrap the dollar sign expansion in quotes:
FOR EACH FILE IN FILELIST('$(SalesFactFiles)')
LET Weeklyname = FileName('$(FILE)');
LET SalesfactTime = FileTime('$(FILE)');
Pi
@neil-devlin FILE will just be the name of the file - so no need to wrap it in a formula:
LET Weeklyname = '$(FILE)';
This will return it with the full path, so yes, start from here and use a string function to parse it (hint: use"subfield" with a '/' delimiter 😉 )
Pi
Hi Neil
You're doing the right thing, just need to ensure correct capitalisation and wrap the dollar sign expansion in quotes:
FOR EACH FILE IN FILELIST('$(SalesFactFiles)')
LET Weeklyname = FileName('$(FILE)');
LET SalesfactTime = FileTime('$(FILE)');
Pi
Just to elaborate - you were almost there with FileName() - except this is used within a LOAD statement to get the name of the currently loaded table.
I use it when I am doing a pull directly from a wildcarded filname:
LOAD
A,
B,
FileName() AS NameOfFile
FROM [../../Data/QVD/MyFile_2020*.QVD] (QVD);
Pi
You can't use Filename() function outside the load statement, first load it in to a field and then you could use it to assign to variable
Thanks for this Pi, this worked brilliant for FileTime but still returns NULL for FileName unfortunately, assume this can only be used in a LOAD statement as you've said? Is there any function to get the name of the current file in this instance outside of using something like 'Right('$(FILE)', count)' ?
Neil
@neil-devlin FILE will just be the name of the file - so no need to wrap it in a formula:
LET Weeklyname = '$(FILE)';
This will return it with the full path, so yes, start from here and use a string function to parse it (hint: use"subfield" with a '/' delimiter 😉 )
Pi
Thanks for the help Pi! Have my script doing what I need it to now 😁. The subfield hint was very much appreciated!
Neil
Great to hear!