Merge Excel rows from multiple files into a template one

[SHBarrios]

I'm new to Power Automate and I want to solve a problem, I seek assistance to understand the issue. I have created 2 Cloud Flow: the first one that save all attachments from multiple emails address and store them in a single OneDrive Location. The second cloud flow is to send an email after it generate a report on such attachments and send it to a pre-arrange list of users.

I create a desktop flow that basically open a Report Template which have header on it, add the rows for all the excel files that are in OneDrive folder location and then save the report in a folder in OneDrive location. grab the date converted to a format that I need and save the in another location with the name and also the format as part of the name. Feel free to see the flow below, the template and output file is like this

Template form

Output from my desktop flow (edited) 

I have a few issues that I need help. I know that desktop flows are unable to run while my system is power down. I need a cloud flow to emulate my flow, I have been reading the forums, but I have no clear idea on how to implement my desktop flow successfully. I have been trying but I have to admit my knowledge is lacking and while Copilot AI suggested a few steps I'm still not able to figure it out the logic and how to set the correct parameters.

Start Flow- Grab the excel files, open the template and add the info

Mid Flow - save the document and add get the time

final steps - save the document with time frame added

Copilot AI suggested the following flow - While I know that list files on folder is the same as my first desktop flow steps all other are unfamiliar:

is this the template, if so how to get the right headers on it. if not, what else is missing?

Is this the reader for all excel files excluding headers?

I know this one , this is the UTC time format

wait, what outputs, body parameter, I do not see them in any previous steps...I know the folder where I want those...

I hope some guidance and thanks in advance for your kind help, I have received some work arounds using 3rd party vendors solutions

[Nived_Nambiar]

Hi @SHBarrios 

I think this should include 

1. Listing all files in onedrive

2. Looping through each of excel files and listing the rows present in it.

3. append the row to master table 

4. Once done > send it as email.

Power Automate cloud requires table to be present in the excel sheet around the sheet content so to recognize the rows etc, even if it is report template file which is empty- there should be an empty table created.

see below - 

Assume this is excel template file

below are list of files to be merged

Now use below approach-

Expression- 

first(outputs('Get_tables')?['body/value'])?['id']

Overall flow

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[SHBarrios]

Wow!! That is great solution, but I do see 2 problems implementing that into the solution for my flow:

1) my saved files from my first cloud flow save them as .csv files, with no table format (please see the folder and data below).

2) in order to implement the solution proposed, I need to change my Cloud Flow "Gather all emails" and change the output to all files has a formatted table on it, which I believe will need to add an extra step on the loop for each after creating the file, am I right ?

and that is when I get lost , I do not have an idea how to change that...

[AlexEncodian]

@SHBarrios 

You can parse data from CSV files using native expressions. This can be a little complicated but you'll find some topics on these threads.

To extract CSV data into Power Automate in a no code way, Encodian has two possible actions:

Parse CSV

Get Rows from Excel (works with CSVs too)

[SHBarrios]

@AlexEncodian 

Thanks for the proposed solution, but 3rd party vendors and API may not be the solution I'm looking for, as I mention previously, this may be tempting but only if and only if there are no other solutions.  Beside that I need api keys and buy a subscription right?

[AlexEncodian]

@SHBarrios 

API Key is free to get with no credit card required.

But yes, the free plan gives you 50 credits per month (i.e. extracting data from 50 CSVs). Above that you would need a paid subscriptions.

As mentioned, you can parse CSVs natively. I'm not an expert on this but you'll find threads on this topic so you can start studying it.

[SHBarrios]

@AlexEncodian ,

I figure it out in our previous interactions, I get the API key free at subscription, but I need to buy a lot more credits than just 50 a month is 8 .csv files a day, so I will run out of credits in a week.

I will continue looking in this topic, I learn a lot, but is not quite easy to implement desktop flow to cloud flow.

[SHBarrios]

@Expiscornovus 

I was reading your solution for Removing header (first row) in CSV files, I was wondering if you kindly guide me to the process to solve my issue of parsing multiple .csv files that I have in my OneDrive folder and marge into my template file.

[Nived_Nambiar]

Hi @SHBarrios 

Given that output excel file should have empty table present it, we can still proceed with if the input files are of csv type.

see below

I have placed csv files in input folder in sharepoint

Now see the flow design

1. use get files properties to get the list of all files from the folder location where csv files are stored

2. Now use an apply to each loop to loop through all files 

 Inside the loop, follow the below sub-steps 

2.1- Use get file content to get file content of the csv file 

2.2 - use compose action to decode text from csv file get file content action 

Expression - 

decodeBase64(outputs('Get_file_content')?['body']?['$content'])

2.3 Now use another apply to each which loops through csv file content and add row to excel table 

Expression - 

skip(split(trim(outputs('Compose')),decodeUriComponent('%0A')),1)

The above expression will skip the headers of csv file.

2.3.1- inside the above apply to each loop, use add row to table to add the csv row content to excel table 

Since apply to each iterates through rows , each element in a row is separated by comma,  so will be splitted by comma and take the respective element out of it.

This is how input data looks like

Output should look like 

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[SHBarrios]

@Nived_Nambiar 

That is close enough, I think I can edit the code for work in OneDrive location. Let me try and get back to you if not able to work it out.

[SHBarrios]

@Nived_Nambiar ,

well it does not work at all.... or at least will take a long time. each of the  .csv files is having over 250 rows each one and is still running after 35 minutes.