Answered

Need Help with Excel File Automation in SharePoint

(1) Share

Report

Posted on 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!

Categories:

Using connectors

I have the same question (0)

All responses (6)

Answers (0)

Sort by

v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No
user12341 8 on at

Like
a
(1)

Report
Copy link

Link copied!

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 :

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @user12341 ,

Have you formatted data in that xlsx file into table?

Best regards,

Was this reply helpful? Yes No
user12341 8 on at

Like
a
(1)

Report
Copy link

Link copied!

Yes I have XLSX File And that's why I am not able to get any tables into the LIST ROW FROM TABLE

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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,

Was this reply helpful? Yes No
takolota1 4,980 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No