Batch macro input - exclude all rows after a row with specific text

Question

Hi, I have a batch macro that is pulling in multiple files. Each file has a row at the end of the data, saying "DO NOT DELETE" in column A. There are various teams submitting these files, and they often put additional workings after this row, in various columns. Ideally, I'd like the batch macro to bring in only the rows above this "DO NOT DELETE" row. How can I do this? Thank you!

Gaurav_Dhama_ · Answer

Assuming since it is a manual workpaper, the row where Do not delete appears is not fixed.

To take care of that dynamically, in addition to @rzdodson flow, filter for string Do no delete after adding record ID. You will get a record ID where this string appears, append this value to original data, lets call this appended column as LstRw. Use a filter tool to compare the record ID with the LstRw ([RecordID]<[LstRw]). This will give you all the data above this string

catcat1720 · Answer

Thank you, will give it a go and report back!

rzdodson · Answer

@catcat1720 not sure how your batch macro that brings in all of the files is structured. But, I am wagering you have an Input tool that is getting updated by Control Parameter and Action tools to functionally pass in each file one-by-one. And, I am thinking you may be using a Directory tool in the original workflow to retrieve the list of file names you are wanting to pass through your batch macro.

If that is the case, I would drop a RecordID tool right afterwards. From here, you can insert a Filter tool that retrieves the data prior to that "DO NOT DELETE" value. Once the macro is saved and you run the workflow, it'll only retrieve the data that is relevant for your analysis.

Solution.yxzp