extract substring from a filename

Question

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

Answer 1

Hello @Tinashe Chinyati ,

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

@concat(substring(split(variables('Filename'),'_')[2],0,4),'/',substring(split(variables('Filename'),'_')[2],4,2),'/',substring(split(variables('Filename'),'_')[2],6,2))  

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 .

Thanks
Himanshu
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

Answer 2

@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])