question

pankajchaturvedi-6530 avatar image
0 Votes"
pankajchaturvedi-6530 asked ·

Latest file extraction based on file timestamp(Azure data Factory)

Hi Team,

I want to extract the latest file from the ADLS and loads into target folder based on the timestamp if files contains timestamp.

for example: Customer_20201024.csv
Customer_20201025.csv
Customer_20201026.csv.

So basically it should process Customer_20201026.csv file into target here bcz it has latest timestamp compare than two other files. Could someone please help me here.

I am looking forward your response.

Thanks,
Pankaj

azure-data-factoryazure-storage-accounts
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Team,

Could you please someone help me here.Thanks!

0 Votes 0 ·

1 Answer

HimanshuSinha-MSFT avatar image
1 Vote"
HimanshuSinha-MSFT answered ·

Hello @pankajchaturvedi-6530
Thanks for the ask and also using the Microsoft Q&A.

Can you please let me know as to what will be the filename for say date September 25th 2020 ?
Will it be Customer_2020925.csv or 20200925.csv ( please look into the extra"0" before 9) ?
If its 20200925.csv the below logic will work.( i have tested that )


You can use the below solution with some changes .
https://docs.microsoft.com/en-us/answers/questions/136815/index.html

  1. Update the default value for TakeAnyStartDate to something like TakeAnyStartDate 20001027

  2. Inside the FE loop add SET Variable activity "DatewExtract_fromFileName" use the expression
    @substring( variables('filename') ,indexof(variables('filename'),'2') ,8 )

  3. The IF expression should be updated with
    @greater(int(variables('DatewExtract_fromFileName')),int(variables('TakeAnyStartDate')))

  4. Inside true condition you should have only one SET Variable activity with the expression as SET Variable activity

In the case if the file for September is Customer_2020925.csv , we will have to parse the string and appened "0" for month.

Let me know how it goes .I am attaching the pipeline JSON if that helps .
35582-pankajjson.txt
35603-2.gif

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



pankajjson.txt (7.5 KiB)
2.gif (30.5 KiB)
· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Well done !

Thanks a lot ,Himanshu for helping me and provided the best solution.

0 Votes 0 ·
pankajchaturvedi-6530 avatar image pankajchaturvedi-6530 pankajchaturvedi-6530 ·

Hi Himanshu,

if there is no new file loaded into dat lake input folder, then it should not copy any file into target folder.in our current solution even if new file is not loaded in input folder it is copying previous latest file.

Output
{
"name": "Finaloutput",
"value": "people_20210510.csv"
}
for example:
"people_20210510.csv" after copying this file, if i am triggering the pipeline it is copying the same file, Basically it should not copy any file(Zero file).Thanks!

I am looking forward your response.

0 Votes 0 ·