Combine multiple identical tables from EXCEL file in order to export in a single CSV

(0) Share

Report

Posted on by Community Power Pla...

Hi everyone,

I'm discovering the powerful possibilities of Power Automate.

I'm trying to learn by myself with the help of the templates and tutorials on internet

Thanks to this community I resolved some problems. But since few days I block on a such simple request... 😓

I have a single excel file with a dozen of identical tables on separate sheet (Same Headers but with different values)

I just want to keep the headers and to combine all the values in order to export the results in a CSV file. For instance :

TABLE A (SHEET 1)

PROJECT	USERNAME	TYPE	LOGIN
WD	apearce	AGENT	1000
WD	clille	NOT-AGENT	1001

TABLE B (SHEET 2)

PROJECT	USERNAME	TYPE	LOGIN
DE	ajansen	AGENT	2000
DE	mreed	NOT-AGENT	2002

RESULT in a CSV :

PROJECT	USERNAME	TYPE	LOGIN
WD	apearce	AGENT	1000
WD	clille	NOT-AGENT	1001
DE	ajansen	AGENT	2000
DE	mreed	NOT-AGENT	2002

It seems so simple but I don't know yet how to do.

Thanks for your help.

Nico

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

grantjenkins 11,063 Moderator on at

Like (1)

Report
See example below that will get what you are after.

Note that my example uses an Excel file with three Tables.

The full flow is below. I'll go into each of the actions.

Get tables retrieves a list of all the Tables in your workbook.

We then initialize an Array variable called rows that will eventually contain all the rows from each of the Tables.

We then loop through each of the Tables and retrieve the rows.

Next, we combine the current data in our Array variable rows with the rows we just received. The expression used here is:

union(variables('rows'),outputs('List_rows_present_in_a_table')?['body/value'])

We then set our Array variable rows to the combined rows.

Finally, we can build our CSV table using the data from our Array variable rows.

Note the expressions used for each value is:

item()?['PROJECT'] item()?['USERNAME'] item()?['TYPE'] item()?['LOGIN']

The final output of our CSV would look like:

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi M Grant,
Thank you so much for taking your time to help me.
It works perfectly. 👌
I was on the right path but never I'll had found the solution without your help (Especially for the expression).
I'll link unanswered comparable topics to your solution.
Thank you again.

Was this reply helpful? Yes No
Aswift2022_ 30 on at

Like (0)

Report

Hi, I have a similar requirement, although my tables are in different workbooks, and I would like to create a table , not CSV - I am sure there’s a slight tweak from this example, but I can not figure it out, nor find the answer. Thank you in advance if you are able to help

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report

@Aswift2022_ Are you able to post a new question in the forum and you can ping me directly. The more info you have the better we can find a good solution.

Was this reply helpful? Yes No