Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a Server folder with Excels in the folder being added every month.
File Names:
January19
February19
March19
.
.
December19
January20
February20
I need to load only the latest Excel which is February20 in this case. I tried all the solutions available on the threads but nothing seems to be working with my filename format. Please advise.
I used an inline load as an example.
With FileBaseName():
tmpFileNames:
FIRST 1 LOAD
// SubField because I only want the month and year of the file (Apr19 Dec19)
SubField(FileBaseName(),'_',3) as MonthYear
// change with your path
FROM [E:/downloads/HRC_C_*.xlsx]
(ooxml, embedded labels, table is Foglio1);
FileNames:
load *, Date(Date#(Left(MonthYear, Len(MonthYear)-2) &'-'& Right(MonthYear,2) & '-01', 'MMMM-YY-DD')) as MY
Resident tmpFileNames;
Drop Table tmpFileNames;
LastFileName:
First 1 load MonthYear as ExcelToLoad
Resident FileNames
order by MY desc; // order by works if MY is a date!
LET vExcelToLoad = Peek('ExcelToLoad');
// add the MY field
FileNames:
load *, Date(Date#(Left(MonthYear, Len(MonthYear)-2) &'-'& Right(MonthYear,2) & '-01', 'MMM-YY-DD')) as MY;
load * inline [
MonthYear
January19
February19
March19
December19
January20
February20
];
// resident load 1 record order by date desc (latest excel file)
LastFileName:
First 1 load MonthYear as ExcelToLoad
Resident FileNames
order by MY desc;
// set the variable with the name of the excel file to load
LET vExcelToLoad = Peek('ExcelToLoad');
// .... load the file
Hi,
Looks like this requires me to create Inline table with all the file names. It becomes difficult when I have 3-4 years worth of excels and pick up the latest. It should be dynamic, something using FileList perhaps? How do we do it
Used Filebase()
FileNames:
load *, Date(Date#(Left(MonthYear, Len(MonthYear)-2) &'-'& Right(MonthYear,2) & '-01', 'MMM-YY-DD')) as MY;
Load filebasename() as MonthYear
FROM [E:/downloads/*xlsx;
// resident load 1 record order by date desc (latest excel file)
LastFileName:
First 1 load MonthYear as ExcelToLoad
Resident FileNames
order by MY desc;
// set the variable with the name of the excel file to load
LET vExcelToLoad = Peek('ExcelToLoad');
// .... load the file
But loads Apr19 although I have Dec19 as the latest
I used an inline load as an example.
With FileBaseName():
tmpFileNames:
FIRST 1 LOAD
// SubField because I only want the month and year of the file (Apr19 Dec19)
SubField(FileBaseName(),'_',3) as MonthYear
// change with your path
FROM [E:/downloads/HRC_C_*.xlsx]
(ooxml, embedded labels, table is Foglio1);
FileNames:
load *, Date(Date#(Left(MonthYear, Len(MonthYear)-2) &'-'& Right(MonthYear,2) & '-01', 'MMMM-YY-DD')) as MY
Resident tmpFileNames;
Drop Table tmpFileNames;
LastFileName:
First 1 load MonthYear as ExcelToLoad
Resident FileNames
order by MY desc; // order by works if MY is a date!
LET vExcelToLoad = Peek('ExcelToLoad');
Perfect. Thank you so much