Need Help with Excel File Automation in SharePoint

Like (1) Share

Report

Posted on 30 Oct 2023 13:46:10 by user12341

Hey there, Power Automate enthusiasts!

I'm working on a project where I need to automate some tasks involving Excel files in SharePoint, and I could use some guidance. Here's what I'm trying to accomplish:

Fetch an Excel File: I want to fetch an Excel file (.XLSX) from a specific location in SharePoint.
Extract Content: Once I have the Excel file, I need to extract specific data from it.
Data Manipulation: I want that some of the columns have shortcut like Y and N so in change Y to yes and N to NO , also in one column I am getting full form of address I want only last 5 words of that value.
Store Back in SharePoint: After the data manipulation, I want to store the modified Excel file back in SharePoint AS CSV

I'm somewhat familiar with Power Automate, but I'm not sure how to set up this workflow efficiently and reliably. If anyone has experience with a similar task or any suggestions, I'd greatly appreciate your insights and guidance.

Please feel free to share your knowledge, tips, or even step-by-step instructions if you have them. Any Power Automate gurus out there who can help me out?

Thanks in advance for your assistance!

All responses (6)

Answers (0)

takolota1 4,859 Super User 2025 Season 1 on 06 Oct 2024 at 23:03:31

Like (1)

Report

Need Help with Excel File Automation in SharePoint

You can also use this template Azure Function to pull data from any Excel sheet without the need for table formatting...
https://community.powerplatform.com/galleries/gallery-posts/?postid=eea06380-8483-ef11-ac21-7c1e5201b42f

Or if you need something without premium connectors, then you can use these Office Script templates…
https://community.powerplatform.com/galleries/gallery-posts/?postid=9f7a7bcf-c88f-ef11-ac20-7c1e525bd67d
v-jefferni on 01 Nov 2023 at 01:41:49

Like (1)

Report

Re: Need Help with Excel File Automation in SharePoint

Hi @user12341 ,

Anyway, you need to format excel data as table so that in Power Automate data could be retrieved. Please follow below blog and use office script to format data into table first:

Get Excel Data into Power Automate That Is Not Formatted as a Table | by Kyle Gibson | Medium

Best regards,
user12341 8 on 31 Oct 2023 at 10:50:30

Like (1)

Report

Re: Need Help with Excel File Automation in SharePoint

Yes I have XLSX File And that's why I am not able to get any tables into the LIST ROW FROM TABLE
v-jefferni on 31 Oct 2023 at 10:11:05

Like (1)

Report

Re: Need Help with Excel File Automation in SharePoint

Hi @user12341 ,

Have you formatted data in that xlsx file into table?

Best regards,
user12341 8 on 31 Oct 2023 at 09:26:50

Like (1)

Report

Re: Need Help with Excel File Automation in SharePoint

Hello @v-jefferni
I've Followed your demonstration but In the first Step of LIST ROW IN TABLE I am not able to get tables in table field.
So I've Used GET FILE CONTENT FROM SHAREPOINT to get the data but How can I use that data to create CSV File from that ?

Also In GET FILE CONTENT I am getting data something like this form :
v-jefferni on 31 Oct 2023 at 07:36:42

Like (1)

Report
Re: Need Help with Excel File Automation in SharePoint

Hi @user12341 ,

Please refer to below screenshots, the flow I created for demonstration:

Expression in Select:

for Y/N columns:

if(equals(item()?['Column1'],'Y'),'Yes',if(equals(item()?['Column1'],'N'),'No',item()?['Column1']))

for address column:

join(reverse(take(reverse(split(item()?['Address'], ' ')),5)),' ')

logic: split the address with space as separator, reverse the order of result array, take the first five words and reverse back the order, at last use space as separator to concatenate the words together and get the result you want.

Best regards,