Pick input file by month through Dynamic Input tool

[kauser]

Hello,

i have a folder that listed all input excel file(.xlsx) and out of that list there is a file name by first three letters of month such as - Jan, Feb, Mar, Apr.... . in every month business will replace that folder with their current month and because of that replacing file name for that month will change. for example - in January there will be file name as Jan and in next month when they will replacing all files that file name will change to Feb and Mar, Apr....

how can i use Dynamic Input tool to pick that specific file with month name (Jan for January, Feb for February.......) 

[shancmiralles]

if i get what you mean you have  a folder with xlsx files for each months and filenames are abbreviation of the months ( january = jan , february = feb, etc) ... 
if you are just want to pick the file for the current month (like now august so its aug.xlsx) as your only current ouput  , i'd assume your input tool is the directory input tool.
i'd pull in all files with xlsx ( so that's 12 files for each months)... 

so when you run the directory tool , it should show a column for Filename.  

i would parse that
( Column to split : FileName
Delimiters: .xlsx)

you should have FileName1 and FileName2 at the end of the fields once you run the WF

Next I'll add a formula tool creating a new (output column) named Month.

my formula would be (please forgive me im lazy in writing formula lol.. i didn't complete all the months below)
IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF
(IF [FileName1]="Mar" THEN "March" ELSEIF
[FileName1]="Aug" THEN "August"
ELSE [FileName2] ENDIF)

next i'll be pulling a "date time now" tool  on the canvas (not connecting it ) and parse it .. so it should have 2 columns:
1: August (curent month)
2: 2024 (current year)

FINALLY join tool. 
i'll be joining my formula tool on the left input anchor and the (date time now) parse tool on the left anchor... 
joining it by specific filed for the left it would be my "Month" field and " right would be my "1" field.

output will always be the xlsx for the current month (so for this month it should be Aug.xlsx

hope i got your question right.. 

and hope this helps! 
@kauser 

[kauser]

@shancmiralles 

Thanks for your reply. actually business will replace all their input files with new files mean in month of January there will be one only Jan file and in February only Feb file(this will replace with Jan file). also please note there are few others .xlsx files along with this month file. i hope i make it clear to you   

[shancmiralles]

Yeah.. if you follow the instructions I gave it should work . Doesn't matter if there are other excel files.. since by the end of the WF the join tool will only get the file for the current month.. 

Regardless if the business will replace existing monthly files if they use the same filename format then should still be good.

The instructions I gave you doesn't need modification and all you have to do is run it every month ( or schedule it in a server to auto run it for you) .

@kauser 

[Yoshiro_Fujimori]

@kauser ,

Here is one way to do this;

1. Get Full Path with Directory Tool
2. Filter the output by the month (Jan, Feb, Mar, ...) of today's date with the expression;
   StartsWith([FileName], DateTimeFormat(DateTimeToday(),"%b"))
3. Pass the Full Path to Dynamic Input

I hope this helps.

[kauser]

@Yoshiro_Fujimori 

Thanks for your reply. its working nice but issue is it picked only current month file but input files for this workflow will be from previous or month before. for example in august all input files will be from June/July and that file name will be Jun/Jul.xlsx. Also, in that input folder there will be only one file that will name with first three letters from month and rest of files will be have individual name. 

[Yoshiro_Fujimori]

Not sure I fully understand your situation, but you may try to tweak the expression in Filter tool to meet the naming rule of your input file.

e.g.

[FileName] =
DateTimeFormat(DateTimeAdd(DateTimeToday(),-2,"month"),"%b") + "_" +
DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"month"),"%b") + ".xlsx"

Good luck.