extract substring from a filename

Tinashe Chinyati 221 Reputation points

Hi Guys, I am new in ADF and would like to extract a date time string in my filename to use as a date hierarchy in my sink to ADLS gen2. for example I have a filename as "A_ODP_20200914_094647_00.CSV" and I want the 20200914 which I will partition as Year=2020 folder, Month=09 and Day=14 dynamically as the files come in. Can anyone assist with how I can go about it. Thank you

Azure Files
Azure Files
An Azure service that offers file shares in the cloud.
1,041 questions
Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,188 questions
0 comments No comments
{count} votes

Accepted answer
  1. HimanshuSinha-msft 19,246 Reputation points Microsoft Employee

    Hello @Tinashe Chinyati ,

    Thanks for the ask and using the forum . The below expression will work


    Just a quick inside as to what i am doing here .

    1. The date is assigned to the variable Filename , i did used A_ODP_20200914_094647_00.CSV to test .
    2. Split the name with "_" , and since it return an array , i have the data at index 2 .
    3. Use substring to get the year , month and day .
    4. I thought you may be need "\" as you talked about partitioning so used concat function .

    The below animation should help .


    Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

    2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Ayush Chauhan 116 Reputation points

    @HimanshuSinha-msft I do have the same issue can you assist me, how do I split the file name ' Amazon US Daily Sales Diagnostic_2021-05-10.xlsx'

    Currently I'm using - @markus.bohland@hotmail.de (split(variables('FileName'),'_')[1])