Date conversion from Variable

[ksharpes]

Hello,  I am looking to loop through a number of files in a directory and import these in in sequence.

Each file will be named "2019-01 XXXX.XLSX", "2019-02 XXXX.XLSX" etc etc

The below works except for vMonth does not reproduce the date, which i want to show as the last day of that month

FOR rowno = 0 TO noOfRows('FILELIST')-1
let vSourceFilenamefull = peek('FileName', rowno, 'FILELIST');
let vSourceFile = peek('FileName2', rowno, 'FILELIST');
let vMonth = date(MonthEnd(DATE(left('$(vSourceFile)',7),'YYYY-MM')),'DD/MM/YYYY');

Is anyone able to assist?

[Anil_Babu_Samineni]

can you please provide at least one iteration how the values is generating of "vSourceFile" variable?

[sunny_talwar]

Try this

=Date(Floor(MonthEnd(Date#(Left('$(vSourceFile)', 7), 'YYYY-MM'))), 'DD/MM/YYYY')

[ksharpes]

Below is the full code

sub DoDir (Root)
for each Ext in 'xlsm'
for each File in filelist (Root&'\*.' &Ext)
LOAD
'$(File)' as FileName,
MID('$(File)',34,7) as FileDate
autogenerate 1;
next File
next Ext
for each Dir in dirlist (Root&'\*' )
call DoDir (Dir)
next Dir
end sub
call DoDir ('lib://Jedox_Data (xxxxxx_xxxxx)') 

RENAME TABLE [AUTOGENERATE(1)] to [FILELIST];