Extract Date from FileName in Copy Activity (ADF)

Question

We have multiple files with names as 'Amazon US Daily Sales Diagnostic_2021-05-10.xlsx', we want a solution to fetch this date from the filename and add a column by name 'date' in the table with this value through ADF by using copy activity.

Answer 1

Hello anonymous user,

Thanks for the question and using MS Q&A platform.

You may follow the below steps to extract Date from FileName in Copy Activity (ADF):

Step1: Use Get Metadata Activity to fetch Item name(file name) from dataset.

Step2: Create a variable to hold Date value from file name.

Step3: Use Set variable activity to set value for your variable (@split(split(activity('Get FileName').output.itemName,'_')1,'.')[0]).

Step4: In Copy activity add additional column to add another column for this date on top of your source data.

Hope this helps. Do let us know if you any further queries.

---------------------------------------------------------------------------

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

Answer 2

Hello anonymous user,

Yes, you can use 'Childitems' instead of 'iteamName'.

The below steps helps to extract Date from FileName in Copy Activity from a folder.

Step1: Use Get Metadata Activity to fetch child Items from folder. Dataset should point to folder in which we have our files.

Step2: Create a variable to store date.

Step3: Pass GetMetadata ouput childItems to ForEach Activity

Step4: Inside ForEach, Use Set Varible activity to fetch date from Filename and store it in variable.

Step5: Inside ForEach, Use Copy activity with Dataset Dynamically pointing to file and add additional column for the Date.

ds_SalesExcel Data set details

Hope this helps. Do let us know if you any further queries.

---------------------------------------------------------------------------

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.