How to get current filename in filelist loop

[neil-devlin]

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));

[PiEye · Accepted solution]

 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

[PiEye]

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

[avinashelite]

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 

[neil-devlin]

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

[PiEye · Accepted solution]

@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

[neil-devlin]

Thanks for the help Pi! Have my script doing what I need it to now 😁. The subfield hint was very much appreciated!

Neil

[PiEye]

Great to hear!