Multiple filter conditions in a pipeline

Question

Hi is it possible to add multiple filter components in a pipeline. For instance if I want to filter a Data Lake folder first using a metadata activity and then filtering on Items with a specific word in the file, a date and file type = 'csv':
@activity('GetFileList').output.childItem and @equals(item().type, 'File')
I can use a @ANDO function to get and startswith, endswith function to get filename and filetype, but this seems wrong for instance. Is there a better way to filter for file types. The intention is to use a copy activity to move files of a specific type to their own folder. Any help would be appreciated.
Thank you.

Answer 1

Hi @CoffeeCanuck ,

Welcome to Microsoft Q&A forum and thanks for reaching out.

As per my understanding your requirement is to move specific type of files to their respective folders in sink. If that is the case.

Option 1:
The simplest way is to create a pipeline with multiple parallel copy activities and use binary dataset on both source and sink side. Then use wildcard filename path in source to filter a particular type of files from a folder in ADLS Gen2. For this implementation you will have to use a copy activity for each file type (*.csv or *.txt , so on...)

Option 2:
In case if would want to make this more dynamic with single copy activity, then an alternate would be to have a reference SQL table or reference File with two columns (FileType, SinkFilepath) to lookup the values.

Sample table as below:

Then have a lookup activity to lookup on the reference table/file and retrive the file type values and sink file path then pass the lookup output to subsequent ForEach activity which contains a Copy activity and map the lookup output values in copy activity source wild card file name settings and sink file path settings.

Note:

1. Please make sure to uncheck Recursively option under copy activity source settings so that only file under specified folder are moved to sink.
2. Make sure you select binary dataset inorder to delete the files from source after copying to sink location (nothing but moving files)

The reason for the above solution is because you can avoid multiple activities like Filter activity, ForEach activity in your pipeline which would save cost as well. If you would like to use filter activity to filter by fileType as File and FileName endsWith '.csv' , then you will have to have a filter activity one for each file type (.csv/.txt/.json) then followed by ForEach activity containing a copy activity.

Hope this info help. Do let us know if you have further query.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.