Skip to main content

Notifications

Power Automate - Using Connectors
Answered

How can I fetch year from the sharepoint file name?

(1) ShareShare
ReportReport
Posted on by 15

I have a use case where I need to copy sharepoint files(2-3 file uploaded yearly) to Azure blob storage.

Each file that is uploaded on sharepoint contains a year. Eg : 17 XYZ 2024, 15 XYZ 2024, 23 XYZ 2023 (test)

While writing the files I have to give file names as XYZ_YEAR, YEAR fetched from the original file name. Eg : XYZ_2024, XYZ_2023

For the first 2 examples I am able to fetch year easily using this function - last(split(variables('file'),' ')). However, this does not work if we don't have year at the end as the uploaded file name is or will not be consistent.

I just want to fetch year from the file name irrespective of it's placement in the filename.

Thank you for the help in advance.

It's kind of urgent, any help would be appreciated 🙂

  • MohdDanish0108 Profile Picture
    MohdDanish0108 15 on at
    Re: How can I fetch year from the sharepoint file name?

    Can we rename the excel sheet name while copying the excel files to another location?

    Thanks for the help in advance

  • MohdDanish0108 Profile Picture
    MohdDanish0108 15 on at
    Re: How can I fetch year from the sharepoint file name?

    Thank you so much 🙂 

    I was able to implement it for my usecase. Much appreciated 🙂

  • Verified answer
    Chriddle Profile Picture
    Chriddle 7,315 on at
    Re: How can I fetch year from the sharepoint file name?

    Chriddle_1-1715853890849.png

     

     

    Select

    From:

     

    split(
    	xpath(
    		xml('<a/>'),
    		concat('translate("',outputs('Compose') ,'", "-+_(){}[]", " ")')
    	),
    	' '
    )

     

    The xpath() function is just a fancy shortcut to replace all occurrences of the characters -+_(){}[] with a space.

    If you find this scary, replace it with nested replace() functions.

     

    Add other possible separators if necessary.

    Then this is splitted at space.

     

    Map:

     

    if(
    	and(
    		equals(
    			length(item()),
    			4
    		),
    		isInt(item()),
    		startsWith(
    			item(),
    			'20'
    		)
    	),
    	item(),
    	''
    )

     

    Checks each item if

    • its length is 4
    • it can be interpreted as an Integer
    • it starts with 20

    and, if so it returns its value otherwise an empty string.

    Add more tests if needed.

     

    Compose 2

     

    if(
    	equals(
    		length(
    			join(
    				union(body('Select'), json('[]')),
    				''
    			)
    		),
    		4
    	),
    	join(
    		union(body('Select'), json('[]')),
    		''
    	),
    	'noYearInFilename'
    )

     

    Checks whether there is a clear result (and returns it)

     

  • MohdDanish0108 Profile Picture
    MohdDanish0108 15 on at
    Re: How can I fetch year from the sharepoint file name?

    Any leads guys? I haven't able to figure out the solution yet.

  • MohdDanish0108 Profile Picture
    MohdDanish0108 15 on at
    Re: How can I fetch year from the sharepoint file name?

    This will remove manual intervention and could help with triggering pipeline as well. Right now the uploader has to raise a ticket and wait for the datafactory pipeline to be ran manually. With this process we will have an event trigger in the datafactory side which run the due process automatically and give instant result. Saving 0.5-1 day of wait time for users and dependency on support team to take file from sharepoint and upload it on blob storage

  • lbendlin Profile Picture
    lbendlin 7,597 on at
    Re: How can I fetch year from the sharepoint file name?

    You are trying to automate things that don't need to be automated. The cadence is way too infrequent. 

    https://xkcd.com/1205/

  • MohdDanish0108 Profile Picture
    MohdDanish0108 15 on at
    Re: How can I fetch year from the sharepoint file name?

    This file is created by multiple persons, point of contact keep on changing and they don't adhere to the convention for us. We want to automate the solution, earlier we used to manual take the file and change it to our needs.

  • lbendlin Profile Picture
    lbendlin 7,597 on at
    Re: How can I fetch year from the sharepoint file name?

    It will be much simpler to enforce file naming conventions and to rename files that fail to meet the convention rules.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,495

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,822

Leaderboard

Featured topics