Load * from excel files only when the excel file contains today's date

[OmarBenSalem]

Hi all,

I'm trying to load every excel file in a folder where the excel file contains today's date-1

my excels are as follow:

what I've done is the follow:

table :

let D1=  date(Today()-1,'DDMMYYYY');

FOR Each Ext in 'xlsx'

  FOR Each File in filelist ('lib://Test/'&'\*.'&Ext)

Concatenate

load  *  Where left(File,8) ='$(D1)'  ;

next File

What am I missing please?

[Silambarasan1306]

This works perfect for me.

For Each vPath in FileList('lib://Test/*.xlsx')

Data:
LOAD *,
  Date(Date#(SubField(SubField('$(vPath)','/',4),'_',1),'DDMMYYYY')) as Date
FROM [$(vPath)]
(ooxml, embedded labels, table is Sheet1);

Next vPath

Please find the attached application.

[OmarBenSalem]

imagine that in all of ur files; all the columns contains EXACTLY the same information.

Can you alter your source, so that the files contains the SAME values ; 1 row per column to make it simple; and see what happens

[OmarBenSalem · Accepted solution]

I was trying to load the files relative to D-1, D-2, M-1 and Y-1 (depends on today's date) and have the last version of each file :

this worked (if someone would be interested):

For Each vPath in FileList('lib://Test/*.xlsx')

let D1= Date#(Today()-1, 'DDMMYYYY');

let D2= Date#(Today()-2, 'DDMMYYYY');

let M1=num( FirstWorkDate(AddMonths(Today()-1, -1), 1));

let Y1=num(FirstWorkDate(AddYears(Today()-1, -1), 1));

Data:

LOAD *,

Date(Date#(SubField(SubField('$(vPath)','/',4),'_',1),'DDMMYYYY')) as Date,

     Left(SubField(SubField('$(vPath)','/',4),'_',2),2) as Flag

FROM [$(vPath)]

(ooxml, embedded labels, table is Sheet1);

Next vPath

//D1

NoConcatenate

fD1:

load * where numDate = $(D1);

Load

col1,

Date,

num(Date) as numDate,

(Flag)  Resident Data ;

  versionD1:

Load num(max(Flag),'00') as maxFlagD1 Resident fD1;

let vMaxD1= Peek('maxFlagD1',-1,'versionD1');

NoConcatenate

final:

Load * Resident fD1 Where Flag= $(vMaxD1);

drop table fD1;

//D2

NoConcatenate

fD2:

load * where numDate = $(D2);

Load

col1,

Date,

num(Date) as numDate,

(Flag)  Resident Data ;

versionD2:

Load num(max(Flag),'00') as maxFlagD2 Resident fD2;

let vMaxD2= Peek('maxFlagD2',-1,'versionD2');

Concatenate(final)

  Load * Resident fD2 Where Flag= $(vMaxD2);

  drop table fD2;

//M1

NoConcatenate

fM1:

load * where numDate = $(M1);

Load

col1,

Date,

num(Date) as numDate,

(Flag)  Resident Data ;

versionM1:

Load num(max(Flag),'00') as maxFlagM1 Resident fM1;

let vMaxM1= Peek('maxFlagM1',-1,'versionM1');

Concatenate(final)

Load * Resident fM1 Where Flag= $(vMaxM1);

drop table fM1;

//Y1

NoConcatenate

fY1:

load * where numDate = $(Y1);

Load

col1,

Date,

num(Date) as numDate,

(Flag)  Resident Data ;

versionY1:

Load num(max(Flag),'00') as maxFlagY1 Resident fY1;

let vMaxY1= Peek('maxFlagY1',-1,'versionY1');

Concatenate(final)

  Load * Resident fY1 Where Flag= $(vMaxY1);

  drop table fY1;

   Drop Tables Data,versionD1,versionD2,versionM1,versionY1;

[OmarBenSalem]

Hi all (again)  stalwar1‌ , jaganhic

What if I want to accomplish the same thing I've done when all of the files are seperated in subfolders.

I mean from :

to:

Thanks !