Load latest files from directory

[Anonymous]

Good day!

During working in script with loading xlsx files, i've met problem with reading only latest files.

In script i have such block, which read files which have updating date=today()

Directory;

FOR Each File in filelist ('$(DataPath)Test_*.xlsx')

  Main_Temp:

FROM

  '$(File)'

(ooxml, embedded labels, table is Sheet1)

Where date(FileTime( '$(File)' ))=Today();

NEXT File;

But this code read all files and get values from file which have updating date=today() and that's why time for this load is very long.

Is there any way to modify this code to read only files with updating date=today()  ? (In my directory i have only one such file with one record and i think, time for it load must be very short)

Thanks.

[marcus_sommer]

Try:

FOR Each File in filelist ('$(DataPath)Test_*.xlsx')

if date(FileTime( '$(File)' ))=date(Today()) then

      Main_Temp:

     FROM

       '$(File)'

      (ooxml, embedded labels, table is Sheet1)

end if

NEXT File;

- Marcus

[HirisH_V7]

Hi

Check this,

Directory;


FOR Each File in filelist ('Test_data*.xlsx')


Main_Temp:
  load
  '$(File)' as Name,
FileTime( '$(File)' ) as FileTime,
  Week,
  Sales
FROM
  '$(File)'
(ooxml, embedded labels, table is Sheet1);

NEXT File;


Table:
first 1
Load
Name,
FileTime,
  Week,
  Sales,
  1 as dummy

  Resident Main_Temp
  Order By FileTime asc;
drop table Main_Temp;


NoConcatenate
Excel:
load
  '$(File)' as Name,
FileTime( '$(File)' ) as FileTime,
  Week,
  Sales
FROM
  '$(File)'
  (ooxml, embedded labels, table is Sheet1);

Picking the latest file based on the Filetime().

HTH,

PFA,

Hirish

[Anonymous]

doesn't works(

this code doesn't read any file..

[marcus_sommer · Accepted solution]

Filetime is a timestamp and needed to be floored like and NEXT need no counter and ;

FOR Each File in filelist ('$(DataPath)Test_*.xlsx')

if date(floor(FileTime( '$(File)' )))=date(Today()) then

      Main_Temp:

     FROM

       '$(File)'

      (ooxml, embedded labels, table is Sheet1)

end if

NEXT File;

- Marcus

[Anonymous]

Works)))

Thanks, dear!!!