Select latest valid file (Iterative Macro)

[Shubh07kr]

Hi All,

I have to select the latest file. In the file the headers are in the 5th row but some time there are no headers at all. So in that case we need to select the 2nd latest file and check if headers are in it or not and keep repeating until I get a valid file. Once I get the valid file will process further and no need to check further.

So I am sorting the files in such way that latest file is at the top and using an iterative macro to check each file one by one. and the put a filter based on the condition to check but by this I am getting only 1 row in my output . I need to put in a new column with a flag say true or 1 in each row of the dataset when we get a valid file so that all dataset is picked once we get a valid file. I tried but did not had any luck.

Could anyone please suggest what we could do here?

I there is another solution design available, that would also be great.

[caltang]

Well if you provide some sample data, it will be very helpful to illustrate your point. 

[aatalai]

you can use the directory tool then sort on one of the date time fileds and then use that to open the specfic file

[apathetichell]

@Shubh07kr --- agreeing with @aatalai  here -- I probably wouldn't use an iterative macro here. I would:

1) select the nth most recent files (say 5)

2) send them into a batch macro which retrieves the first row (including headers) of each file and the file name.

3) send out to my main workflow.

4) --- branch out from 1) to send to a different batch macro.

5) pull out all data from most recent file.

6) parse headers from first macro to identify the headers.

7) add headers via dynamic rename to data source from 5.

[Shubh07kr · Accepted solution]

Hi @apathetichell, @aatalai, Thanks for your solution. Although I got my requirements fulfilled without macro.

I am reading the 1st row of last 5 latest files also taking the file name  and using regex to check the first column name. For all  the rows matched, I am sorting  sorting it and then pick the first row and get the file name. Then use dynamic input to read the data.

[apathetichell]

@Shubh07kr  hey --- so word of advice --> do this as a batch macro now vs later. Sooner or later you will have a phantom field (field in one file, but not in the others, or a different type in one file than the others) which will lead to a schema conflict. This will crash dynamic input and your workflow. If you use a batch macro you've worked around this issue. 

[Yogesh1795]

it would help if you attach some pictures. I would use directory and dynamic Input tool, the dynamic input tool ask for reference, if your input file from directory match the reference it get the records of that file only. 

[Shubh07kr]

@apathetichell In my case schema won't change user is ok with the current solution. Although thankyou for your advice, will keep in mind this next time if have similar kind of request. 😊