Microsoft Fabric Copy Data Activity - Filter Out Files that Start With "~"

Jordan F 0 Reputation points
2024-10-02T22:46:19.43+00:00

I need to copy files from a network folder to a Lakehouse and I am building a pipeline to handle that tasks. Some of these files are temporary excel files that Microsoft generates natively as other users are making edits. I need to exclude files that begin with "~" from being passed through the Copy Data activity as they are causing errors.

I can't figure out how to format the wildcard file path for not files that start with "~". I'm not sure if that syntax exists or if a more complex pipeline needs to be built.

Thank you.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,901 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nandan Hegde 32,501 Reputation points MVP
    2024-10-03T04:23:27.53+00:00

    Based on my understanding, there is no direct way to filter the files via copy activity.

    You can follow the below process:

    a) Use get meta data activity to get list of all files from your source location

    use another get meta data activity to get list of files from your source location with file name beginning with ~

    b)Then use filter activity to get the other files

    the below link can be used to get the filter data :

    https://datasharkx.wordpress.com/2023/04/18/moving-delta-missing-files-between-source-and-sink-via-azure-data-factory-synapse-pipeline/

    c) then use foreach iteration to copy it

    0 comments
  2. AnnuKumari-MSFT 33,476 Reputation points Microsoft Employee
    2024-10-08T06:44:42.3333333+00:00

    Hi @Jordan F ,

    Welcome to Microsoft Q&A platform and thanks for posting your query here.

    As mentioned by Nandan , there is no direct pattern available for wildcard file path in Copy acitivity. You need to rather use filter activity after Get metadata activity fetches out the filenames and leverage the following expression in the filter activity to meet your requirement :

    @not(startswith(item().name, '~'))

    Alternatively, you can use list of files option in copy activity to copy only the specified desired files, for that you need to mention each filenames in a text file.

    Reference: List of file option in ADF

    Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.