Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to load the latest excel file into my script from a specific folder.
The filename is "DAC_GROUP_2020-03-07T1100"
I am not able to finish the script for it to load the latest file.
This is how the script looks like right now:
Table_Temp:
Load FileName() as File_Name,
(right(FileName(),20)) as Test
// Date(Date#(Right(FileName(),8),'YYYYMMDD'),'DD/MM/YYYY') as DateField
FROM [lib://Dropbox (dm1_qsuser)/AllClients\Simplifi\DAC_Group_*.xlsx]
(ooxml, embedded labels, table is sheet1);
Table_Temp2:
Load
Date(Date#(Left(Test,10),'YYYY-MM-DD'),'DD/MM/YYY') as Date
Resident Table_Temp;
drop Table Table_Temp;
Min_Max:
Load Max(Date) as MaxDate,
Min(Date) as MinDate
Resident Table_Temp2;
LET vMaxDate = Peek('MaxDate',0,'Min_Max');
Final:
Load * FROM [lib://Dropbox (dm1_qsuser)/AllClients\Simplifi\DAC_Group_$(vMaxDate).xlsx]
(ooxml, embedded labels, table is sheet1);
Can someone please help?
If you look at the error, you see that it's looking for a file named
DAC_GROUP_43905...
Whereas your file is named
DAC_GROUP_2020-03-07T1100
You have to format that date from 43905 to the one that matches your filenames.
Is it giving you an error? What's it doing that it's not supposed to do?
it is giving me the following error.
It is suppose to load the latest file in the folder.
If you look at the error, you see that it's looking for a file named
DAC_GROUP_43905...
Whereas your file is named
DAC_GROUP_2020-03-07T1100
You have to format that date from 43905 to the one that matches your filenames.
You may also want to consider this post here.