ADF Better way to count the number of files matching a FileMask, in a known folder

user_1662268 0
Viewed 514

I have a Known\Folder Path.

That folder contains several hundred small txt files.

Generally the filenames are of the form Prefix_<Code1>_<SubCode2>_<State>.txt

I want to know how many files there are for a specific value of Code1.

I was hoping to use the GetMetadata activity, with Path Known\Folder\Prefix_Value_*.txt, but that just returns empty set :(

Currently I've got it working with GetMetadata on Known\Folder, with childItems captured, and then a foreach over all the files, with If on @startsWith(file.name, 'Prefix_Value'). But that results in hundreds of iterations of the loop, in sequence, and each activity takes ~1 second so it ends up taking minutes to do this check.

Is there a better way to do this? Either to direclty locate all files matching my mask, or a better way to count the matching elements of a hundreds-of-items array?

azure-data-factory-pipeline azure-data-factory-2
2 Answers

Lots of little activities might be expensive if you do it often.

If you only want the count, you can do this in the following hideous way (promise it isn't written in Brainf&ck) ... it relies on the fact that you can use XPATH to scan XML in ADF. You only need a set-variable activity after your metadata lookup.

Set a variable equal to this - it will contain the number of files with 'Code1' in the name.

@{xpath(xml(json(concat('{"files":{',replace(replace(replace(replace(replace(replace(string(activity('Get Metadata1').output.childitems),'[',''),']',''),'{',''),'}',''),',"type":',':'),'"name":',''),'}}'))),'count(/files/*[contains(local-name(),''Code1'')])')}

The inner part:

replace(replace(replace(replace(replace(replace(string(activity('Get Metadata1').output.childitems),'[',''),']',''),'{',''),'}',''),',"type":',':'),'"name":','')

takes the metadata activity's output and strips the []{} parts and the type and name elements, then

json(concat('{"files":{',<the foregoing>,'}}')

wraps that up in to a JSON object, with files as the outer key and the filenames as inner keys (with text = "file" but that's going to be irrelevant).

Then you can take that JSON, turn it into XML and query the XML.

xpath(xml(<the above JSON>), 'count(/files/*[contains(local-name(),''Code1'')])')

The XPATH query counts all the elements under /files (which are now our filenames) whose names contain the text 'Code1'.

user_136127450

There is no way to get the file count directly matching Wildcard in Get Metadata activity by now. You can vote Get Metadata for Multiple Files Matching Wildcard to progress this feature.

If you only want to copy those files, you can use Wildcard file path. enter image description here

If those files stored in Azure Blob Storage or somewhere that can be got file count with prefix by API, you can use Azure Function activity.

user_133676380
Related