Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
maybe this:
table :
let D1= date(Today()-1,'DDMMYYYY');
FOR Each File in filelist ('lib://Test/'&'\*.'&Ext)
Concatenate
load * from File Where left(File,8) ='$(D1)' ;
next File
Better you can try like this,
For Each vPath in FileList('lib://Test/*.xlsx')
Table:
LOAD
*,
Date(Date#(SubField(SubField('$(vPath)','/',4),'_',1),'DDMMYYYY')) as Date
FROM [$(vPath)]
(ooxml, embedded labels, table is Sheet1);
Next vPath
For Each vPath in FileList('lib://Test/*.xlsx')
let D1= date(Today()-1,'DDMMYYYY');
Table:
if(wildmatch(right(filename(),8),'$(D1)') then
LOAD
*
FROM [$(vPath)]
(ooxml, embedded labels, table is Sheet1);
endif
Next vPath
This Brought to me only the 13/12/2017 files (why?)
while I want to have the FULL control on the files I want to import:
1) the 14/12/2017 files (today-1) and then bring only the last version 02)
and so on
That unfortunately brought nothing:
hey omar
you didn't try with wildmatch(). Can you try and let me know.
I altered it because of of a syntax error :